"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
---