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.:)


--
londar
------------------------------------------------------------------------
londar's Profile: http://www.excelforum.com/member.php...o&userid=32970
View this thread: http://www.excelforum.com/showthread...hreadid=527960

Transposing + Absolute Reference + Multiple Worksheets = my headache
 

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

Transposing + Absolute Reference + Multiple Worksheets = my headache
 

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" wrote in
message ...

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

Transposing + Absolute Reference + Multiple Worksheets = my headache
 

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" wrote
in
message ...

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

Transposing + Absolute Reference + Multiple Worksheets = my headache
 

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" wrote in
message ...

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" wrote
in
message ...

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