Sunday 15 September 2019

Transposing columnar record data to rows in Excel

http://i.stack.imgur.com/tOB2t.gif


Can I reference a cell in another worksheet like this?


Example:



Worksheet A
A1 Data1
A2
A3 Data2
A4
A5 Data3

Worksheet B (I want to copy by referencing the cells in Worksheet A)



B1 'Worksheet A'!A1
B2 'Worksheet A'!(A1+2) ~ meaning I want to copy cell 'Worksheet A'!A3
B3 'Worksheet A'!(A3+2) ~ meaning I want to copy cell 'Worksheet A'!A5 ... Note that A3 in this formula comes from the formula in B2 by skipping one row in Worksheet A.
B4 'Worksheet A'!(A5+2)



The reason for this is so that I can skip the A2 and A4 cells and be able to drag the formula in Worksheet B to extend it without the need to point manually each cell that I want to copy from Worksheet A.

No comments:

Post a Comment

How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files. Is it possible to do this in a quick way rather than doing it m...