I need to reference cells in a row on one sheet as a column in another

travellerva · Aug 14th 2021

I have a row of 50 cells in one sheet and want to replicate those formulas in a separate sheet but as a column rather than a row. I'm trying to find a way of setting up = formulas in the second sheet without having to manually enter =E6; =F6; =G6 etc. I've tried copying and moving the row and transposing the copy formulas but no luck. There must be an easy way!!!

**alansidman** · Aug 14th 2021

How about a small sample for us to work with? No pictures! Upload a sample file.

**NBVC** · Aug 18th 2021

You can try something like this:

Assuming your items start in E6 and go rightward to H6... then in your new sheet try something like:

=INDEX(Sheet1!$E$6:$H$6,ROWS(Sheet1!A$1:A1))

copied down.

Adjust as necessary.

tr1face · Aug 19th 2021

How about referencing each cell as =Sheet_name!Cell_address ?

I need to reference cells in a row on one sheet as a column in another

Participate now!

Share