Need help asap - Excel

[Sephiroth]

Ok... I have till Monday to get this done. I'm working on part of my Excel class's final exam...

I have most of it done, but the last major part of it here is driving me up the wall. I'll attach the file here... it will make a lot more sense if you're looking at it when you read this.

When you click in cells A3 and B3, you get drop down lists of processor brands, amd or intel, and models depending on which brand you selected, same thing with ram, video cards, and mobos. That was all easy enough through data validation / indirect validation lookups...................

Here is what I can't figure out an easy way to do to save my life........:

In cell D4 (and the rest of the price cells for that matter...) it needs to be able to figure out what model is listed in cell B3, find it in the data below, move over one cell, grab it's price, and multiply it times the quantity the user put in cell C3. The only way I can think of to do this is a ton of visual basic that basically says if B3 = this then price = # * quantity.... that's a hell of a lot of raw code to do though give all of the possible choices, can anybody figure out anything easier?

Thanks in advance... you'll be a lifesaver.

[Sephiroth]

file

[LukeMan]

What is looks like you want to do is a "lookup" or "vlookup" or "hlookup". Real quick what that does it based on what is entered in a cell excel is told to find that value in a range then return what is finds in a cell or row. You could then do the qty*price piece

Don't really have the time to work it out for you but you can if info in excel under those terms. If you have a problem (took me a bit before I "got" it way back when) I will be around over the weekend.

L.

[Sephiroth]

I knew about the vlookup() function, but, how would that account for all of the variables there? I'd have to somehow nest 8 or 9 of them in one really really really big if statement so it's grabbing the right ranges.

[Dudelive]

I couldn't get this to work on my Excell 2003, but maybe the conditional format is the way to go with the vlookup thing. I couldn't get it to work. but maybe its because its beta? I'm going to play with this and if I can get it i'll post it.

Also try the FIND command, if you want to make it longer you could do something like this for ALL of them

=IF(FIND("3.06 GHz",B3,1),SUM(C3*420),IF(FIND("2.8 GHz",B3,1),SUM(C3*320),and so forth LOL ))))
that would be really bad coding, but it wouldn't work, because i'm tired! so i'm gonna play with it later. let me know if you get it working before I wake up.

Later
~Dude

[LukeMan]

Okay, got me hooked. To late tonight for me (0 dark 30 comes pretty early). See what I can do........

L

[[SiN]]

Ok, I had to jump onto this one :P

The only other way I can think to do this other then VB would be to do as Dudelive said VLOOKUP. Which does make a perty long if statement but does work.

such as for the processors

Code:

=IF(A3=A11,VLOOKUP(B3,A18:B30,2,FALSE)*C3,IF(A3=A12,VLOOKUP(B3,D18:E23,2,FALSE)*C3,IF(A3=A13,VLOOKUP(B3,G18:H31,2,FALSE)*C3,IF(A3=A14,VLOOKUP(B3,J18:K27,2,FALSE)*C3,"Not Found"))))

But here ya go, I put it together on the one you posted for us to mess wit
You will notice that when you change the main item, such as processor brand the price gives you a N/A till you change the processor speed to something that corresponds with the processor chosen. I dont know how to get around that, but it does work

[Sephiroth]

damn!, now that's a hell of an if statement lol, i got around it this morning with select case statements in VB, but it isn't working right half the time for some reason...... not sure what i have wrong in my code, could be anything though, took me and 2 teachers half an hour just to get it to work, /me uses this , thanks faqer :P

[Sephiroth]

here's the VB me, my VB teacher, and my Excel teacher managed to come up with in a calculate totals button:

Quote:

Dim mrstMatchValueStart As String
Dim mrstMatchValueEnd As String

Private Sub cmdCalculate_Click()
 
    Select Case Range("A3").Value
   
        Case "Athlon"
          mrstMatchValueStart = "A18"
          mrstMatchValueEnd = "B30"
       Case "Duron"
          mrstMatchValueStart = "D18"
          mrstMatchValueEnd = "E23"
       Case "Pentium4"
          mrstMatchValueStart = "G18"
          mrstMatchValueEnd = "H31"
       Case "Celeron"
          mrstMatchValueStart = "J18"
          mrstMatchValueEnd = "K27"
    End Select
   
   Range("D3").Value = "=Vlookup(B3," & mrstMatchValueStart & ":" & mrstMatchValueEnd & ",2,False)*C3"
   
    Select Case Range("F3").Value
   
       Case "Epox"
          mrstMatchValueStart = "J43"
          mrstMatchValueEnd = "K50"
       Case "Intel"
          mrstMatchValueStart = "A43"
          mrstMatchValueEnd = "B60"
       Case "Asus"
          mrstMatchValueStart = "D43"
          mrstMatchValueEnd = "E49"
       Case "Abit"
          mrstMatchValueStart = "G43"
          mrstMatchValueEnd = "H51"
    End Select
   
   Range("I3").Value = "=Vlookup(G3," & mrstMatchValueStart & ":" & mrstMatchValueEnd & ",2,False)*H3"
   
        Select Case Range("K3").Value
   
        Case "PC133"
          mrstMatchValueStart = "A73"
          mrstMatchValueEnd = "B76"
       Case "PC2100"
          mrstMatchValueStart = "D73"
          mrstMatchValueEnd = "E75"
       Case "PC2700"
          mrstMatchValueStart = "G73"
          mrstMatchValueEnd = "H75"
       Case "PC3200"
          mrstMatchValueStart = "J73"
          mrstMatchValueEnd = "K74"
       Case "PC800"
          mrstMatchValueStart = "M73"
          mrstMatchValueEnd = "N75"
    End Select
   
   Range("N3").Value = "=Vlookup(L3," & mrstMatchValueStart & ":" & mrstMatchValueEnd & ",2,False)*M3"
       
    Select Case Range("A7").Value
   
        Case "ATI PCI"
          mrstMatchValueStart = "A87"
          mrstMatchValueEnd = "B89"
       Case "ATI AGP"
          mrstMatchValueStart = "D87"
          mrstMatchValueEnd = "E98"
    End Select
   
   Range("D7").Value = "=Vlookup(B7," & mrstMatchValueStart & ":" & mrstMatchValueEnd & ",2,False)*C7"

End Sub

[DoG]

Hey Seph, you said that lucky's statement was long but christ, thats even longer :P