• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking to Multiple Tabs question

R

rjacmuto32

Member
I have a file with 48 tabs of data on it. I want to combine the data into 1 organized tab. (Yes I know it should have been that way from the start but I didn't create the file)

I have sevearl formula's in all the cells in row 3 that are linked ='TabABC'!B3 and ='TabABC'!B4. Now each row I want to have the formulas look at different tabs. So in row 4 my formula is =TabXZY'!B3 and =TabXZY'!B4. I know I can use a find a replace on each row but I have to that 48 times. I want to know is there some kindn of way I can put the tab name say cell A3 and A4, and some how link the formula using the tab name in cell A3. So is this possible or how can i do it

In cell B3 I want to do soemthing like ='A3'!B3 but it doens't work. how can I do = cell A3 contains my tab name to look for and bring back B3 from that matching tab name. This way I can just put the tab name in column A and all the formula's work off that.

Thanks
--Robert
 
Hi, rjacmuto32!
If you copy and paste the data the original worksheet references will be kept, but if you cut & paste the data then the references are updated to the new location, so cutting the range with data in each worksheet and pasting it below the last used row in the destination file will do the job and the worksheet name "translation" will be done automatically.
Does it helps?
Regards!
 
Not really. In Cell B3 I have the formula of ='TabABC'!$B$3. And in B4 I have the formula of ='TabXYZ'!$B$3, in cell B5 i have ='TabMMMMM'!$B$3. And I want to do that all the way down to row 48. How can you automatically update the tab name reference in each formula? You can see i'm linking to the same cell but on different tabs. I want to change the tab name in each row. Find and Replace will take some time. that's why I'm curious if this can be done in a formula. Cell A3 contains my tab name of ABC. Then in B3 how can I reference the tab name in A3 to find that matching tab and bring back the cell
 
Hi, rjacmuto32!

I don't see any problem regarding the referenced sheet qualification of any formula in any worksheet if moving all them to a single worksheet (as you 1st wrote), Excel will do that automatically. You can do this to check it:
- create a new workbook, 2 worksheets
- Sheet2 cell A1: 10
- Sheet3 cell A1: =Sheet2!A1
- cut Sheet2 cell A1
- paste it in Sheet1, cell D11
- check the value: 10
- check Sheet3 cell A1 formula: =Sheet1!D11
- cut Sheet3 cell A1
- paste it in Sheet1 cell E12
- check the formula: =Sheet1!D11
- then you can replace all Sheet1! by "" and that'd be all

The only issue is that being a manual procedure you'll have to do the cut & paste 48 times.

Regards!
 
Still doesn't help. I think I left the part off that they are on the same tab. This might be better.

Tab Name = Combined
B3 ='TabABC'!$B$3
B4 ='TabXYZ'!$B$3
B5 ='TabMMMMM'!$B$3

So if I was to copy B5 and paste into b6 it would read ='TabMMMMM'!$B$3. that is the same formula as in B5. but I want B6 to be TabZZZZZ'!$B$3. Then B7 to be TabWWWW'!$B$3

And that also repeats to Column S
 
Hi, rjacmuto32!
I think you didn't read me:
SirJB7 said:
If you copy and paste the data the original worksheet references will be kept, but if you cut & paste the data then the references are updated to the new location
Click to expand...
SirJB7 said:
...
- cut Sheet2 cell A1
- paste it in Sheet1, cell D11
...
- cut Sheet3 cell A1
- paste it in Sheet1 cell E12
...
do the cut & paste 48 times.
Click to expand...
Where did I wrote copy & paste?
Regards!
 
So I have to go to TabABC and cell B3 which contains my data. Cut that, and Paste it into Combined Cell A2.
Then go to TabXYZ and cell B3 and Cut that, and paste into Combined Cell A3?
 
Hi, rjacmuto32!
Yeap. Give it a try with the uploaded file. It has 3 sheets with data (Hoja1,2,3) with a 10x10 range (A1:J10). Worksheets Hoja1 & Hoja2 have formulas with no other sheet references, i.e., only with dependance from themselves, and Hoja3 has only formulas referring Hoja1 & Hoja2.
Do this:
Cut Hoja1 A1:J10, paste in Hoja0 A1; cut Hoja2 A1:J10, paste in Hoja0 A11; cut Hoja3 A1:J10, paste in Hoja0 A21.
You may want to check Hoja3 formulas at each step just to understand the process.
Regards!
 

Attachments

  • Linking to Multiple Tabs question (for rjacmuto32 at chandoo.org).xlsx
    12.8 KB · Views: 2
Hi, rjacmuto!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top