Autofill help!

[Rick88]

Is there a way to get this formula to autofill?

=if('9'!a73>0,'9'!a73,"") of course this references sheet 9, cell a73 but I want the next cell down to use the following formula;
=if('10'!a73>0,'10'!a73,"")

When I try to autofill this, it just changes the number 73 to 74, 75, etc.

Any help is greatly appreciated.

Thanks,
Rick

 

[Rick88]

Sorry I forgot to mention I am using excel 2003.

 

[zookeepertx]

Just taking a stab at it, but would this work?
if('10'!$a$73>0,'10'!$a$73,"")

 

[Weaver]

Hmm, it would depend.

You could use the ROW() function to increment the sheet number and then maybe use indirect() to turn it into an address.

What row does your first formula appear in and how many rows will it cover?

 

[Rick88]

It appears in C73-C103, D73-D103 all the way through I73-I103, column references a different sheet but the same cell in each column.
I.E. c73-c103 references sheets 1 through 31, cell a71,
d73-d103 references sheets 1 through 31, cell a72 etc

 

[Weaver]

Try this in C73 and copy down

=IF(INDIRECT(ROW()-72&"!A71") > 0,INDIRECT(ROW()-72&"!A71"),"")

Hopefully you'll be able to figure out from this how to populate the other cells.

 

[mvptomlinson]

As an alternative to using

ROW()-72

You should be able to use

ROW(A1)

 

[Rick88]

I apologize if I am leaving out information (which I am sure is the case).

Let me start over, the explanation may be a little lengthy.

I am working with the sheet titled "Totals",

In cell H73, I am starting with the formula =if('1'!a76>0,'1'!a76,"")

the next cell down H74 I will have to manually type in
=if('2'!a76>0,"2'!a76,"") to get the information, if any is entered, on sheet 2, cell a 76.
I am trying to figure out how to do the first formula for each column and then click and drag down to H103 to autofill so that the only information that changes in the formula is the sheet being referenced, i.e. '2'!a76, '3'!a76, '4'!a76, etc.

Is this possible?
Thank you in advance,
Rick

Using excel 2003

 

[mvptomlinson]

That's exactly what Weaver's formula does. My addition was just to show that when you put ROW()-72 into a cell in row 73, it's the same as using ROW(A1).

Full formula:
=IF(INDIRECT(ROW(A1)&"!A71")>0,INDIRECT(ROW(A1)&"!A71"),"")

Becomes:
=IF(INDIRECT(1&"!A71")>0,INDIRECT(1&"!A71"),"")
=IF(INDIRECT("1!A71")>0,INDIRECT("1!A71"),"")
=IF(1!A71>0,1!A71,"")

When you fill that formula down one row, the ROW(A1) becomes ROW(A2) automatically. ROW(A2) then becomes simply 2, referencing the sheet named 2. ROW(A3) becomes 3, etc.

 

[Weaver]

As an alternative to using

ROW()-72

You should be able to use

ROW(A1)

Good call.