Quick question: I want to create four cells with data input limited (using the list data validation option) to a list of names. Each cell will contain only staff at a certain rank. Easy enough when there’s no change; just a lookup will do.
However, because people are coming and going and moving ranks, I need the validation list to be referencing a constantly changing list of people at each rank. I can get as far as having a fixed-size column that contains all of the names of people at a given rank. However, when I reference this in the data validation cell, it includes all of the blank cells as valid options for the user to select.
How do I create a drop-down validation list that does not include blank cells as valid options? Alternatively, how do I tell Excel that when the user selects the cell, I want the data validation option to check to see how many people are at that rank and only list those names?
I am having a bit of trouble following what you are trying to do. Maybe you could tell us what formula you are currently using that gives you the problem.
Maybe I don’t understand what you are trying to do either.
I thought you could test for a blank, store in a cell if not blank, increment and test next cell. That way you would end up with a list of non-blank cells.
Okay (I’ve given up now, since I’m off work on holiday for two weeks) - here’s what I was trying to do.
I was asked to amend a spreadsheet for budgeting time for clients. Part of this involves calculating the costs per member of staff on site. Each staff member has a rank and an associate billing rate. There are up to four members of staff on a job, each of a different rank.
The spreadsheet has four cells to enter the names of staff for the job. I was asked to find a way to automatically enter the billing rates for that member of staff and include it in the calculations for costs.
So, I have four cells for the staff names, and a calculations spreadsheet that lists the current staff, their rank and billing rate. Easy.
However … staff change ranks. What I wanted was to restrict the staff that could be assigned in each of the four ‘slots’ to those of an appropriate rank. But, since staff ranks change, the validation list for each of those four cells would have to be manually re-checked and amended for each job.
What I wanted was a way for Excel to check who was currently at what rank and allow only those names in the validation drop-down list on each cell. I worked out how to create a list of all staff members at each rank, but it unavoidably included blank cells. What I then wanted was to find a way to tell Excel to ignore blank cells when formulating the list.