Getting Cell References to Change Orientation

[nkissam]

I would like to reference cells in another tab in my worksheet, but change the orientation.

For example, in Tab 2, I want to make rows A1:A26 equal to columns A1:Z1. Do I have to click back and forth between worksheets 26 times, or is there a way around this?

Thanks

[duane]

put this in cell a1 sheet1 and copy down to row 26

to get data in cols a:z in row 1 of sheet2

=INDIRECT("sheet2!R1"&"c"&ROW(),FALSE)

[nkissam]

How would I write the formula for the opposite situation...

I want sheet 2, cloumns a:z to reference sheet 1, rows a1:a26.

thanks again

[duane]

=INDIRECT("sheet2!R"&COLUMN()&"c1",FALSE)

[duane]

now I am not sure I answered your question- you want to enter the formula in sheet2 columns a:z I take it?

that would be

=INDIRECT("sheet1!R"&COLUMN()&"c1",FALSE)

[nkissam]

I want to multiply a values in a sheet called FEES, cells D9:D20 by a value in another worksheet called TIME EST., cells C9:V9. I want the product to appear in the worksheet called TIME EST, cells C10:V10.

So, the formula would start in C10 in the worksheet called TIME EST, and I want to drag it across to V10 and have it populate, even though the references to the FEES worksheet need to change their orientation from rows to columns.

Thanks!

[duane]

I think I've got this straight - this is entered in Time est cells c10

=INDIRECT("Fees!R"&COLUMN()+6&"c4",FALSE)*C9

to multiply cell c9 (above c10) by cell d9 on sheet Fees

then in cell d10 it multiplies cell d10 by cell d10 on sheet Fees

[nkissam]

Just wish I understood how to recreate this for multiple situations just like this in many worksheets.

[duane]

no genius here....just look at help on indirect function, and also look at r1c1 reference (as opposed to a1)

[nkissam]

I have been studying the R1C1 reference in the HELP, and trying to expand upon this formula to make it useful in other worksheets. I think I almost have it but am still stuck on how to get the reference right.

In column cells C8:infinity in worksheet TE, I want to reference rows B9:infinity in worksheet FEES. The important part is I want to drag the formula across the columns in worksheet TE and have it populate with the decending rows in cloumn B on the FEES sheet.

I think if you write this one for me I will get it. The last formula had to do with multiplication so I am having trouble modifying.

Thanks,

[duane]

in cell C8 on sheet TE

INDIRECT("Fees!R"&COLUMN()+6&"c2",FALSE)

results in the equation

=Fees!R9C2 where the 9 comes from column() being 3 (column C) +6

which is the same as =Fees!B9 (column 2 (B), row 9)

when you go to D8 on TE, the column is now 4

so you get =Fees!R10C2

which is

=Fees!B10

[r3k5e71]

thanks for breaking down the function reference duane, with that last post, I finally figured out the function...

[duane]

you are quite welcome.....best way to learn is to spend some time figuring it out and getting assistance only as needed.