Geting info from same cells in dfr sheets

Microsoft Microsoft excel 2007 open lice...
August 30, 2010 at 05:33:27
Specs: Windows XP
Dear all,

i have a problem i cannot solve myself. There are several sheets (named starting with 1 ending 60) with information of same type in it, and another one sheet summarising that info. I am using the formula =IF('1'!$F$39="N"; 1; 0), where 1 is a sheet name, f39 - cell with some kind of information. And there is another column in summary sheet where cell nr does not change, but sheet Nr. must be "2", then "3" and so on. Is there any sollution to this issue? It is a hard job to edit sheet nr. in every formula one by one...

See More: Geting info from same cells in dfr sheets

Report •

August 30, 2010 at 05:57:41
=IF(INDIRECT(B$1&"!$F$32")="N"; 1; 0) kinda solved the problem.. But maybe there are more solutions?

Report •

August 30, 2010 at 06:20:11

As it is easy to create sequences of numbers in cells, you can combine INDIRECT() with references to consecutive cells.
If A1 contains 30, then this formula:

=INDIRECT("'1'!F" & A1)
will refer to cell F30 on a worksheet named "1"
If you create a series in column A (30, 31, 32 etc), then drag the formula down to extend it, it will refer to F30, F31 , F32 etc.

Another way to get a sequence without using numbers in other cells is to use ROW() in your formula. ROW() returns the row number of the cell containing the formula, so this formula in Cell B1:

=INDIRECT("'1'!F" & ROW()+29)
also refers to cell F30. Drag it down to B2 and it refers to F31.

Hope this helps


Report •

Related Solutions

Ask Question