Transposing + Absolute Reference + Multiple Worksheets = my headache

hi there,

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

I basically have an inventory stock, running on the vertical is the site names, running along the horizontal is the goods. In the area 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 the axis) so that i can reference it throughout other worksheets. This reference must be dynamic, so any changes made on the first sheet will change throughout.

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

Is there anyway to do this? If i type into my other worksheet =Sheet!G10 and then try to drag downwards it will only copy what is on sheet1 running in the vertical fashion, but what i want it to do is read the horizontal. Pressing F4 so the $ comes in would work, but again only if the axis i drag corresponds to the same one i am trying 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 simple way to make an absolute reference of a transpose function would be perfect so long as it can be dynamically entered throughout the workbook.

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

Thanks a lot.

reread what i wrote it may be confusing. What i want is an easy way to copy the formulas over. I could manually reference each cell i need in 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 i click the bottom right corner and drag is VERTICALLY DOWN, i want is to change the next cell to =Sheet1!H11 NOT =Sheet1!G12 which is what it does. Adding the $ or pressing F4 does what i want ONLY if i drag in the HORIZONTAL direction, which is not where i want the new data to be displayed.

Hi

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

--
Regards

Roger Govier


"londar" <[email protected]> wrote in
message news:[email protected]...
>
> reread what i wrote it may be confusing. What i want is an easy way
> to
> copy the formulas over. I could manually reference each cell i need
> in
> 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 i
> click the bottom right corner and drag is VERTICALLY DOWN, i want is
> to
> change the next cell to =Sheet1!H11 NOT =Sheet1!G12 which is what it
> does. Adding the $ or pressing F4 does what i want ONLY if i drag in
> the HORIZONTAL direction, which is not where i want the new data to be
> displayed.
>
>
> --
> londar
> ------------------------------------------------------------------------
> londar's Profile:
> http://www.excelforum.com/member.php...o&userid=32970
> View this thread:
> http://www.excelforum.com/showthread...hreadid=527960
>

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

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

Originally Posted by 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

--
Regards

Roger Govier


"londar" <[email protected]> wrote in
message news:[email protected]...
>
> reread what i wrote it may be confusing. What i want is an easy way
> to
> copy the formulas over. I could manually reference each cell i need
> in
> 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 i
> click the bottom right corner and drag is VERTICALLY DOWN, i want is
> to
> change the next cell to =Sheet1!H11 NOT =Sheet1!G12 which is what it
> does. Adding the $ or pressing F4 does what i want ONLY if i drag in
> the HORIZONTAL direction, which is not where i want the new data to be
> displayed.
>
>
> --
> londar
> ------------------------------------------------------------------------
> londar's Profile:
> http://www.excelforum.com/member.php...o&userid=32970
> View this thread:
> http://www.excelforum.com/showthread...hreadid=527960
>

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)


--
Regards

Roger Govier


"londar" <[email protected]> wrote in
message news:[email protected]...
>
> my original data that i am trying to copy is in Sheet1, so would I
> keep
> the same format as you have written or change it to
> =OFFSET(Sheet1!.......)
>
> Thanks for the reply though, if this works you are my hero.
>
>
> Roger Govier Wrote:
>> Hi
>>
>> On your Sheet1 in the first cell to receive data enter
>> =OFFSET(Sheet2!$G$10,0,ROW(1:1)-1)
>> and drag down
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "londar" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > reread what i wrote it may be confusing. What i want is an easy
>> > way
>> > to
>> > copy the formulas over. I could manually reference each cell i
>> > need
>> > in
>> > 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 i
>> > click the bottom right corner and drag is VERTICALLY DOWN, i want
>> > is
>> > to
>> > change the next cell to =Sheet1!H11 NOT =Sheet1!G12 which is what
>> > it
>> > does. Adding the $ or pressing F4 does what i want ONLY if i drag
>> in
>> > the HORIZONTAL direction, which is not where i want the new data to
>> be
>> > displayed.
>> >
>> >
>> > --
>> > londar
>> >
>> ------------------------------------------------------------------------
>> > londar's Profile:
>> > http://www.excelforum.com/member.php...o&userid=32970
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=527960
>> >
>
>
> --
> londar
> ------------------------------------------------------------------------
> londar's Profile:
> http://www.excelforum.com/member.php...o&userid=32970
> View this thread:
> http://www.excelforum.com/showthread...hreadid=527960
>