April 10, 2017
Attached are 3 files: my files (forum_file and forum_file2) and reference file from another topic here (excel_dynamic).
A column has cells with formulas inside them but don't return any values. The reference file (excel_dynamic) is fantastic in populating the drop down list with only those that return a value (meaning, it excludes cells with formulas but don't return any values).
However, the file is limited to one column only.
I need the drop down list to return different columns, depending on the header name. This formula works great: =INDEX(Sheet1!$A$1:$C$11,0,MATCH($A$1,Sheet1!$A$1:$C$1,0)) BUT it returns the nonblank cells.
Can someone help me? I've been stuck on this for weeks. I did find a solution that involves name ranges (as in the file: forum_file2), but I'm hoping not to do this since my actual data needs hundreds of drop down lists based on the value of another cell. If I do name ranges, I will end up creating name ranges (and their helper name ranges) for hundreds of drop down lists.
THANK YOU SO MUCH!!
June 25, 2016
See if this is what you need.
Basically I am using a formula to find which column (header) you selected and then using array formula to extract the data into another helper column that will be used by the DV. I have given the helper column a name as it is required to ignore the blank and 0 value cells when displayed in the DV.
Hope this helps.
Most Users Ever Online: 57
Currently Online: PaulFogel
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas