Extract info from sheet/tab ignoring the name

gjgj

New Member
Joined
Jan 10, 2011
Messages
15
Hi all,
is it possible to take data (possibly from look up formula?) from sucessive tab/sheets with in a range? The tabs are named differently but it would be great to have a formula ignore the name of the tab and just automatically 'jump to the next one' and collect the info.

Basically i have one summary tab of info which is taken from specific parts of each of the other tabs in the worksheet. There are large amounts of sheets each named uniquely. The problem is it is updated every week and so is the name of the tab. The cell and the info type which is extracted and summarised is always the same. So every week I have to change the formula to reflect the new tab/sheet names so it works ok.

Thanks!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

gjgj

New Member
Joined
Jan 10, 2011
Messages
15
Thanks for the reply VoG, but I think it wouldn't work actually - sorry my inital description wasn't the clearest.
The individual sheets have the same structure (the relevant info in same cells) but with varying info in them, but the info type is always the same:

Individual sheets:
Left cell same | Right cell unique
<table style="width: 148px; height: 56px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">Project:</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">xyz</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">Duration:</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">2 days</td> </tr> </tbody></table>
So the lookup wouldn't really work (I think?).
If there is a way for a formula to just extract the text from the same cell number from one sheet, then 'skip to the next sheet' (ignoring the sheet name as its always changing) it would be perfect. So the summary sheet would look like:

Summary Sheet:
<table style="width: 229px; height: 76px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Sheet</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Project</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Duration</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">xyz</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2 days</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">zyx</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">3 days</td> </tr> </tbody></table>
thanks again! Hope this clears it up a bit.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,140
Members
414,365
Latest member
UUR

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
Top