Data from multiple worksheets consolidated onto one worksheet

[lynnaroo42]

Hello,

I am new to Mr. Excel and have exhausted my search engine trying to find a solution. Open to any ideas.

Our department does manual medical chart reviews. Each Chart review has its own worksheet. I then create a summary tab where I pull in the data from the other tabs to run stats on it. I enter the formula and try to use the fill series feature but the worksheets have to be manually updated. Below is an example.

[TABLE="width: 512"]


<colgroup><col width="128" style="width: 96pt;" span="4">
<tbody>[TR]

[TD="width: 128, bgcolor: transparent"]Age[/TD]

[TD="width: 128, bgcolor: transparent"]=Sheet2!$B3[/TD]

[TD="width: 128, bgcolor: transparent"]=Sheet2!$B3[/TD]

[TD="width: 128, bgcolor: transparent"]=Sheet2!$B3[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Gender[/TD]

[TD="bgcolor: transparent"]=Sheet2!$B4[/TD]

[TD="bgcolor: transparent"]=Sheet3!$B4[/TD]

[TD="bgcolor: transparent"]=Sheet4!$B4[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]CCM[/TD]

[TD="bgcolor: transparent"]=Sheet2!$B5[/TD]

[TD="bgcolor: transparent"]=Sheet3!$B5[/TD]

[TD="bgcolor: transparent"]=Sheet4!$B5[/TD]

[/TR]


</tbody>[/TABLE]
The age line is what I will get when I try to drag/autofill but the gender or CCM line is what I want. More specifically, I want the sheet to change as the variable rather than the cell.

Things I have considered: Macro (rows/data looking for changes), link (data in cell changes with each new audit), same work sheet (need it separate for staff to see their individual scores), & templates (rows/data looking for changes).

Much appreciated!

Lynne Emmons

 

[GeoKoro13]

Try to be more clear pls. I'm not an expert but it might be something i could help.
Are you having problems to get the correct sheet names in the formula?

 

[lynnaroo42]

Try to be more clear pls. I'm not an expert but it might be something i could help.
Are you having problems to get the correct sheet names in the formula?

I'm looking for a faster way to get these numbers to automatically ascend/fill rather than manually updating each specific cell. When I drag to fill the series I get the age row with sheet 2 showing for every column. I need it to look like the Gender or CCM row where the cell (B4) stays the same but the sheet # grows with each column. I can do it manually but when I have 30 rows and 45 columns it is tedious.

[TABLE="width: 512"]


<colgroup><col width="128" style="width: 96pt;" span="4">
<tbody>[TR]

[TD="width: 128, bgcolor: transparent"]Age[/TD]

[TD="width: 128, bgcolor: transparent"]=Sheet2!$B3[/TD]

[TD="width: 128, bgcolor: transparent"]=Sheet2!$B3[/TD]

[TD="width: 128, bgcolor: transparent"]=Sheet2!$B3[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Gender[/TD]

[TD="bgcolor: transparent"]=Sheet2!$B4[/TD]

[TD="bgcolor: transparent"]=Sheet3!$B4[/TD]

[TD="bgcolor: transparent"]=Sheet4!$B4[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]CCM[/TD]

[TD="bgcolor: transparent"]=Sheet2!$B5[/TD]

[TD="bgcolor: transparent"]=Sheet3!$B5[/TD]

[TD="bgcolor: transparent"]=Sheet4!$B5[/TD]

[/TR]


</tbody>[/TABLE]

 

[L. Howard]

Try this.

=INDIRECT("Sheet"&COLUMN()&"!B4")

and pull across.

You may need to use a + or - "number" to adjust the column() to the correct columns on the other sheets. Where if you used the above formula starting in column A and you want to return the values from column C on the others sheets, then it would look something like this

=INDIRECT("Sheet"&COLUMN()+2&"!B4")

Howard

 

[GeoKoro13]

So try the following:

1. Insert a row above or at the last row of your data. Let's say is row 2 now.
2. Starting from B2, type Sheet2. Next cell, that is C2, type Sheet3.
3. Select both cells and then drag to auto fill the rest column as far as you want
4. Now, by using the following formula at B3

=IFERROR(INDIRECT("'"&B2&"'!"&"B4"),"")

​

Now, when you auto fill the formula to the next columns will be getting the value from the different sheets, if there are name Sheet1, Sheet2 etc.
I just added the IFERROR function so you won't be getting errors when there is no data at the specific cell. You might change the "" to 0 if you prefer to get a zero value instead of nothing.