I am using the Indirect function in the following formula =INDIRECT("’"&E4&"’.I56") . This works great but instead of using E4 I would like to always use E but I want the 4 to be the current row. I tried :
=INDIRECT("’"&E&ROW()&"’.I56")
I also tried:
=INDIRECT("’"&ADDRESS(ROW(),COLUMN() -1,4)&"’.I56")
and that didn’t work either.
Any Suggestions?
Thank you
Your original formula will only work if E4 is containing the name of an existing sheet. What you are trying can only make sense if the other cells in column E possibly getting referenced also contain the names of existing sheets.
Did you realise that?
The first “enhanced” formula you show us will try to interpret E as a reference (name). If you rectify it replacing E with “E” it will pass E followed by the digits of a row number, then a dot and I56. There will not be a valid sheet name. Thus no valid reference. Error indicator #REF!
The second try will also not pass a sheet name, and as a whole no valid address.
What you seem to be intending should be done by =INDIRECT(INDIRECT(“E”&ROW())&".I56") . (I don’t need the silly single apostrophes because I always use syntactycally clean sheet names.)
My Advice: Dont play with nested INDIRECTions. They are not in every case easily analised.
Don’t use sheet names except those made from one letter followed by a sequence of letters and/or digits. No spaces, no special characters! You are rewarded the omission of single apostrophes in connection with sheet names.
You should get a first working result if you simply fill down your original formula dragging the “fill handle” of the cell containing it. (The little square bottom right!)
My addional Advice: INDIRECT is only usefull in very rare and limited cases, NESTED INDIRECTions should never ever come to your mind.
@karolus - You are clearly right. The case under discussion, however, might (basically) be one of these rare ones. Needing to refer to variable sheets is a justification for INDIRECT because very strange parameters of the universe led to the fact that we cannot refer to a sheet using its number, a sheet offset or an index into a series of sheets. It’s crazy - and a superfluous case of needing custom functions and complicated constructs…
Yes, I am using E5 as a list box to control the results in the =INDIRECT(INDIRECT(“E”&ROW())&".I56"). Maybe not the most efficient way but it works.
Anyway, your suggestion worked perfectly and solved my problem. Thank you for your help and your suggestions on sheet naming which does seem cleaner and easier.
Thanks Again!
If you start for Example in Row 5 with Formula:
=INDIRECT("'"&$E5&"'.I56")
copying to right and left changes nothing because $B
copying down or up will adapt the Row-reference as you need.
If this Formula don’t work, then there are not valid Sheetnames in Column E.
But thats youre Fault, not the Formulas.
Yes, absolutely true, that would work as well. I should have though of that.
Thank You for your help.