Results 1 to 13 of 13

20120507, 09:17 #1
 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#110 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???

20120507, 10:26 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Hi
Try this:
=IF($E$9="CRI BID DAY",D107,OFFSET(E107,0,SUBSTITUTE($E$9,"Bidder #","")*2))
zeddy

20120507, 10:32 #3
 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.

20120507, 10:45 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20120507, 10:52 #5
 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.

20120507, 11:00 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

20120507, 11:10 #7
 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?

20120507, 11:14 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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

The Following User Says Thank You to zeddy For This Useful Post:
dmaynie (20120507)

20120507, 11:18 #9
 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.

20120507, 11:22 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Hi
Is it possible for you to attach your file?
I could fix it and send it back.
zeddy

20120507, 11:25 #11
 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

20120507, 11:35 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Hi
You can send it to:
zzzwoody7@gmail.com
zeddy

20120507, 14:47 #13
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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