Copying data from a master tab to other tabs which updates automatically

Y

ycharron

New Member
Joined
Aug 16, 2013
Messages
5
Hello,

I have searched on multiple forums and on the web, and tried different formulas from answers and tutorials, but somehow, none of them provide me with the answer i need which is why i'm hoping someone will be able to help me, or tell me if this is possible or not.

I'm currently using Excel 2010 with Windows 7
I'm looking for a way to take data from a master tab and copy it automatically to a different tab, but i also want any updates i make in the master tab to be automatically reflected/updated in the other tab.

Example:
My master tab contains data that will be manually entered since it will come from various sources. Some cells will inevitably be empty or have a 0 in them.

This is what it looks like in the master tab:
Excel 2010
ABCDE

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #A6A6A6, align: center"]French[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Italian[/TD]
[TD="bgcolor: #A6A6A6, align: center"]German[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Spanish[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #00B0F0"]Costs1[/TD]
[TD="bgcolor: #00B0F0, align: center"]100 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]110 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]120 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]130 €[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #00B0F0"]Costs2[/TD]
[TD="bgcolor: #00B0F0, align: center"]200 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]210 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]220 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]230 €[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #00B0F0"]Costs3[/TD]
[TD="bgcolor: #00B0F0, align: center"]300 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]310 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]320 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]330 €[/TD]

</tbody>
tab1​
My second tab will be used to make multiple graphs based on different data from the master tab and because the sorting will change depending on the data i use in the graph, the second tab needs to contain information from the Master tab, but i need to transpose it and i also need it to automatically adjust itself or update if i make changes in the Master tab.

This is what it would look like in the second tab
Excel 2010
ABCD

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0"]Costs1[/TD]
[TD="bgcolor: #00B0F0"]Costs2[/TD]
[TD="bgcolor: #00B0F0"]Costs3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #A6A6A6, align: center"]French[/TD]
[TD="bgcolor: #00B0F0, align: center"]100 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]200 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]300 €[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Italian[/TD]
[TD="bgcolor: #00B0F0, align: center"]110 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]210 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]310 €[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #A6A6A6, align: center"]German[/TD]
[TD="bgcolor: #00B0F0, align: center"]120 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]220 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]320 €[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #A6A6A6, align: center"]Spanish[/TD]
[TD="bgcolor: #00B0F0, align: center"]130 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]230 €[/TD]
[TD="bgcolor: #00B0F0, align: center"]330 €[/TD]

</tbody>
tab2​


Thank you in advance for any help.

yan charron

 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hello yan,

Have you tried in tab2 to simply use a formula = tab1 B1

HTH
 
Upvote 0
Maybe this:

Code: 
A1-> =IF(INDEX(Master!$A$1:$E$4,COLUMN(),ROW())="","",INDEX(Master!$A$1:$E$4,COLUMN(),ROW()))

Or

A1-> =IF(INDEX(Master!$A$1:$E$4,COLUMNS($A1:A1),ROWS(A$1:A1))="","",INDEX(Master!$A$1:$E$4,COLUMNS($A1:A1),ROWS(A$1:A1)))

Markmzz
 
Last edited:
Upvote 0
thank you both for your answers. I tried both of them and unfortunately, they did not seem to work for me. I'm still very new at this so i'm quite sure it's something i'm doing wrong.

A coworker of mine was also looking into this and came up with the following piece of code which seems to work:
{=TRANSPOSE(master!$A$1:$W$10)}

again, thank you for the help!
 
Upvote 0
ycharron said:
thank you both for your answers. I tried both of them and unfortunately, they did not seem to work for me. I'm still very new at this so i'm quite sure it's something i'm doing wrong.

A coworker of mine was also looking into this and came up with the following piece of code which seems to work:
{=TRANSPOSE(master!$A$1:$W$10)}

again, thank you for the help!
Click to expand...

Here the formulas worked. Look at this:

[TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl64, width: 27, bgcolor: #D9D9D9"][/TD]
[TD="class: xl63, width: 39, bgcolor: #D9D9D9"]A
[/TD]
[TD="class: xl63, width: 47, bgcolor: #D9D9D9"]B
[/TD]
[TD="class: xl63, width: 47, bgcolor: #D9D9D9"]C
[/TD]
[TD="class: xl63, width: 47, bgcolor: #D9D9D9"]D
[/TD]
[TD="class: xl63, width: 47, bgcolor: #D9D9D9"]E
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Master
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #D9D9D9"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]French
[/TD]
[TD="class: xl65, bgcolor: transparent"]Italian
[/TD]
[TD="class: xl65, bgcolor: transparent"]German
[/TD]
[TD="class: xl65, bgcolor: transparent"]Spanish
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #D9D9D9"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]Costs1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]130
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #D9D9D9"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]Costs2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]210
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]220
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]230
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #D9D9D9"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]Costs3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]300
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]310
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]320
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]330
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]****
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 190"]
<tbody>[TR]
[TD="class: xl66, width: 27, bgcolor: #D9D9D9"][/TD]
[TD="class: xl65, width: 47, bgcolor: #D9D9D9"]A
[/TD]
[TD="class: xl65, width: 39, bgcolor: #D9D9D9"]B
[/TD]
[TD="class: xl65, width: 39, bgcolor: #D9D9D9"]C
[/TD]
[TD="class: xl65, width: 39, bgcolor: #D9D9D9"]D
[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]Sheet1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #D9D9D9"]1
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Costs1
[/TD]
[TD="class: xl67, bgcolor: transparent"]Costs2
[/TD]
[TD="class: xl67, bgcolor: transparent"]Costs3
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #D9D9D9"]2
[/TD]
[TD="class: xl67, bgcolor: transparent"]French
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]300
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #D9D9D9"]3
[/TD]
[TD="class: xl67, bgcolor: transparent"]Italian
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]210
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]310
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #D9D9D9"]4
[/TD]
[TD="class: xl67, bgcolor: transparent"]German
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]220
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]320
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #D9D9D9"]5
[/TD]
[TD="class: xl67, bgcolor: transparent"]Spanish
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]130
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]230
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]330
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]****
[/TD]
[TD="class: xl67, bgcolor: transparent"]********
[/TD]
[TD="class: xl67, bgcolor: transparent"]******
[/TD]
[TD="class: xl67, bgcolor: transparent"]******
[/TD]
[TD="class: xl67, bgcolor: transparent"]******
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Formulas

Code: 
A1-> =IF(INDEX(Master!$A$1:$E$4,COLUMN(),ROW())="","",INDEX(Master!$A$1:$E$4,COLUMN(),ROW()))

Or

A1-> =IF(INDEX(Master!$A$1:$E$4,COLUMNS($A1:A1),ROWS(A$1:A1))="","",INDEX(Master!$A$1:$E$4,COLUMNS($A1:A1),ROWS(A$1:A1)))

Markmzz
 
Upvote 0
I must be doing something wrong, because i still can't get it to do anything :(

I put the formula in cell A1 from tab 2...i even made sure to go in a correctly link the master tab and range, but still nothing happens.

To make certain i understand this, i put the formula in Cell A1 of the tab 2 correct? Do i need to do anything special after that or just hit enter?
 
Upvote 0
ycharron said:
I must be doing something wrong, because i still can't get it to do anything :(

I put the formula in cell A1 from tab 2...i even made sure to go in a correctly link the master tab and range, but still nothing happens.

To make certain i understand this, i put the formula in Cell A1 of the tab 2 correct? Do i need to do anything special after that or just hit enter?
Click to expand...

Try this new version (array formula - use Ctrl+Shift+Enter and not only Enter):

Code: 
A1 of tab2-> =INDEX(IF(Master!$A$1:$E$4="","",Master!$A$1:$E$4),COLUMNS($A1:A1),ROWS(A$1:A1))

If the array formula didn't work, then post your formula with the range of the master tab.

By the way, what is your version of Excel (English, Portuguese or another)?

PS-> if possible, post your formula in English and in your version of Excel.

Markmzz
 
Upvote 0
thanks again for the reply!

To asnwer your questions, i'm using Excel 2010 (office Enterprise Professional) - It is in Frnehc however, but this usually doesn't affect the formulas since they still use the same namingconvention/syntax for all languages.

Ok, so i used the array formula in cell A1 of Tab2 you provided and used ctrl+shift+enter, but nothing happened. It still remains blank.

This is an exacte ctrl-c and ctrl-v of the formula from my spreadsheet:
=INDEX(IF(Master!$A$1:$E$4="","",Master!$A$1:$E$4),COLUMNS($A1:A1),ROWS(A$1:A1))

I appreciate all your help, but i also don't want you to waste time on this...for now i can manage with doing it manually. Eventually i will want to turn the whole thing into a macro, but i don't need it now :)

again, i appreciate all the help you provided!

yan
 
Upvote 0
ycharron said:
thanks again for the reply!

To asnwer your questions, i'm using Excel 2010 (office Enterprise Professional) - It is in Frnehc however, but this usually doesn't affect the formulas since they still use the same namingconvention/syntax for all languages.

Ok, so i used the array formula in cell A1 of Tab2 you provided and used ctrl+shift+enter, but nothing happened. It still remains blank.

This is an exacte ctrl-c and ctrl-v of the formula from my spreadsheet:
=INDEX(IF(Master!$A$1:$E$4="","",Master!$A$1:$E$4),COLUMNS($A1:A1),ROWS(A$1:A1))

I appreciate all your help, but i also don't want you to waste time on this...for now i can manage with doing it manually. Eventually i will want to turn the whole thing into a macro, but i don't need it now :)

again, i appreciate all the help you provided!

yan
Click to expand...
Yan,

Look at your PM Box.

Markmzz
 
Upvote 0
Thanks again Mark.

i didn't understand that i needed to copy the formula in each cell...now it works fine!

thanks again for the help

-yan
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How to automatically update a master table with tables from other worksheets ?
Replies
0
Views
455
Andre989
A
V
  • Locked
  • Question Question
Looking to pull specific rows from one sheet to another within the same workbook that will update when one is updated
Replies
1
Views
813
Fluff
Fluff
F
  • Solved
VBA Macro - Automatic Update "Master" data sheet from multiple sheet
Replies
7
Views
1K
Trebor76
T
V
  • Question Question
Import and update automatically in a master sheet filtered data from daily sheets
Replies
5
Views
587
mumps
mumps
B
  • Question Question
Copying Data from certain tabs which have different headers and are updated multiple times a week into a separate Master Tab that has a table
Replies
2
Views
351
Crystalyzer
Crystalyzer

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

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