Data Validation to not show blanks at the end

[Godwin117]

I have below formula, when I click ok after entering the formula into that data validation source, it shows the formula as the options and not the values that should be there. Not sure if anyone else has had an issue like this. Any help would be greatly appreciated.

=OFFSET(Data Validation!$K$2,,,COUNTA(Data Validation!$K:$K)-1)

 

[Jeffrey Mahoney]

Hey Godwin. Are you trying to say that when you use the pulldown in the cell, it shows the formula that you added in the data validation and NOT the values you wanted?

What is the the range Data Validation!$K:$K? This will help me duplicate your issue.

Can you double check that the formula in the data validation has the = (equal) at the beginning.

 

[Godwin117]

Hey Godwin. Are you trying to say that when you use the pulldown in the cell, it shows the formula that you added in the data validation and NOT the values you wanted?

What is the the range Data Validation!$K:$K? This will help me duplicate your issue.

Can you double check that the formula in the data validation has the = (equal) at the beginning.

Yes, there is an equal sign in at the beginning of the formula. The range of data is on sheet "Data Validation" from K2:K50, as there won't be more than 50 in the list. I there was a couple spaces at the beginning, now it's giving me "A named range you specified can not be found".

 

[Jeffrey Mahoney]

Answer to my first question??

Please copy the Sheet name by double clicking on the sheet tab and press Ctrl-C.

I created a named range called MyList and then used that in the data validation.

this is the formula I used in the Named Range. I always use the header and offset from there. I don't know if your formula needed the last parameter of 1 to state the number of columns in the list.
=OFFSET('Data Validation'!$K$1,1,0,COUNTA('Data Validation'!$K:$K)-1,1)

 

[Godwin117]

Answer to my first question??

Please copy the Sheet name by double clicking on the sheet tab and press Ctrl-C.

I created a named range called MyList and then used that in the data validation.

this is the formula I used in the Named Range. I always use the header and offset from there. I don't know if your formula needed the last parameter of 1 to state the number of columns in the list.
=OFFSET('Data Validation'!$K$1,1,0,COUNTA('Data Validation'!$K:$K)-1,1)

I apologize for that yes, when I use the drop down it does that. There are blanks at the end of the data within the drop down, but it does work. Would you know how to get rid of the blanks?

 

[Jeffrey Mahoney]

Yes, I create dynamic ranges all the time for my data. Please try the method of creating a named range and using that formula I provided. Use the Named Range in the Data validation instead of your formula.
=MyList
or whatever you named your range.

 

[Godwin117]

Yes, I create dynamic ranges all the time for my data. Please try the method of creating a named range and using that formula I provided. Use the Named Range in the Data validation instead of your formula.
=MyList
or whatever you named your range.

That is correct, that's how I used it.

 

[Jeffrey Mahoney]

Sorry for being pedantic. The first formula you posted: that was entered directly into the Data Validation, correct?

The formula I provided was used as a named range: Formulas - Name Manager.

You don't have any cells in your list with spaces in them do you?

I'm freely brainstorming here because I don't have eyeballs on your workbook.

 

[Godwin117]

Sorry for being pedantic. The first formula you posted: that was entered directly into the Data Validation, correct?

The formula I provided was used as a named range: Formulas - Name Manager.

You don't have any cells in your list with spaces in them do you?

I'm freely brainstorming here because I don't have eyeballs on your workbook.

You're good, at first yes I did, then I used the name manager to see if that would change anything. The other cells have formulas in them that'll populate information based on specific criteria, otherwise they are blank.

 

[Jeffrey Mahoney]

That's it. Formulas will be counted in the CountA function

We need to use match and change the result of your formula so that it returns a zero instead of blank. If the last few formulas now result in a "", then change it so it results in 0.

This returns the the last text value in the column
=OFFSET('Data Validation'!$K$1,1,0,match("zzzzzzzzzzz",'Data Validation'!$K:$K)-1,1)