Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
I have four sheets each with its own name which corresponds to the left cells. I want to reference those sheets by only stating the left cells. For example, instead of having ='SIN PLANIFICACIÓN'!E118 in C3, I have something of the likes of =$B3!E118. So I can drag this formula down and automatically reference other sheets.
How can I do this?
INDIRECT is the function you need.
=INDIRECT("'" & $B3 & "'!E118")
This will work if you are always referencing the same cell on each sheet.
However, if you want that cell reference to also change as you drag down the formula, this won't work as the E118 is text and Excel won't auto-increment it. In that case you would need this:
=INDIRECT("'" & $B3 & "'!E" & ROW() + 115)
ROW() just returns the number of the row that your formula is on, so that the formula in cell C4 will be on row 4 and you will get a reference to E119.
Note: The ' character is necessary when you have spaces in the sheet names, but will work if you have no spaces also.
