I have a datasheet that has many columns that may change positions. To make it easier to use I want to create a second sheet that keeps the columns in a specified order. I can count on the column titles remaining the same, so I want to copy columns based on their title, or rather the first cell in the column matches a string.
As an example three of the columns in sheet 1 are
id name due date
But in another instance of sheet 1 they may appear as
id due date name
I want my second sheet to specify that the first row is 'id', second is 'name', etc. I attempted to use hlookup, but only found it useful for filling a single cell, dragging the formula didn't carry too well for me. Any ideas on ways to perform this using functions in excel? I'd rather not use a macro unless absolutely necessary.
Answer
=HLOOKUP(A$1,Sheet1!$A:$C,ROW(),FALSE)
As you fill the formula down, the ROW() acts as an offset to look at the next column on your table.
No comments:
Post a Comment