Excel Data Validation List - Combine Static Value and Formula

neb255

Board Regular
Joined
Sep 14, 2011
Messages
62
Hello,
I am looking for a way to create a data validation dropdown list in cell G17 which combines a static value of "GBP" and a formula. The formula is a vlookup which returns a local currency, and my end goal is to provide the option to select either the result of the vlookup or GBP in the dropdown.

Screenshot 2021-06-22 174433.jpg



I have tried using the below in the Data Validation List Source but none seem to work:
=OR($C$5,"GBP") where C5 is a cell containing the vlookup
=[$C$5,"GBP"]
={$C$5,"GBP"}
[$C$5,"GBP"]
{$C$5,"GBP"}
GBP,=VLOOKUP($F$17,$C$16:$D$24,2,FALSE)
GBP,VLOOKUP($F$17,$C$16:$D$24,2,FALSE)
=VLOOKUP($F$17,$C$16:$D$24,2,FALSE),GBP
=VLOOKUP($F$17,$C$16:$D$24,2,FALSE),"GBP"
=(VLOOKUP($F$17,$C$16:$D$24,2,FALSE)),"GBP"

Using just the vlookup in the data validation source works fine, however i cannot seem to figure out how to combine the result with "GBP" as a second option.

Thanks
 

Attachments

  • Screenshot 2021-06-22 174433.jpg
    Screenshot 2021-06-22 174433.jpg
    41.7 KB · Views: 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
397
Office Version
  1. 365
  2. 2010
You could create a drop down list using Vlook to look up other currencies and plus GBP as shown below.

Then refer the drop down list in your data validation box as source.

1624393007839.png


Kind regards

Saba
 

neb255

Board Regular
Joined
Sep 14, 2011
Messages
62
You could create a drop down list using Vlook to look up other currencies and plus GBP as shown below.

Then refer the drop down list in your data validation box as source.

View attachment 41387

Kind regards

Saba
Hi Saba,
Thanks for the suggestion. I had thought about this, but was hoping for something which could be built directly into the data validation source rather than creating a separate lookup reference.
 

Forum statistics

Threads
1,141,073
Messages
5,704,140
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top