google sheets – How to change the height of the line of text in a cell?

I could not find an option in the recent version of Google Sheets to change the height of the line of text (the spacing between lines of a single cell).

You understand even more the problem when you export the spreadsheet in PDF format: the lines are very close to each other, which makes the reading uncomfortable.

In summary, it would be useful to adjust the height of the lines. I like to give spacing to lines of text (a long line, no splitting of text on different cells).

Google Sheets – Historical data not available yesterday

The formula below is used to obtain historical data up to today:

=GOOGLEFINANCE("GOOG","ALL",DATE(YEAR(TODAY())-1,1,1),"12/05/2019","DAILY")

Unfortunately, I do not receive the data of today (until now, I understand why), but the data of yesterday are not transmitted either.

I just received the data until 2019-12-03.

Recalling: the question was open to 2019-12-05.

firefox – ctrl-v sometimes causing the warning to copy / paste in Google Sheets

Curious if someone else is experiencing it. I use Google sheets in Firefox 70 on Ubuntu 18.04, and sometimes, ctrl-v opens the warning to copy / paste instead of paste as expected. I've added gif below. I'm trying ctrl-v 4 times. The first 3 work well, and the 4th opens the window and does not stick.

Ctrl-v demo running several times then not working

google sheets – Automatically concatenate multiple columns

I have three columns

A    B    C
D    E    F
G         H
          I

I want to write a formula such that it combines the three superimposed columns so that my fourth column is:

A
D
G
B
E
C
F
H
I

Addition of a z at the end of the second column would automatically give:

A
D
G
B
E
z
C
F
H
I

Formulas – Multiple Unique Values ​​in Google Sheets Spreadsheets

I rarely use spreadsheets, but I thought that creating a workbook for a contest would be really helpful. I do not know if what I ask is possible without a custom function. I am grateful for any help that can be offered.

I'm trying to make a template that keeps track of a weekly writing contest. Each week is noted independently, then added at the end of the month. I wanted to have a sheet that keeps track of the grand total. I thought I could do a UNIQUE search () in each worksheet and have it display a single column. According to this answer, I tried

=UNIQUE({'Week1'!B2:B,'Week2'!B2:B,'Week3'!B2:B,'Week4'!B2:B})

This retrieves unique values ​​for each sheet, but aligns them in multiple columns. I was hoping to get all the user names from column A, and then use a search formula to add up the totals for each week in column B. Here is the concept validation workbook if anybody else is interested in this. one can take a look at it.

Again, I appreciate any suggestions or guidelines for this to work. If that can not be done, I can simply switch to the manual totals tab, but I hoped to streamline my workflow.

google sheets – Determining the location of a check box that activated a script?

I have hundreds of checkboxes in several sheets of a single Google Sheets project.

When a user clicks on a check box, I want a script to be enabled.

In this script, I need to know in which cell the check box on which the check box has been activated is present. Is there a way to do this without creating a hundred different scripts for each check box and hard code the location of each of these cells?

For example, if I checked this box

enter the description of the image here

I would like my script to know that it has been activated from Sheet12: B2. If the specific sheet is not possible, only the location of the cell (B2 in this case) is sufficient.

Formulas – (Google Sheets) How to use conditional formatting on one column based on data from another column?

Battery Exchange Network

The Stack Exchange network includes 175 question-and-answer communities, including Stack Overflow, the largest and most reliable online community on which developers can learn, share knowledge and build their careers.

Visit Stack Exchange

vba – Copy the data of a "master" sheet on several "detailed" sheets

I'm starting to use VBA. What I'm working on is copying the details of a master file to multiple sheets in a spreadsheet.

I am experiencing a performance problem when I run my code. My Excel file keeps flashing and not responding.

My code is as follows.

Sub Button1_Click()
Dim lastrow As Long
Dim erow As Long
Dim i As Integer

MsgBox ("Clear details of the destination sheets.")

Worksheets("15 Tax Class").Range("A7:V5000").Clear
Worksheets("14 Long Texts").Range("A7:I5000").Clear
Worksheets("12 UoM").Range("A7:V5000").Clear
Worksheets("11 Description").Range("A7:E5000").Clear
Worksheets("09 Sales").Range("A7:AS5000").Clear
Worksheets("03 Plant").Range("A7:FI5000").Clear
Worksheets("02 Client").Range("A7:CR5000").Clear
Worksheets("01 Header").Range("A7:U5000").Clear

MsgBox ("Please wait while template is being populated.")

lastrow = Worksheets("Masterfile").Cells(Rows.Count, 1).End(xlUp).Row

For i = 7 To lastrow

'01 Header Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("01 Header").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("01 Header").Cells(erow + 1, 1)

Worksheets("01 Header").Cells(i, 2) = "Z"
Worksheets("01 Header").Cells(i, 3) = "DIEN"
Worksheets("01 Header").Cells(i, 4) = "X"
Worksheets("01 Header").Cells(i, 5) = "X"
Worksheets("01 Header").Cells(i, 6) = "X"
Worksheets("01 Header").Cells(i, 7) = "X"

'02 Client Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("02 Client").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("02 Client").Cells(erow + 1, 1)

Worksheets("Masterfile").Range("D7:D5000").Copy
erow = Worksheets("02 Client").Cells(Rows.Count, 3).End(xlUp).Row
Worksheets("02 Client").Range("C7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("E7:E5000").Copy
erow = Worksheets("02 Client").Cells(Rows.Count, 4).End(xlUp).Row
Worksheets("02 Client").Range("D7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("F7:F5000").Copy
erow = Worksheets("02 Client").Cells(Rows.Count, 5).End(xlUp).Row
Worksheets("02 Client").Range("E7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("G7:G5000").Copy
erow = Worksheets("02 Client").Cells(Rows.Count, 5).End(xlUp).Row
Worksheets("02 Client").Range("F7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("02 Client").Cells(i, 82) = "NORM"

'03  Plant Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("03 Plant").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("03 Plant").Cells(erow + 1, 1)

Worksheets("Masterfile").Range("H7:H5000").Copy
erow = Worksheets("03 Plant").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("03 Plant").Range("B7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("I7:I5000").Copy
erow = Worksheets("03 Plant").Cells(Rows.Count, 75).End(xlUp).Row
Worksheets("03 Plant").Range("BW7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

'09 Sales Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("09 Sales").Cells(erow + 1, 1)

Worksheets("Masterfile").Range("J7:J5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("09 Sales").Range("B7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("K7:K5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 3).End(xlUp).Row
Worksheets("09 Sales").Range("C7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("L7:L5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 17).End(xlUp).Row
Worksheets("09 Sales").Range("Q7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("O7:O5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 25).End(xlUp).Row
Worksheets("09 Sales").Range("Y7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("P7:P5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 26).End(xlUp).Row
Worksheets("09 Sales").Range("Z7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("Q7:Q5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 27).End(xlUp).Row
Worksheets("09 Sales").Range("AA7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("R7:R5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 27).End(xlUp).Row
Worksheets("09 Sales").Range("AB7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("S7:S5000").Copy
erow = Worksheets("09 Sales").Cells(Rows.Count, 27).End(xlUp).Row
Worksheets("09 Sales").Range("AC7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

'11 Description Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("11 Description").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("11 Description").Cells(erow + 1, 1)

Worksheets("11 Description").Cells(i, 2) = "EN"

Worksheets("Masterfile").Range("B7:B5000").Copy
erow = Worksheets("11 Description").Cells(Rows.Count, 4).End(xlUp).Row
Worksheets("11 Description").Range("D7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False


'12 UoM Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("12 UoM").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("12 UoM").Cells(erow + 1, 1)

Worksheets("Masterfile").Range("L7:L5000").Copy
erow = Worksheets("12 UoM").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("12 UoM").Range("B7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("M7:M5000").Copy
erow = Worksheets("12 UoM").Cells(Rows.Count, 4).End(xlUp).Row
Worksheets("12 UoM").Range("D7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

Worksheets("Masterfile").Range("N7:N5000").Copy
erow = Worksheets("12 UoM").Cells(Rows.Count, 5).End(xlUp).Row
Worksheets("12 UoM").Range("E7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

'14 Long Texts Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("14 Long Texts").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("14 Long Texts").Cells(erow + 1, 1)
Worksheets("Masterfile").Paste Destination:=Worksheets("14 Long Texts").Cells(erow + 1, 3)

Worksheets("14 Long Texts").Cells(i, 2) = "MATERIAL"
Worksheets("14 Long Texts").Cells(i, 4) = "GRUN"
Worksheets("14 Long Texts").Cells(i, 5) = "EN"

Worksheets("Masterfile").Range("C7:C5000").Copy
erow = Worksheets("14 Long Texts").Cells(Rows.Count, 8).End(xlUp).Row
Worksheets("14 Long Texts").Range("H7").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False

'15 Tax Class Data
Worksheets("Masterfile").Cells(i, 1).Copy
erow = Worksheets("15 Tax Class").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Masterfile").Paste Destination:=Worksheets("15 Tax Class").Cells(erow + 1, 1)

Worksheets("15 Tax Class").Cells(i, 2) = "PH"
Worksheets("15 Tax Class").Cells(i, 4) = "MWST"
Worksheets("15 Tax Class").Cells(i, 5) = "1"

Next i

MsgBox ("Generating of Upload Template already done!")

End Sub

Google Sheets applies conditional formatting for each column.

I would like to highlight the maximum value in each column. Example:

   A    B    C
1  50   12   60%
2  20   84   50% 
3  38   20   15% 

Here A1, B2, C1 will be highlighted. So, I know how to do that for 1 column. I create a custom formula:

=$A:$A=max(A:A)

So, if I do, only cell A1 will be highlighted. So, how can I do this for the other column at the same time without duplicating the conditional formatting on each column?

I've noticed that the max () function does not work on the percentage, it does not highlight my cell, do you know why?

How can I prevent the conversion of floating / decimal numbers to text in Google Sheets? (IOS)

I have a cell with the value 2.2 that is displayed as text. I can not convert the text value format to a number.

Screenshot 1 shows that the cell format is a number
Screenshot 1

Screenshot 2 shows that using the standard formula, the cell value type is text
Screen capture

type info formula:
https://support.google.com/docs/answer/3267375

I am currently using Google Sheets v1.2019.46202 on iOS 13.1.3.