Help With INDIRECT Formula

[danielhitch]

Hi.

This is my first try at using an INDIRECT formula and I cannot get it functioning quite how I want it.

My formula at present is:

=IF(INDIRECT("'"&$A7&"'"&"!"&"C$78")=0,"",INDIRECT("'"&$A7&"'"&"!"&"C$78"))

Cell A7 contains text which relates to a sheet name in my workbook.
C78 is the cell in which I want the value from.

My problem is that I want to copy the formula both across, and down to populate the table.

The reference to A7 works perfectly. But, the reference to C78 does not change its value.

Where am I going wrong?

Thanks in advance.

Dan.​

 

[Arcticwarrio]

you tryed C78 instead of C$78 ?

 

[njimack]

The ref to C78 doesn't change because it's in quotes, so it's treated as text. Try something like this:
INDIRECT(ADDRESS(78,COLUMN(C1),,,$A7))

 

[GlennUK]

you tryed C78 instead of C$78 ?

It's in text, so that won't make any difference.

Danielhitch, you could use ADDRESS(78,3+COLUMN()-COLUMN($B:$B),2) instead of "C$78", replacing $B:$B with whatever column the formula resides in.

... or use njimack's even simpler formula of course.

 

[jasonb75]

You can only do it that way (make the text portion of INDIRECT dynamic) if you use R1C1 reference style, i.e. if your formula starting in column A

INDIRECT("'"&$A7&"'"&"!"&"R78C[2]",0)

 

[danielhitch]

Thanks guys!

Ive changed my formula to:

=IF(INDIRECT(ADDRESS(78,COLUMN(C1),,,$A7))=0,"",INDIRECT(ADDRESS(78,COLUMN(C1),,,$A7)))

and it seems to work as I wish.

Regards,

Dan.

 

[jasonb75]

Is your data numeric or text?

Which cell is the formula located in? (first cell).