How to extract data from named sheets to summary sheet [Closed]

Report
Posts
1
Registration date
Friday November 7, 2014
Status
Member
Last seen
November 7, 2014
-
Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
-
Hi,
I have data in sheets, say Nov1, Nov2 to Nov30. I am looking for a solution,
a) in cell B2 of the summary sheet, the values have to appear from Nov1 - cell2.
I tried this by manually linking, like, ='Nov 1'!C$2
b) Similarly, I linked for Nov2 as, ='Nov 2'!C$2

Like this, I did for all values from C2 to C101.
I have details of various products in all sheets that are named as Nov1, Nov2, Nov3 to Nov30.
My query is how to have a formula to copy the details from all sheets that are named as Nov1, Nov2, Nov3 to Nov30 to the summary sheet in cells from B2 to B101. This is about one cell, but I have many cells in each of these named sheets that are around 200.
Note - while I am trying to drag the formula ='Nov 1'!C$2 it is coming as such with the date unchanged. So, if there is simple way, to bring change in the date when they formula is dragged, it will help me ie, ='Nov 1'!C$2 should change to ='Nov 2'!C$2 , ='Nov 3'!C$2 and so on when dragged down. Thanks in advance,

1 reply

Posts
1852
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 7, 2020
136
sekhar2014, Good afternoon.

Try to use it:
B2 --> =INDIRECT("'NOV " & ROW(A1) & "'!$C$2")

Copy it down.

Is it what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!