Transpose Links from individual sheets to master sheet

[inarbeth]

I have multiple sheets on which users enter data in Cells B3 to B21 on each sheet.
I have a master sheet where the information entered on individual sheets is copied by links. On the master sheet the information is displayed in Rows. Thus data on Sheet1 B5:B25 is displayed in Row 6 cells H6:Z6 of Master Sheet, data on Sheet2 is displayed in Row 7 cells H7:Z7 etc. The formula in H6 of the Master Sheet is =Sheet1!B5, in H7 it is =Sheet2!B5 etc. It is of course not possible to use Autofill to copy formulas across on the Master sheet. It can be done manually but there are hundreds of cells to fill.
Is there an elegant way to copy the formulae on individual sheets onto the Master Sheet. I want to be able to repeat the procedure later if additional sheets need to be added.

[jrnyman]

You could use a simple macro to copy what you need and pastespecial with transpose selected to paste the column data into a row. If you give more information, I can help with the code. Either sample data, or knowing how to locate the cells to copy and where to paste (eg. blanks cells, formatting, something special)

[inarbeth]

What I am trying to do effectively is to Paste a transposed link. Sheets 1, 2, 3 etc will have data added to them from time to time. Paste Special allows you to paste a link or to transpose but not to do both. I do not want simply to copy the data but to have it linked to the Master Sheet.I hope that clarifies the query.

[Chris Davison]

On 2002-04-21 11:18, inarbeth wrote:
It is of course not possible to use Autofill to copy formulas across on the Master sheet.

Everything's possible within Excel...

Are your sheets actually still called Sheet 2, Sheet 3, Sheet 4 or have they been renamed.... if they have some sort of incremental logic, we can solve your problem via an autofill....

what are your sheet names ?

[inarbeth]

Thanks Chris. I was forgetting the first rule about Excel: "The answer is Yes"!! When the spreadsheet is ready to be used the sheet names will be named after properties or the towns they are in. However for the purpose of setting up the workbook I have called them Property1, Property2, Property3 etc. I can live with them being called Sheet1, Sheet2 etc at this stage if that would help. The Master sheet is called Properties.

[ This Message was edited by: inarbeth on 2002-04-23 00:59 ]

[Chris Davison]

On 2002-04-21 11:18, inarbeth wrote:
I have multiple sheets on which users enter data in Cells B3 to B21 on each sheet.
I have a master sheet where the information entered on individual sheets is copied by links. On the master sheet the information is displayed in Rows. Thus data on Sheet1 B5:B25 is displayed in Row 6 cells H6:Z6 of Master Sheet

I'm having a look at this now.... but can you quickly clarify the range on the individual sheets.... your examples above are in dichotomy with each other :

is it "Cells B3 to B21" on each sheet

or "B5:B25" is displayed

[Chris Davison]

Assuming you manage to get your sheets all into a single workbook, and assuming all your sheets' tab names end in numerics and are in a logical order (ie Chris1, Chris2, Chris3 or Property1, Property2, Property3 etc etc)

try this in your master sheet, cell H6 :

=INDIRECT("Sheet"&ROW()-5&"!B"&COLUMN(E:E))

it allows for copying across and down.

(The row() function will return the row number, in the first instance "6", which when copied down becomes 7 then 8 then 9 etc etc.... this is appended to the "Sheet" with the & in indirect...thus : sheet1 sheet2 sheet3 etc etc

the "B" remains static in the formula

the column function returns the number of the column. in this case e=column # 5, so we get 5. However, as it's copied across, it becomes F and G and H etc etc, or 6,7,8,etc... appended to the B, becomes B5 then B6 then B7 then B8 in each formula

The =indirect function calls a cell represented by the result of the textual representation within the arguements (explained above)

I know I haven't explained that too well !

[Jack in the UK]

Chris

NEVER put yourself done ever you work is spot on... and worth the effort..

shalll we patant @anythings possible in excel!

Nothing wrong we the last 50 posts i tireless read that youve posted.. ive told you publically as its not a secret... keep going friend!

[inarbeth]

Thanks Chris. Sorry about the error in describing the range. It should have been B3 to B21. Your method works but only if the Sheet names remain as Sheet1, Sheet2 etc.
I have found a way to copy the formulas which perhaps someone can provide a macro to automate. It is rather crude and involves the following:
1. In another cell (could be C3 but perhaps better somewhere else where it won't interfere with other information on the sheet), enter the formula =B3.
2. Autofill that down until =B21.
3. Use find and replace to find the equal sign and replace it with a text string that won't appear elsewhere, such as ZZZZZ (I would want to confine the find and replace to just the required cells since there will be other formulae on the sheets).
4. Copy the cells containing the ZZZZZ.
5. Select H6 on the master sheet. Use Paste Special, Transpose.
6. Select find and replace to find the ZZZZZ and replace with the equal sign.

This is rather long-winded and there is probably a more elegant way to do what I need.

I would want to be able to rename the sheets after the operation (and better still at any time). Doesn't Excel still know the Sheets as Sheet1, Sheet2 etc even if the tabs are renamed?
Many thanks for your help.
Ian

[Chris Davison]

On 2002-04-24 01:28, inarbeth wrote:
Thanks Chris. Sorry about the error in describing the range. It should have been B3 to B21. Your method works but only if the Sheet names remain as Sheet1, Sheet2 etc.
I have found a way to copy the formulas which perhaps someone can provide a macro to automate. It is rather crude and involves the following:
1. In another cell (could be C3 but perhaps better somewhere else where it won't interfere with other information on the sheet), enter the formula =B3.
2. Autofill that down until =B21.
3. Use find and replace to find the equal sign and replace it with a text string that won't appear elsewhere, such as ZZZZZ (I would want to confine the find and replace to just the required cells since there will be other formulae on the sheets).
4. Copy the cells containing the ZZZZZ.
5. Select H6 on the master sheet. Use Paste Special, Transpose.
6. Select find and replace to find the ZZZZZ and replace with the equal sign.

This is rather long-winded and there is probably a more elegant way to do what I need.

I would want to be able to rename the sheets after the operation (and better still at any time). Doesn't Excel still know the Sheets as Sheet1, Sheet2 etc even if the tabs are renamed?
Many thanks for your help.
Ian

Ian,

hmmmmmm

the "sheet" name formula was just a sort of temporary solution. If your sheets were eventually named property1, property2, property3 etc, we could still accomplish what you require pretty easily as the 1,2 and 3 etc are what matter in the logic of the formula

if your sheets end up with names like finrep, budget, violin, milk then I can't automate it as there's no "logic" in the names.

(that's just me though, someone else might be able to)

if you can guarentee that your sheets begin with 1,2,3,4 etc etc or end with 1,2,3,4 etc
(ie finrep1, budget2, violin3, milk4 or 1finrep, 2budget, 3violin, 4milk) then we can very very easily just tweak my previous formula, anything else and we're either doing stuff manually (I will be forced to sue you) or you'll require some VBA coding.

what do you reckon ? if you don''t want to tie yourself down to having your sheet names structure "dictated" to you, I can understand !