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.

 

[Chris Davison]

as soon as I hit "post" I thought of something

we can still achieve what you want with "randomly"-named sheet tabs....

it will just mean a spare column somewhere.

Give me a shout, with the names of your sheets you decided on (or expect to have)

[they don't need to have those 1,2,3s in them, although if they do, that would be fab]



_________________
Hope this helps,
Chris



edit.... it'll still work when the sheet names are changed in 3 weeks time too..
This message was edited by Chris Davison on 2002-04-24 12:46

 

[inarbeth]

Thanks for trying Chris. Can anyone else help?

 

[Chris Davison]

[No message]

 

[inarbeth]

Thanks Chris but I need to be able to rename sheets. Can anyone help with some VBA? Cheers.

 

[whoisdriving]

Dear Inabeth,

check out

http://exceltip.com/st/Copying_Form...out_Changing_the_Relative_References/127.html

It basically says that you have to know the 1Row1column cell and the lastRowLastColumn cell and the number of rows and columns.

You highlight the opposite, so if your original data is 3columnsx2rows, you need to highlight the area you want to put the information with 2columnsx3rows. Then click "=" sign and type TRANSPOSE, hit CTRL-A, enter the 1strow1stcolumn:lastrowlastcolumn, then hit CTRL-SHIFT-ENTER.

AND THAT'S IT!!!

 

[inarbeth]

Cripes, six and a half years have passed since my last post on this. Have forgotten why are asked this Q but I'll have a look at the link.
Thanks