INDIRECT Syntax

[cardan]

Hello, I have an issue that should be easy to fix but I can't seem to
get the right syntax for the INDIRECT function.
I am trying to reference a cell in another worksheet. When I copy the
formula across, I would like it to still reference the sheet but also
reference the next cell over. The INDIRECT syntax I am using is..

....INDIRECT("'"&$B4&"'!E3")... Cell B4 contains the name of the
worksheet I am referencing. When I copy the formula over to the next
column it still references E3. I would like it to reference cell F3 of
the worksheet name in cell B4.

Any help would be greatly appreciated! Thank you in advance.

 

[Pete_UK]

This is one way of doing that:

=INDIRECT("'"&$B4&"'!"&CHAR(64+COLUMN(E$1))&ROW($A3))

This will also enable you to copy down if you wish to get the cells
from E3, E4, E5 etc.

Hope this helps.

Pete