Summary sheet

[afdmello]

Hi Folks/Gurus/MVP

I have 12 sheets of with data each sheet referencing an equipment

I am creating a summary sheet in which I want to populate cell B4(equipment name) of each sheet in a single column

for example
summary sheet

B4 shall be sheet 1 b4 For summary sheet b4 I used the formula = sheet 1! b4 but I cannot use the fill handle can it done any easier way?

B5 shall be sheet 2 b4

B6 shall be sheet 3 b4

Thanking you for your time in advance

AFD

 

[Peter_SSs]

You can, using the INDIRECT function. However, note that INDIRECT is a volatile function so if used a lot in your sheet, may impact the performance of the sheet.
In B4 of the Summary sheet, try this, copied down.

=INDIRECT("'Sheet " & ROWS(B$4:B4) & "'!B4")

 

[afdmello]

You can, using the INDIRECT function. However, note that INDIRECT is a volatile function so if used a lot in your sheet, may impact the performance of the sheet.
In B4 of the Summary sheet, try this, copied down.

=INDIRECT("'Sheet " & ROWS(B$4:B4) & "'!B4")

Wow Thank you Peter it worked. I did some adjustment of removing the extra quote marks.

If I give names to sheets tab then I guess this formula wont work?

In the summary sheet I am also doing the CountA function to do the action items
for example

summary sheet cell C4 has the formula
=COUNTA(sheet1!C4:C10)

How can I copy down this formula in the summary sheet for the other 12 sheets and again I guess if I give names this formula wont work

Thanks Peter.

AFD

 

[Peter_SSs]

I did some adjustment of removing the extra quote marks.

If you were able to do that then your sheet names were not like "sheet 1", "sheet 2" etc that you gave in post #1 . If they weren't causing any errors, then I would leave them in - see further below.

If I give names to sheets tab then I guess this formula wont work?

If you give the sheets different names then the formula as given will not work. However, If you were able to list those sheet names in the Summary sheet in, say, A4:A15 (or it could be another column) then you could use a formula like this for the original problem.
B4 copied down: =INDIRECT("'"&A4&"'!B4")

and for the COUNTA formula, it would be: =COUNTA(INDIRECT("'"&A4&"'!C4:C10"))

Note that again I have included the extra single quotes. That way, if you make any sheet names that contain a space, the formula will still work.