Filling data from 1 sheet to another… (2000 SR-1)

Topic

New Reply

Why do I feel like such a dope doing this Excel stuff??? Thank goodness for this lounge!
Okee dokee… Here’s what I have:

• Formatted Sheet has column headings in row 6… (this sheet has all kinds of extra columns and calculated cells, etc)
• Data Sheet has column headings in row 1… (this one is just a simple data table)
  [/list]Here’s what I need to do:
  • Get the data from one column on the Data Sheet to matching column on the Formatted Sheet
    [/list]Last month I had simply added a formula that referenced the correct Data Sheet cell in the first data row (row 2) and filled down…
    (Example: “=Sheet1!B2”)

    What I didn’t know is that the columns in the data coming in can be in a different order…. (They might add,delete or change columns before sending it to us)
    SO if last month B1 said “Client”, this month it could be “Issue ID”… so on the formatted sheet I see Issue ID’s in the Client column…

    I’m trying to use MATCH to find the matching column heading on the Data Sheet…
    =IF(ISERROR(MATCH(TRIM(BD$6),Sheet1!$F$1:$BG$1,0)),0,MATCH(TRIM(BD$6),Sheet1!$F$1:$BG$1,0))
    This works fine but that’s as far as I seem to be able to get… Each of the columns now show the number representing the column of the array that matches…

    Client	Issue ID
    3	2
    3	2
    3	2

    How do I get the cell address or value of that cell underneath the column heading …or…. heck I don’t know… I’ve tried using Offset and Address and and and and… but I can’t seem to get the formula right… I assume I just have to figure out the last bit and then fill down… but I’m stuck…
    Any suggestions??

Reply | Quote

Replies

If I understand correctly:
=IF(ISERROR(MATCH(TRIM(BD$6),Sheet1!$F$1:$BG$1,0)),0,MATCH(TRIM(BD$6),Sheet1!$F$1:$BG$1,0))
gives you the column of interest.

Then:
=Index(DataList, row()-5,IF(ISERROR(MATCH(TRIM(BD$6),Sheet1!$F$1:$BG$1,0)),0,MATCH(TRIM(BD$6),Sheet1!$F$1:$BG$1,0)))
should get you what you want.
DataList is a named range of your data (SHeet1!B2: BG100, or whatever)
I assumed since your sheet had the headers in row 6 that you start with row so I used row()-5 to get the row number of interest (formatted sheet row 7 = datasheet row 2) and then the column is from your formula

Is this what you are after? I was a little confused at your setup.

Steve

Reply | Quote

That’s perfect Steve! Thanks a million!!!

And sorry for the confusion… Unfortunately it’s not my setup… I was hired as a database/application developer for this company… Now THAT I can do!…
However, all of a sudden I’m in charge of automating a ton of manual work being done in Excel…
AND I’m not supposed to request changes to the data coming in or change the format of the data going out… Isn’t that always the way?!?!

In actual fact, this workbook contains 6 worksheets and numerous data ranges that need to be thrown in where needed in the final reports… I was trying to use INDEX but couldn’t quite get it… This solution is going to help me fix it all…
Anyway… Happy Friday!!… Thanks again!…

Reply | Quote