Microsoft Microsoft excel 2007 open lice...

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

=IF(INDIRECT(B$1&"!$F$32")="N"; 1; 0) kinda solved the problem.. But maybe there are more solutions?

Hi, 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

Regards

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History