Help with formula to set up multiple transposing work sheets

[Derro]

Hi Excel Gu-ru's. I am using Excel 2007. Could any one please help

an old bloke who is out of his depth. I have multiple work sheets of

prime and slave configeration containing cells with text headings, and

cells with financial data formula's that all need to be set up with the

same headings etc. A further requirement is that when at a latter

date I wish to change headings or data in the prime sheet, the

corresponding headings and data in the slave work sheets need to

change automatically along with the prime sheet. As there are many

cells to initially set up it would save many hours if I could use a

formula that I could paste and fill. A further complication is that the

headings and data in the prime sheet will be in columns and need to

be transposed when pasting and filling to cells that are in rows in the

other slave work sheets. Any help or suggestions would be

appreciated.

 

[Dave Peterson]

Say your primary worksheet is named Sheet1. You can use a formula like this in
any other worksheet:

=if('Sheet1'!a1="","",'sheet1'!a1)

Then the cell with the formula will update whenever you change the value of A1
on Sheet1.

But that's just headings.

Changing the data is much more complex. In fact, I wouldn't do it. I'd leave
all my data in one worksheet. I'd add an indicator column if I had to view data
from some subset.

Then I'd use Autofilter to show the rows that I needed to see. I could sort,
chart, create pivottables much easier this way.

But if you had to separate data into separate worksheets, you could use one of
these techniques:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[Derro]

Say your primary worksheet is named Sheet1. You can use a formula like this in
any other worksheet:

=if('Sheet1'!a1="","",'sheet1'!a1)

Then the cell with the formula will update whenever you change the value of A1
on Sheet1.

But that's just headings.

Changing the data is much more complex. In fact, I wouldn't do it. I'd leave
all my data in one worksheet. I'd add an indicator column if I had to view data
from some subset.

Then I'd use Autofilter to show the rows that I needed to see. I could sort,
chart, create pivottables much easier this way.

But if you had to separate data into separate worksheets, you could use one of
these techniques:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb