vLookup with check boxes?

[SilkyNick]

Well to start off I am using Excel 2007, I am currently trying to figure out how exactly to make my idea work...I was given the general idea a bit ago and at first in my mind it sounded like a wonderful idea, but now I can't seem to get a hold of that person to ask them any questions on how I could actually do so.I have an invoice template set up across two sheets (Invoice & Price Table), on the first page is the actual invoice that needs to be filled in. I have made an userform for being able to fill in the invoice number,what type of test was done, and also things like hours and miles (an their costs per also)

. Now I am wanting to try and get it to where I can have the form look at the type of test cell and from there select the price located on the second sheet that is appropriate to that type, the thing is, there are two possible prices that it can be depending on if the company wanted the results "rush". I was suggested that I could use a check box on the invoice to indicate in the spreadsheet that it is a rush, and somehow link that in with a vlookup to be able to get what I wanted, but I'm not sure exactly how I would go about doing that.

I will upload what I am working with so you can get an idea if you'd like. No I'm not expecting you to do it, but if you do, please let me know the steps you took to get there since I really do in the end want to be able to do this later for similar projects.

 

[SilkyNick]

I currently have the formula in the Analysis Charges section, Sheet2!B!26 is where the True/False cell for the check box is located.

When I put the formula in say F40 (First Analysis Charges price cell) it will replace it permanently with the fix value from the table, so essentially it does what I need it to, but afterwords it wont change with the check box or changing the procedure.

I am probably over thinking this and forgetting something simple...

 

[scott_n_phnx]

For your formula, try changing the Sheet2! to Invoice! and see if that helps. Also, try removing the ' before and after Price Table (but only make one change at a time).

 

[SilkyNick]

Okay, tried doing in one change at a time, and now it is telling me #name since taking the ' from before and after price table.

=IF(Invoice!B26=TRUE,(VLOOKUP(D25,Price Table!B2:E65,4,FALSE)),(VLOOKUP(D25,Price Table!B2:E67,2,FALSE))) is now what it currently reads.

 

[scott_n_phnx]

Let's see if this works. Here is the screenshot. See if this helps. Test3 is just my version of the Type Analysis.

 

[SilkyNick]

Okay, so I moved the formula to a cell away from everything and that one stays even when I change the name and check/uncheck without it removing the formula afterwords....I have also tried changing the F40 format back to general, no luck.

OH DUH! give me a minute I may have figured this out!

Nevermind....thought maybe my named ranges mattered, but that didn't change it either...I am confused because it will stay in F39 just fine and it works fine, but the F40 and 41 won't hold it....

 

[SilkyNick]

Apologies for double posting, but it wont let me edit my message.

I figured it out, It was my userform keeping it from working properly, I forgot I needed to remove it because that was from my previous idea. Thank you so so much for helping me with this!

 

[scott_n_phnx]

I am glad that you were able to get it working. Let me know if you need anything else.