Create list from same cell in other worksheets

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Not sure how to title this thread so let me explain what I'm trying to do. Basically I have several worksheets that are setup identically. I want to take all the data from each one and put it on a single worksheet (via link so it changes as the data from the worksheets are changed) but stack them.


Example: I create a new worksheet called Summary. The other worksheets in the workbook are called: Red, Blue, Green. Each of those have a unique value in cell B2 which is what I want to capture on the Summary worksheet. In my Summary worksheet I want cell B2 to link to Red B2. In Summary worksheet I want B3 to link to Blue B2. In Summary worksheet I want B4 to link to Green B2.

Rather than manually map these I'm hoping there's a faster way to do it.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,079
if all tables have the same structure use Power Query (Get&Transform) with Append feature
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
You could use INDIRECT:

Book1
ABCD
1Sheets
2Red-1Red
3Blue-2Blue
4Green-3Green
5
Sheet4
Cell Formulas
RangeFormula
B2:B4B2=INDIRECT("'"&INDEX($D$2:$D$4,ROWS($B$2:$B2))&"'!B2")


Keep in mind that INDIRECT is volatile, and if you have a lot of them they could slow down your workbook.

You could also use the Worksheet_Activate event and write a macro that updates the values whenever you go to the Summary sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,490
Members
410,686
Latest member
Fer9us
Top