Posted to microsoft.public.excel.misc
|
|
Help with formula to set up multiple transposing work sheets
"Dave Peterson" wrote:
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 wrote:
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
Thanks a lot Dave very helpfull Now back to work!
|