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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
if all tables have the same structure use Power Query (Get&Transform) with Append feature
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top