using the =page! feature to transpose a row to column

[phillipdanbury]

Hello,

I have a series of excel sheets wherein I use formulas to calculate responses to text pasted in from other documents.

The text is pasted in columns in each sheet. This renders final calculations listed in a row at the bottom.

I have one master sheet where I list, in columns, all of the final calculations.

I prefer to use the feature that permits the text to be translated from the various sheets directly to the master page (for instance: =Page!H10).

My problem: when I enter the formula for excel to find the proper cell on one of the many sheets, then drag the formula DOWN, excel reads the cells DOWN. Usually, I can correct this by entering a few instances of the desired pattern in contiguous cells and then dragging it in any direction.

For instance, I enter =Digits!C10, =Digits!D10, =Digits!E10 in three cells from top to bottom. Then, I drag over the three to see if excel continues my pattern. It doesn't.

How can I construct a column of data on one page from a row of data on another page?

Thank you,
Phillip

[protonLeah]

=INDIRECT("Digits!C"&(ROW() + x ))
where x depends on the row the formula is in and the column you want to start with

[phillipdanbury]

Hello,

Thanks for your reply.

I was able to get the formula working. I used the following:

=INDIRECT("Digits!C"&ROW()+12)

Then, for subsequent rows:

=INDIRECT("Digits!D"&ROW()+11)
=INDIRECT("Digits!E"&ROW()+10)
=INDIRECT("Digits!F"&ROW()+9)

However, when I attempt to drag the formula down to generate additional fields, excel merely copies the existing formulas. Is there any way to get this formula to generate?

[protonLeah]

Try:

Please Login or Register  to view this content.

[phillipdanbury]

Hello,

Thank you that worked very well.

Good day,