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

[ycharron]

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

	A	B	C	D	E
1		French	Italian	German	Spanish
2	Costs1	100 €	110 €	120 €	130 €
3	Costs2	200 €	210 €	220 €	230 €
4	Costs3	300 €	310 €	320 €	330 €

<tbody>
</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

	A	B	C	D
1		Costs1	Costs2	Costs3
2	French	100 €	200 €	300 €
3	Italian	110 €	210 €	310 €
4	German	120 €	220 €	320 €
5	Spanish	130 €	230 €	330 €

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

tab2​

Thank you in advance for any help.

yan charron

 

[James006]

Hello yan,

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

HTH

 

[markmzz]

Maybe this:

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

 

[ycharron]

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!

 

[markmzz]

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!

Here the formulas worked. Look at this:

	A	B	C	D	E	Master
1		French	Italian	German	Spanish
2	Costs1	100	110	120	130
3	Costs2	200	210	220	230
4	Costs3	300	310	320	330
****	******	********	********	********	********

<tbody>
</tbody>

	A	B	C	D	Sheet1
1		Costs1	Costs2	Costs3
2	French	100	200	300
3	Italian	110	210	310
4	German	120	220	320
5	Spanish	130	230	330
****	********	******	******	******

<tbody>
</tbody>

Formulas

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

 

[ycharron]

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?

 

[markmzz]

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?

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

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

 

[ycharron]

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

 

[markmzz]

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

Yan,

Look at your PM Box.

Markmzz

 

[ycharron]

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