Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula to set up multiple transposing work sheets
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula to set up multiple transposing work sheets
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 |
#3
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
See/work with multiple sheets at the same time | Excel Discussion (Misc queries) | |||
how to print multiple work sheets using vba | Excel Discussion (Misc queries) | |||
how to sum cells from multiple work sheets | Excel Worksheet Functions | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Multiple Work Sheets | Excel Worksheet Functions |