REF# error

[David Bateman]

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?

Thanks,

db

 

[Harlan Grove]

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$164-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.