I have a range that has a lot of blank spaces and I want to make a list out of that range, but I want that list to only include the cells with data in them. How do I do this? Attached is an example.
I have a range that has a lot of blank spaces and I want to make a list out of that range, but I want that list to only include the cells with data in them. How do I do this? Attached is an example.
You could derive a second list with the numbers bunched up, and use that as the source of your data validation. For example, put this formula in H1:
=IFERROR(SMALL(Numbers,ROW()),"")
and copy down to H9. Then your data validation could be for this source:
=INDIRECT("h1:h"&COUNT($H$1:$H$9))
Hope this helps.
Pete
How do I specify the range I want to include in the first formula?
You already had a named range called Numbers in your example sheet, referring to the range:
=Sheet2!$A$1:$A$9
So, you can either specify the range instead of that name, or use a named range like I have.
Hope this helps.
Pete
Oh yeah, I forgot haha. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks