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.

 

[scott_n_phnx]

Depending on the setup of your sheet, you can use checkboxes. I would suggest using the Linked To Cell option, and then use the VLOOKUP based on the TRUE/FALSE value. Given more info, I can assist in building the sheet.

 

[SilkyNick]

Depending on the setup of your sheet, you can use checkboxes. I would suggest using the Linked To Cell option, and then use the VLOOKUP based on the TRUE/FALSE value. Given more info, I can assist in building the sheet.

Since I can't upload the file anywhere, here are some screen shots of how it is set up. (Please note the button inside invoice is there temporarily)

Also the blank part above term is where my company information will go so I can't do much to that area

Thank you for the quick reply also

 

[scott_n_phnx]

Here is how I have it set up. As a test, I added the Type Analysis set into a Data Validation List. Next to it, I have added a User Form check box, set the Format Control Cell Link to the next cell. When the List drop box changes, it will do a vlookup to the Analysis Price. When the check box is selected, it Changed the Linked cell to True. In the Analysis Charges, it will update when it shows True. Here is the formula that I used.

=IF(C4=TRUE,(VLOOKUP(A4,Sheet2!B2:E30,4,FALSE)),(VLOOKUP(A4,Sheet2!B2:E30,2,FALSE)))

Let me know if this makes sense. If not, I can add some screenshots.

 

[SilkyNick]

Here is how I have it set up. As a test, I added the Type Analysis set into a Data Validation List. Next to it, I have added a User Form check box, set the Format Control Cell Link to the next cell. When the List drop box changes, it will do a vlookup to the Analysis Price. When the check box is selected, it Changed the Linked cell to True. In the Analysis Charges, it will update when it shows True. Here is the formula that I used.

=IF(C4=TRUE,(VLOOKUP(A4,Sheet2!B2:E30,4,FALSE)),(VLOOKUP(A4,Sheet2!B2:E30,2,FALSE)))

Let me know if this makes sense. If not, I can add some screenshots.

That actually does make pretty good sense so I am going to attempt to follow it first, but if you want you can go ahead and post the screenshots and I can come back for reference.

 

[scott_n_phnx]

I will see if I can post pictures. Since they have sharing sites blocked at work, I may not be able to do so.

 

[SilkyNick]

It's ok if not I understand that issue myself, but I do have one question I would like to ask. Does my check box need to come before Type Analysis, or will that matter as long as I set it to ask for that cell true/false before the vlookups?

 

[scott_n_phnx]

It really does not matter where the check box or the link cell are located. You just need to reference the linked box (TRUE/FALSE) in the vlookup. In my example, C4 is the linked cell. You can have the check box in A1 and the linked cell in AA75, just as long as you point to AA75 for the TRUE check in the formula.

 

[SilkyNick]

Ah ok, I have a slight issue, I have changed out the formula (Currently =IF(Sheet2!B26=TRUE,(VLOOKUP(D25,'Price Table'!B2:E65,4,FALSE)),(VLOOKUP(D25,'Price Table'!B2:E67,2,FALSE))) ), for some reason when I check the check box however, it takes the formula out of the Analysis Charges section. Is there a way to prevent that so I could freely switch between regular/rush without the need for replacing the code

I also moved my type analysis to D25, as the other space wouldn't fit the longer named processes.

 

[scott_n_phnx]

When you say that is takes the formula out of the Analysis Charges section, do you mean that it deletes the formula? If so, is the formula in the linked cell box? By that I mean is Sheet2!B26 the location of the formula?