How to use Formula in DataValidation List Source

GregM

New Member
Joined
Jan 15, 2005
Messages
14
Hi friends,
I need to use a formula in the Source: of my Data Validation to include the Sheetname for my drop down list.
The following formula should work if someone can please show me how to properly QUOTE it. I get very confused trying to put quotes within quotes.

To be clear, the Sheetname that has my data is DataDec2018. The Sheetname I am in currently is Dec2018.

OFFSET(DataDec2018!$I$2,,,DataDec2018!$F$3) <-- This works fine, but I'd have to re-type the month for each different tab.

OFFSET("Data"&RIGHT(CELL("filename"),7)!$I$2,,,"Data"&RIGHT(CELL("filename"),7)!$F$3)

This will allow me to have multiple tabs and use the same formula: DataJan2019, DataFeb2019, etc.

Thanks in advance for any assistance provided.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
OFFSET("Data"&RIGHT(CELL("filename"),7)!$I$2,,,"Data"&RIGHT(CELL("filename"),7)!$F$3)

Okay, it just needs a little help from our INDIRECT function to make it all text.

OFFSET(INDIRECT("Data"&RIGHT(CELL("filename"),7)&"!$I$2"),,,INDIRECT("Data"&RIGHT(CELL("filename"),7)&"!$F$3"))
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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
Back
Top