Fill Formula Not Adjusting Down the Column

[Guest]

I have a spreadsheet that has a summary sheet and multiple tabs. I have the
following formula in one of my summary sheet columns:

=IF('1300'!$F$9=" "," ",'1300'!$F$9)

-where 1300 is the name of one of the tabs and F9 within each tab is the
piece of information I want pulled into the summary tab.

The formula works correctly when I manually type it into any row with any
tab # within it, so I think the formula itself is ok. However, it's not
letting me drag the formula through the column and autofilling by changing
1300 to 1301, 1302, etc(representing pulling information off each consecutive
tab).

I already check that my calculation option is set to automatic and I've F9'd
about 10 times now. Any other suggestions?

 

[Guest]

Try:

=IF(INDIRECT("'" & (ROW()-1+1300) &"'!$F$9")=" "," ",INDIRECT("'" &
(ROW()-1+1300) &"'!$F$9"))

Adjust to your starting row.

HTH

 

[Gord Dibben]

It won't change the 1300 to 1301 by using that formula method.

You need an INDIRECT function call.

Assuming A1 has =IF('1300'!$F$9=" "," ",'1300'!$F$9).....leave as is.

Stick this in A2 and copy down.

=IF(INDIRECT("130"&ROW()-1&"!F9")="","",INDIRECT("130"&ROW()-1&"!F9"))


Gord Dibben MS Excel MVP

On Thu, 5 Jul 2007 10:56:01 -0700, lost in excel <lost in