google sheets
I have the following formula in a Google Spreadsheet:
=IF(I11="VSS";G11;IF(I11="MCS";0;G11*Totais!C30))
I would like to freeze Totais!C30 when I click on bottom right of cell and drag down.
Currently when I do this, Totais!C30 change to Totais!C31, Totais!C32,…
Please try:
=indirect(E26&"!"&$AV$6)
Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).
This formula looks really complicated but really its just a long nested arrayformula:
=IF(A:A=INDEX(A:A,MATCH(TEXT(MAX(ARRAYFORMULA(if(istext(A:A),VALUE(SUBSTITUTE(A:A,LEFT(A:A,1),"")),))),"#"),ARRAYFORMULA(SUBSTITUTE(A:A,LEFT(A:A,1),"")),0)),TRUE,FALSE)
You want to use conditional formatting with a custom function :
You can see the image below but to explain a little, first what I did was remove the Letter portion using SUBSTITUTE leaving you the number, but then we had to format it as a digit using VALUE to get the max, following that I had to convert it back to a text string to use MATCH, and then INDEX to point to the cell that should be highlighted.
I am admittedly horrible at explaining how to do something once Ive figured out how to do it - but here is an attempt to break down the pieces in the formula:
Best Answer
By adding a
$in front of the column & row you can lock that specific area up when copying.Example:
Totais!$C$30would lock down when copying anywhere else in the document.Totais!$C30would lock down moving column to column.Totais!C$30would lock down when moving down rows.