I have items B2 through Q2 and B17 through Q17 on six sheets. I want to
create references on a new sheet with these two sets of rows listed as
two columns, A1 through A96 and B1 through B96. Is there a way to set
this up so I only have to enter a few forumulas and then fill down?

Usually when you fill down, it changes the row, and when you fill
accross, it changes the column. Is there a way to temporarily flip this
so I don't have to manually enter all 96 references?


--
bob135
------------------------------------------------------------------------
bob135's Profile: http://www.excelforum.com/member.php...o&userid=33388
View this thread: http://www.excelforum.com/showthread...hreadid=532136

You can search this topic in Excel Help by typing "About cell and range
references". There is a complete description of using Fixed References.

Fixed Reference means when you will copy the formula fill down or fill
across the formula reference will not change.

For Exmaple:

A B C
------------------------------------------------------------
1 Pentium 4 Fixed Price Qty Amount
2 27.50 2 =sum($a$2*b2),
will be (27.50*2)

when you will copy this formula, see what will happen:

3 5 =sum($a$2*b3)
will be (27.50*5)
4 3 =sum($a$2*b4)
will be (27.50*3)
5 5 =sum($a$2*b5)
will be (27.50*5)


you can also use fixed reference on any range like: =sum($a$2*$b$2)

Hope, this is what you are looking for. Let us know, if it works for you.

"bob135" wrote:


I have items B2 through Q2 and B17 through Q17 on six sheets. I want to
create references on a new sheet with these two sets of rows listed as
two columns, A1 through A96 and B1 through B96. Is there a way to set
this up so I only have to enter a few forumulas and then fill down?

Usually when you fill down, it changes the row, and when you fill
accross, it changes the column. Is there a way to temporarily flip this
so I don't have to manually enter all 96 references?


--
bob135
------------------------------------------------------------------------
bob135's Profile: http://www.excelforum.com/member.php...o&userid=33388
View this thread: http://www.excelforum.com/showthread...hreadid=532136

Copy- Paste Special - Transpose


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...o&userid=33140
View this thread: http://www.excelforum.com/showthread...hreadid=532136

Select B2:Q2, copy, select your target range, click
Edit/PasteSpecial-Transpose. Repeat for each sheet.



"bob135" wrote:


I have items B2 through Q2 and B17 through Q17 on six sheets. I want to
create references on a new sheet with these two sets of rows listed as
two columns, A1 through A96 and B1 through B96. Is there a way to set
this up so I only have to enter a few forumulas and then fill down?

Usually when you fill down, it changes the row, and when you fill
accross, it changes the column. Is there a way to temporarily flip this
so I don't have to manually enter all 96 references?


--
bob135
------------------------------------------------------------------------
bob135's Profile: http://www.excelforum.com/member.php...o&userid=33388
View this thread: http://www.excelforum.com/showthread...hreadid=532136

Hi bob135

Two methods:

1. Create a transposed array
Select your target range & type:
=Transpose(source)
and press Ctrl&Shift + Enter
Note: source = your source range

2. Enter your untransposed formulae as per normal (copying & pasting
over the range). Then change any formulae to text by Ctrl-H and
entering an equals sign = as the find criteria and say $$$$ as the
replacement criteria. Then your table is made of values only and you
can copy and paste-special-transpose. Then reverse the find and
replace exercise to put the equals signs back. Voila - transposed
formulae and values.

Note: The Array formula is more memory intensive, less understood &
less flexible and I prefer the second method.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532136

Hmm...it gives me a #VALUE! error when i try to transpose
=TRANSPOSE(Sheet1!B2:E2) . B2:E2 contain text. It also gives #REF!
errors when I try to paste formulas onto a new sheet. How do I fix
this?


--
bob135
------------------------------------------------------------------------
bob135's Profile: http://www.excelforum.com/member.php...o&userid=33388
View this thread: http://www.excelforum.com/showthread...hreadid=532136

Give my suggestion in the other branch a play or 2
Only 2 formulas to place in A1:B1 in the new sheet,
then fill down to B96 (just like what you asked for <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Bob135,

1. Array method
Place your text in B2 to E2 on sheet1
In B2 on sheet2 enter =TRANSPOSE(Sheet1!B2:E2)
Highlight B2 to B5 on sheet2
Press F2 and then hold the Ctrl and Shift keys whilst pressing enter.
Your text should be transposed. This method will work for large tables
also although arrays can be memory intensive.

2. Convert to text / transpose / convert back to formula method
Do the conversion/transposition/conversion described on the same sheet
(sheet1) first. Then only when it is working move it (cut & paste)
onto the other sheet.


bob135 Wrote:
Hmm...it gives me a #VALUE! error when i try to transpose
=TRANSPOSE(Sheet1!B2:E2) . B2:E2 contain text. It also gives #REF!
errors when I try to paste formulas onto a new sheet. How do I fix
this?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532136

"bob135" wrote:
I have items B2 through Q2 and B17 through Q17 on six sheets. I want to
create references on a new sheet with these two sets of rows listed as
two columns, A1 through A96 and B1 through B96. Is there a way to set
this up so I only have to enter a few forumulas and then fill down?

Usually when you fill down, it changes the row, and when you fill
accross, it changes the column. Is there a way to temporarily flip this
so I don't have to manually enter all 96 references?

Another play to try ..

First, rename your six sheets to just the numbers: 1,2,3,4,5,6

Then in the new sheet:

Put in A1:
=OFFSET(INDIRECT("'"&INT((ROW(A1)-1)/16)+1&"'!B2"),,MOD(ROW(A1)-1,16))

Put in B1:
=OFFSET(INDIRECT("'"&INT((ROW(A1)-1)/16)+1&"'!B17"),,MOD(ROW(A1)-1,16))

B1's the same formula as A1's,
except pointing to the 2nd range's startpoint: B17

Then just select A1:B1, copy down to B96

A1:A96 will return the values within the range B2:Q2
from sheets: 1,2,3,4,5,6 columnwise & consecutively as required,
while B1:B96 will return likewise from the range B17:Q17
(values from sheet: 1 will be listed first,
followed by those from sheet: 2, and so on)

Blank cells, if any, within the source ranges in the six sheets
will return as zeros, but we can suppress the display of
extraneous zeros in the new sheet via clicking:
Tools Options View tab Uncheck "Zero values" OK

Alternatively, we could also use an IF construct:
=IF(OFFSET(...)=0,"",OFFSET(...))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---