Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <img src=/S/help.gif border=0 alt=help width=23 height=15> Why do I feel like such a dope doing this Excel stuff??? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Thank goodness for this lounge!
    Okee dokee... Here's what I have:<UL><LI>Formatted Sheet has column headings in row 6... (this sheet has all kinds of extra columns and calculated cells, etc)
    <LI>Data Sheet has column headings in row 1... (this one is just a simple data table)[/list]Here's what I need to do:
    <UL><LI>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... <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    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...

    <table border=1><td>Client</td><td>Issue ID</td><td>3</td><td>2</td><td>3</td><td>2</td><td>3</td><td>2</td></table>

    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?? <img src=/S/please.gif border=0 alt=please width=31 height=23>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filling data from 1 sheet to another... (2000 SR-1)

    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

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling data from 1 sheet to another... (2000 SR-1)

    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!... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    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?!?! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    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... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Anyway... Happy Friday!!... Thanks again!... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •