1

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.

3
  • I'd say hlookup is about right - just you'll need an offset or index under it to fill in the data. – Raystafarian Aug 5 '15 at 12:10
  • Without seeing sheet 1 to find out why this won't work, I would create 3 new columns on sheet 1 in the order you want them to appear then move from top to bottom, hi-lilting blocks of data and using drag and drop to reorganize the sheet. Make a COPY of your sheet first and edit that to ensure, you don't break it. – Tyson Aug 5 '15 at 12:11
  • Sheet one would get updated daily. My end goal is to create multiple views and not just one. Doing so manually every day would be extremely inconvenient and time consuming. – Centimane Aug 5 '15 at 12:16
1
=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.

5
  • I used some test data (ids 1,2,3 | names Dave,test,hi | Due_Dates today,tomorrow,yesterday) in sheet one, placed the formula in sheet 2 exactly as presented in cell A1 and got filled with 0. Filling it down or right results in 0 as well. The formula warns about circular reference. – Centimane Aug 5 '15 at 14:10
  • Also, I don't see how this could reorder the columns, just seems like it reproduces them as they appear. – Centimane Aug 5 '15 at 14:12
  • By replacing A$1 with the column heading I was looking for (ex. "id") this solution worked – Centimane Aug 5 '15 at 15:46
  • @Dave Sorry if it wasn't clear, with your column headers in row one, you would put this formula in A2. – Kyle Aug 5 '15 at 16:04
  • I like being able to rename the column header so I'm going to leave the column header in the formula I think so I can modify the header on sheet 2 as I please – Centimane Aug 6 '15 at 11:45
0

I run into this often. One easy way to resolve it is to have the columns refer back to the first page with offset. Assuming these variable data on Sheet1, in A1:Q499, then on Sheet 2, I would put this in cell B2:

OFFSET(Sheet1!$A$2,0,MATCH(A$1,Sheet1!$A$1:$Q$1,0)-1,499,)

For extra fancy, you could put cell protection in Sheet2, A1:Q1 that only allows values from a list, and use Sheet1 A1:q1 as that list.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.