Dynamic List Across Column Range

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
697
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

I am trying to generate a Dynamic List across column range based on a cell value. When I check the below formula by highlighting it and pressing Ctrl + F9 (I don't know what is it called.....Debugging????) I am getting the three list of values correctly. But when I create a name for this formula in Name Manager and use that name in my Data Validation it says "The Source currently evaluates to an error. Do you want to continue". Could some one give me some direction to sort this out please?

=OFFSET(tblSample[[#Headers],[Sample ID]],MATCH(Sheet1!$H3,tblSample[[#All],[Sample ID]],0)-1,2,1,3)

In the above formula I am generating a Dynamic List Based on value in Cell Sheet1!$H3.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
697
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Book 1.xlsx
BCDEFGHIJKL
7
8IDDescA1A2A3IDA
910ABCTuesdayMonday10#VALUE!
1020DEFTuesdayWednesday
1130GHIMondayWednesday
1240JKLTuesdayMondayWednesday
1350MNOWednesday
1460PQRMonday
1570TUVTuesday
16
17
Sheet3
Cell Formulas
RangeFormula
K9K9=OFFSET(Table7[[#Headers],[ID]],MATCH($J9,Table7[[#All],[ID]],0)-1,2,1,3)
Cells with Data Validation
CellAllowCriteria
J9:J15List=$C$9:$C$15
K10List=Find_A


The above is my example Data and the Formula in K9 is working fine when I do test by pressing Ctrl +F9. But when I make that formula as Named Range and then use that Name for Data Validation it is not accepting.
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
697
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
In my last two posts I mentioned as Ctrl + F9 which is incorrect. I select the Formula and Press only F9 and it shows the correct out put in the Formula Bar---Just to correct my information
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
697
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
1624276653743.png


See the result in Formula Bar, when I press F9 by selecting the Formula in Cell K9. It producing the list but when the formula goes into Named Range it is not accepting the Name is not accepting as Data Validation Source Range. Hope my issue is clearly explained.
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
697
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hope my question and issue is clear to all.
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

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