Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating from Multiple Spreadsheets (Excel2002)

    <P ID="edit" class=small>(Edited by greenlee on 23-Sep-06 20:29. clarification of need)</P>Hi All,
    I am trying to create a test template by using info from 2 separate spreadsheets. One spreadsheet has the criteria and the other has the value for the criteria. I need to enter the value (1,2,3,etc on sheet2) for the criteria (sheet1) and have the criteria populate into the test template (sheet3). See the example.
    Thank you in advance.

  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: Creating from Multiple Spreadsheets (Excel2002)

    I don't understand what you want on Sheet3 so I made guess. Is this what you want? SOme of the the criteria (those >4) give a reference error since the range is not large enough...

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Sorry for the confusion.
    For instance: I need to enter a #1 into sheet3 and have the criteria for the value populate into the sheet3. I highlighted some points in the attachment. I hope I haven't made things worse.
    Currently I am going each cell one by one for each test case to populate the criteria that cooresponds to the #s. Whew!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    See the attached workbook. I made the spelling of the entry names consistent (e.g. override instead of overide), and used a combination of INDEX, MATCH and VLOOKUP.
    In Sheet3!A1, just enter a number (from 1 to 4); the number format will display it as "Test Case #n" where n is the number you entered.

  5. #5
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Thank you Hans,
    I typed a 3 in "Products" in Test page and only the 3 shows. I am probably going backward on this. I think I need to make Sheet2 represent Sheet then create the sheet3 ?? As you can tell I am sooo a beginner.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Where exactly do you want to enter a number?

  7. #7
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    I'm a nut! I found what you did. And it is great. I get it now.Thank you mucho, mucho!

  8. #8
    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: Creating from Multiple Spreadsheets (Excel2002)

    I like how you used MATCH to determine the column so that the formulas could be copied easier. I thought about doing this, but was afraid that the descriptions in Sheet3 might not always match the column headings in Sheet2.

    I know when I create datatables and output similar to this the descriptions generally do not match, since I use more abbreviations in the column headings and more complete names in the "output". Often I will use an intermediate cell which has the column number and bypass the MATCH altogether, so the formula may be copied, but it is also easily updatable if the 2 items do not match.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    The descriptions and column headings didn't match completely. I took the liberty of making them equal, since the difference was mostly due to spelling variations and/or an extra space. Having intermediate cells to match the descriptions and columns is a very good solution too.

  10. #10
    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: Creating from Multiple Spreadsheets (Excel2002)

    Hans' formulas have an advantage over mine is that they "lookup" the heading that matches the value to the left of the cell and thus you can just create 1 and copy it into the others...

    Steve

  11. #11
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Yes. I found that out after I reviewed the first 15 tests. I need to have the steps for the LOOKUP, MATCH. I can't seem to get it to work.

  12. #12
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Thank you all again.
    I tried all night to get the formula to work. I cannot seem to find which cell and which sheet you created the function. I also was unable to duplicate the formula using the formula functions. Can you detail the steps you took?
    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    No longer confused!!!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> I slept on it and things were easier to figure out. I used the formula that Steve gave me
    =INDEX(OAT!B2:B224,INDEX('Allocation Matrix'!B2:B224,'Test Case Template'!C4)) for product,
    =INDEX(OAT!C2:C224,INDEX('Allocation Matrix'!C2:C224,'Test Case Template'!C4)) for product description, etc
    and manipulated it for each cell in sheet3. I now have the formula and can complete the hundreds of sheets I must create. HOORAH FOR THE EXPERTS!!!!
    Thank you all for helping me along.

    Ok, I danced to soon. After reviewing the tests, I noticed that certain tests are not pulling the correct data. I need to try the INDEX,LOOKUP, MATCH function but whe I tried it in my spreadsheet it doesn't work...
    Shoot! Please help..

  13. #13
    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: Creating from Multiple Spreadsheets (Excel2002)

    Build it first as intermediate parts of the formula to make sure each section is what you want. After those are all worked out then combine them.

    Steve

  14. #14
    New Lounger
    Join Date
    Sep 2006
    Location
    Jacksonville, Florida, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Thank you Steve but I need the steps.. Build what how? :-)


    Here is another workbook to show me if you can...

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating from Multiple Spreadsheets (Excel2002)

    Why do the products have negative IDs in Sheet1 this time?

Page 1 of 2 12 LastLast

Posting Permissions

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