Thread: VLookup and Combobox

Hi experts,

I'm an excel amateur but I'm in the process of building a calculator of sorts for calculating the price of a solar system.

I have a number of combobox's which refer to seperate sheets filled with data. Depending on the combobox selection, the price for that selection will be fetched from the appropriate sheet.

So for instance, if I select a 1.5kW system as my "base system" it will fetch the price $8186.00 by using this VLookup code: =VLOOKUP(BaseSystem!$E$1,BaseSystem,3). This works fine.

I have another combobox, however, where I can select an inverter upgrade. It has a virtually identical VLookup except refers to a seperate sheet (=VLOOKUP(InverterUpgrade!$E$1,InverterUpgrade,3).

The problem is that since the BaseSystem includes the price of the original inverter, in order to accurately calculate the total cost in a case where an inverter upgrade is selected, I must deduct the cost of the initial inverter from the price of the system. I'm not sure how to do this.

So basically what I want to do is add a formula in the inverter upgrade price box that will deduct the price of the base system inverter (I can create a seperate sheet with these prices) and add the price of the new inverter. Obviously if no inverter upgrade is selected it should do nothing.

How can this be achieved?

Please provide a sample workbook to understand your issue better. Btw, 'solar system' has a different meaning than what you mentioned here

Hey!

I had a look at your website, but eventually can only tell you that it is really confusing to understand your requirement. We would be able to help you out, if you can explain this process using an example. Take one item, and share with us the amount which you would like as calculated. And the process how you calculated it.

Thanks,
Vikas

See the attachment. I have used DataValidation-List for the dropdowns.

Originally Posted by johnjohns

See the attachment. I have used DataValidation-List for the dropdowns.

Originally Posted by johnjohns

See the attachment. I have used DataValidation-List for the dropdowns.

Thanks so much for puting this together.

I have a few problems though:

1) Why did you remove the combobox's and put these drop down fields in their place?

2) There seems to be a problem with the 'metering'. Certain dropdown selections are returning $0.00 when they shouldn't be (e.g. United) and others are returning #N/A errors (e.g. Citipower). The correct prices for these are $212.07 and $342.47 respectively as found in the 'Metering' worksheet.

3) At first I thought that the point of sale discount figure subtracted the original inverter price when an inverter upgrade was selected, but then I found that wasn't the case.



To explain what I'm after I'll show an example like Vikas suggested:

- I select the metering: SP Ausnet $335.92
- I select base system: 1.5KW $8186.00
- I select inverter upgrade: 2KW $866.00 *****This is where my original question comes in because adding $866 for the upgrade does not give me an accurate total in the end. What should occur is that I add $866 but then deduct the price of the original inverter. In this case, because the base system is 1.5KW, I should deduct the 1.5KW inverter price of $756 as found in cell B9 in the 'InverterUpgrade' worksheet ie ($866.00-$756.00).*****
- I select panel upgrade: 1 (190w) $484.00
- I write for customer price: $2,750

Based on my selections above, the following should automatically be calculated:

A point of sale discount of: [$8186+$335.92+($866-$756)+$484.00] - ($2,750+$335.92) = $6701.84

Out of pocket expenses: ($2,750+$335.92) = $3085.92



I hope that makes things a little clearer.

Thanks once again

1)
Data Validation List is easier and therefore I used it.

2)

My mistake of putting the formula wrongly, Sorry!
Please correct it as

VLOOKUP(C14,Metering!B2:C18,2,FALSE)

including false

3) I will look into and comeback if not somebody else helps it before.

Nevermind, it's been solved!

STC Worksheet Cell D16 (just do this once and it should follow through for the rest of the options)
Code:

=VLOOKUP(InverterUpgrade!$E$1,InverterUpgrade,3)-InverterUpgrade!$B$9

Then on the InverterUpgrade sheet, change Cell C2 to 756.

This would then give you everything you wanted. btw, I miscalculated above with the answer of $6701.84.

EDIT: No it hasn't. The formula I posted only works when the base system was a 1.5kW.. it doesn't intuitively work it out based on the selected base system. I need a code which depending on the base system selected, will deduct the base inverter price from the upgrade inverter price.