"David Bateman" wrote...
I'm trying to solve a problem in a fairly complicated workbook. In cell H14
I'm getting a REF# error. the function in cell H14 is

=INDIRECT("'" &B3 & "'!" & CELL("address",OFFSET(Q$28,$A$164+3,$A$165-1)))

In cell A166 it says on the cell

[QwestAccount.xls]$A$6

while the actual function in A166 is,

=SUBSTITUTE(CELL("address",OFFSET(Reference!$A$1, $A$164-1,$A$165-1)),
"Reference!", "",1 )

Where do I go from here?

A166 first. The CELL("Address",x) call returns the full address of the
top-left cell in x, so [workbookname]Reference!<whatever. If all you remove
is the "Reference!" bit, then the result is [workbookname]<whatever which
*IS* a syntax error for range references. If all you want is the address on
the worksheet, so only the <whatever piece, use

=MID(CELL("address",OFFSET(Reference!$A$1,$A$164-1,$A$165-1)),
FIND("!",CELL("address",OFFSET(Reference!$A$1,$A$1 64-1,$A$165-1)))+1,16)

Also, when debugging indirect reference formulas such as this, it's best to
start by removing the word INDIRECT from the formula, in this case leaving

=("'" &B3 & "'!" & CELL("address",OFFSET(Q$28,$A$164+3,$A$165-1)))

which would have evaluated to something like

'<whateverisinB3'![QwestAccount.xls]$A$6

which would show what wasn't working.