I have a worksheet (No. 1) which has formulae referring to a previous worksheet (No. 2) in the same workbook



Can I transpose the first worksheet (No. 1) and keep the correct referencing for the formulae ?

Bob M

Can you upload a file showing what you have and examples of what you want?

Sure
test file attached

Bob M

Attached Files

Bob's_testing.xlsm (22.4 KB, 11 views)

Maybe like the attached.

Attached Files

Bob's_testing_2.xlsm (30.1 KB, 15 views)

Thank you - exactly what I am seeking...................

I have hundreds of columns to populate

Please explain where I actually name the range
Bob M

Press Formulas (on the Ribbon) then Name Manager. Press New and write name (for example d) in the Name field. Select the Refers to field and select the relevant range (for example b_1!$A$1:$S$10) and press OK.

Thank you

I understand perfectly........

Bob M
Dunedin, New Zealand

A further question.......................
Currently, new data rows are added to tab 1 as it becomes available

For tab 1_b, I simply copy the last row down to produce the extra rows

For the b1_transpose tab, how do I accommodate new data columns from tab 1_b as the range is fixed?

Bob M

See comments in sheet b_1.

Attached Files

Bob's_testing_3.xlsm (34.7 KB, 10 views)

Thank you

Bob M

I forgot to mention that to expand the table in b_1 you can just select the cell in the bottom right corner (now S10) and press Tab.

I also forgot to mention that the formulas in b_1Transpose at present are copied to A1:J26. You will have to copy these formulas down and to the right as required. In b_1Transpose you can of course use formatting to hide zeros if you want.

Duly noted
Thanks
Bob M