Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Troublw with nested If statements...cant figure out vlookup, or renaming

    Im having issues with figuring another formula to use other than the IF statement. Excel 2007 will not allow me to use more than 7. My spreadsheet has a column that references several other tabs in the same worksheet. Maybe this will help:

    =IF($E$9="Bidder #1",G107,IF($E$9="Bidder #2",I107,IF($E$9="Bidder #3",K107,IF($E$9="Bidder #4",M107,IF($E$9="Bidder #5",O107,IF($E$9="Bidder #6",Q107,IF($E$9="Bidder #7",S107,IF($E$9="Bidder #8",U107,IF($E$9="Bidder #9",W107,IF($E$9="Bidder #10",Y107,IF($E$9="CRI BID DAY",D107," ")))))))))))

    Where Bidder#1-10 are in different columns.




    BIDDER #1 BIDDER #2 BIDDER #3 etc....

    There is a dial that I can push to choose which bidder is lowest and if they have any alternates It will show up on the tab with the formula above. Unfortunately excel wont allow me to use this formula. Any help out there???

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Try this:
    =IF($E$9="CRI BID DAY",D107,OFFSET(E107,0,SUBSTITUTE($E$9,"Bidder #","")*2))

    zeddy

  3. #3
    New Lounger
    Join Date
    May 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hey Zeddy,

    I tried that and the Value sign was returned.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    This formula assumes that in cell $E$9 you have something like Bidder #1, Bidder #2, .., Bidder #10
    I assume there is a space after Bidder and before the #.
    If there is no space, then get rid of the space in the formula, i.e. use :
    =IF($E$9="CRI BID DAY",D107,OFFSET(E107,0,SUBSTITUTE($E$9,"Bidder#", "")*2))

    zeddy

  5. #5
    New Lounger
    Join Date
    May 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hmmmm tried both and the VALUE sign keeps coming back. I wish I could attach the file to this page but its to large. I would save the sheet but if I try to save it then the formula changes to value.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Lets try this as an example, stick this formula in a blank cell:
    =CELL("address",OFFSET(E107,0,SUBSTITUTE(E9,"Bidde r #","")*2))

    What this formula will do is get rid of the "Bidder #" text (by substituting a null character i.e. ""), leaving just the numeric part of "Bidder #7" i.e 7 etc.
    Then, since your original 'formula' has each result spaced by 2 columns, we multipy by 2 to get the correct column number.
    The formula here will just return the address of the 'lookup cell' i.e. if you have "Bidder #9" in cell [E9], this formula will return $W$107 as per your required 'formula'. It will return $K$107 if you have "Bidder #3" in cell [E9]

    So try this formula first to make sure it is returning the correct cell as per your 'formula'.

    zeddy

  7. #7
    New Lounger
    Join Date
    May 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i feel like such a novice....well I am so the feeling is true. that returned as VALUE as well. Can I email you my file?

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Have a look at my file attached.
    (Copying and pasting formulas into a reply here sometimes gets the spacing wrong.)

    Change the number in cell [E9].
    I put entries in the relevant cells for Bidder #1 to Bidder #7

    If you can't work it out send your file and we can 'fix' it.

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    dmaynie (2012-05-07)

  10. #9
    New Lounger
    Join Date
    May 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Im having trouble putting two and two together. My file seems to be a bit more confusing than yours. I get yours, but to integrate it with mine is where I get lost.

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Is it possible for you to attach your file?
    I could fix it and send it back.

    zeddy

  12. #11
    New Lounger
    Join Date
    May 2012
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i tried but it is to large. and if I try to save just one worksheet it will change the formula to VALUES. If you have an email I can send it that way

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    You can send it to:
    zzzwoody7@gmail.com

    zeddy

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Thanks. I've sent an updated file back to you.
    The email address I gave is a temporary one which I don't check often.

    By the way, I should have said "Welcome to the Lounge" for your first posting.

    zeddy
    zeddy

Posting Permissions

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