attach a value to text

[expaddy]

Hi All,

I've created a drop down list and I want to be able to attach a formula to that text that will be activated when selected.

When the user selects January it should take the value multiply by the days then mutiply by temperature variation. The formula is very simple but for the life of me I cannot get it attached to the drop down list. The drop down list contains the 12 months and the inputs are variable.

Any help would be appreciated.

[NBVC]

The formula must be in a separate cell from the dropdown cell.

[expaddy]

Ok thanks but, how do I reference the formula from inside the drop down list.
So if a user picks January it will activate a certain formula.

I've also been trying IF statements i.e.
=IF(G7="January",???), can I put a formula in there or is it specifically for comparison?

Is there a way to maybe compare the left of a cell with a string i.e.
=IF(LEFT,G7,7="January",???)

Any input appreciated.

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[bentleybob]

I think what NBVC is saying is that you can't have your formula in the drop-down list, but the result of selecting something from your list can cause the calculation you want to occur, just in another cell. So, for example, if your drop-down list is in Column A, your formula can be in Column B and is activated based on the selection in Column A.

You could, I suppose, write some VBA code such that selecting "January" in cell A4 would then be overwritten by a formula, but I really don't think you want to do that. Better to have cell B4 show the results of your calculation. Otherwise, if you wanted to change the entry in A4, it would no longer show "January" as an option, only the results of the calculation for January.

[expaddy]

@Bentleybob,
Thanks for replying.

That's pretty much hit the nail on the head, I apologise for not being as clear as I should have been. I'll try to explain again.

I have a drop down list with all the months of the year and when a user selects one of those months I want that choice to activate a calculation. The calculation for each month is slightly different.

The sheet is based around the electrical cost of running a small data centre.
So if the user selects January from the drop down list I want to invoke a calculation.
The calculation is not a problem It's just linking the choice to the calculation and displaying the result in the appropriate cell.

Any help greatly appreciated.

[martindwilson]

doing the calculation next to your validation list (or create a table of months to calculations) then use vlookup to get results would probably be the easiest

[expaddy]

Thanks martindwilson for that but I'm getting a bit lost with the VLOOKUP syntax, I am trying:
=IF("","",VLOOKUP(G6,Sheet2!A1:F12,1,FALSE))
What I am trying to do is compare the chosen string in G6 Sheet1, (drop down), with information contained in sheet2 and print the result back to a cell in Sheet 1.
So I thought the starting point was A1 and end point was F12 then look back up to F1 for a value? I've most likely got that wrong.

I think I am correct in saying that the 2 double quotes match string to string but after that it's looking a bit hazy.

Any help appreciated.

[expaddy]

I've managed to get the value with:
=VLOOKUP(G6,Sheet2!A1:F12,6,)
Will this do or is it bad practice?

[martindwilson]

=IF(g6="","",VLOOKUP(G6,Sheet2!A1:F12,1,FALSE)) the if g6="" part is just so you don't get an error if g6 is blank it says if g6=blank return blank else do the vlookup
=VLOOKUP(G6,Sheet2!A1:F12,1,FALSE) as this stands it will look for a match of g6 in column a1:a12 of sheet 2 and return that value from col a (column 1 of the range a1:f12)

=VLOOKUP(G6,Sheet2!A1:F12,2,FALSE)
will look for a match of g6 in column a1:a12 of sheet 2
and return the corresponding value from col b(column 2 of the range a1:f12)

=VLOOKUP(G6,Sheet2!A1:F12,6,FALSE)
will look for a match of g6 in column a1:a12 of sheet 2
and return the corresponding value from col f (column 6 of the range a1:f12)

[martindwilson]

re your last post you need to put false in there or you may get odd results "false" means find exact match
=VLOOKUP(G6,Sheet2!A1:F12,6,false)

[expaddy]

Ok thanks for the help, I'm getting there, (I think).
I now have:
=IF(G6="","",VLOOKUP(G6,Sheet2!A1:F12,6,FALSE))
Which seems to work.

So in english:
If G6 is empty return an empty cell
else
use the value in G6
look for an exact match in sheet2 cell a1 to cell f12
if found return the corresponding value from column 6
if no match is found return "FALSE"

It's working but I just want to make sure this is the correct method.

By the by, can you force VLOOKUP to search for a certain type i.e. string, numerical, -value etc.

[martindwilson]

yep thats basicaly it vlookup always looks in the first column of your range say it was c1:f12 it would look down c1 and in this case col f would be 4. not sure what you mean by

certain type i.e. string, numerical, -value etc

[expaddy]

Hey thanks for your help it has been really appreciated, I owe you one.

As for the by the by.
I just meant to ask that could VLOOKUP be made to look through a database of different values, i.e. strings, numbers, -numbers etc and be made to only match with whatever you tell it to match.
But I suppose I answered my own question by realising that you specify what VLOOKUP looks for in the first place, DOH!!

Ayhoos thanks very much for your help it was/is greatly appreciated.

[bentleybob]

You can, if needed, test for text (ISTEXT) versus numbers (ISNUMBER).

[expaddy]

Nice one good to know.
Pretty much like Python then.