Transposing + Absolute Reference + Multiple Worksheets = my headache

[londar]

hi there,

been searching around for days literally reading books, manuals, hel
guides and going through the excel tips page on here and still can'
find my answer.

I basically have an inventory stock, running on the vertical is th
site names, running along the horizontal is the goods. In the are
inside is the amount of the good at each particular site.

What i need to find out is how I can now transpose this data (flip th
axis) so that i can reference it throughout other worksheets. Thi
reference must be dynamic, so any changes made on the first sheet wil
change throughout.

On the sheet i am trying to transfer this information to i want th
materials running on the vertical and the sites along the horizontal.

Is there anyway to do this? If i type into my other workshee
=Sheet!G10 and then try to drag downwards it will only copy what is o
sheet1 running in the vertical fashion, but what i want it to do i
read the horizontal. Pressing F4 so the $ comes in would work, bu
again only if the axis i drag corresponds to the same one i am tryin
to copy to, and i would like to reverse the axis.

I would like to try and avoid having to create a pivot table a simpl
way to make an absolute reference of a transpose function would b
perfect so long as it can be dynamically entered throughout th
workbook.

I understand this may be slightly confusing, I can post up an exampl
of what I am working with if need be.

Thanks a lot.:

 

[londar]

reread what i wrote it may be confusing. What i want is an easy way t
copy the formulas over. I could manually reference each cell i need i
the new worksheets but i have over 32 sites and 200 goods.

What i want is when i type =Sheet!G10 in the new worksheet, when
click the bottom right corner and drag is VERTICALLY DOWN, i want is t
change the next cell to =Sheet1!H11 NOT =Sheet1!G12 which is what i
does. Adding the $ or pressing F4 does what i want ONLY if i drag i
the HORIZONTAL direction, which is not where i want the new data to b
displayed

 

[Roger Govier]

Hi

On your Sheet1 in the first cell to receive data enter
=OFFSET(Sheet2!$G$10,0,ROW(1:1)-1)
and drag down

 

[londar]

my original data that i am trying to copy is in Sheet1, so would I kee
the same format as you have written or change it t
=OFFSET(Sheet1!.......)

Thanks for the reply though, if this works you are my hero.

 

[Roger Govier]

Hi

Sorry if the data you are trying to copy from is on Sheet1, in cells
G10, H10, I10 etc. then yes the formula needs to change to
=OFFSET(Sheet1!$G$10,0,ROW(1:1)-1)