Sadhana has attended:
Excel Introduction course
Excel Intermediate course
Formula
I have data in multiple cells that goes across a single row.
I'd like to reference this data in a separate worksheet in multiple cells across a single column instead i.e. transposed.
i.e.
'Data' Worksheet:
A B C D E
1 100 90 120 150 110
2
'Reference' Worksheet:
A
1 ='Data'!A1
2 ='Data'!B1
3 ='Data'!C1
4 ='Data'!D1
5 ='Data'!E1
When entering a formula in A1 of the 'Reference' sheet and filling down, Excel does not increment the Column letter - only the row number, e.g.:
A
1 ='Data'!A1
2 ='Data'!A2
3 ='Data'!A3
4 ='Data'!A4
5 ='Data'!A5
Is there any way of getting excel to increment the Column number instead as required?
RE: Formula
Hi Sadhana,
Thank you for the forum question.
If I understand you right the functions below can do the job. Type this in A1 on the destination sheet and drag down the fill handle.
=INDIRECT(ADDRESS(1,ROW(A1),4,,"Data"))
Please let me know if this not makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
