Data Validation Source for dropdown cell based on variable range?

[EssKayKay]

Hello,

I’m having a problem assigning a source range to a dropdown cell where the first cell in the range is constant; however, the last cell varies. Example P33:P???.
I’m looking for a way to set the last cell range based on another cell value. I have a cell (S17) that holds the value of the last cell in the range (named LastPmtRow).

What I was looking for was a way to code the Data Validation Source that would allow the last cell to be modified. Something like one of the following unsuccessful attempts:

= ("P33:P" & "LastDataRow")
= ("P33:P" & LastDataRow)
= ("P33:P" & S17)
= ("$P$33:$P$" & S17)

Any suggestions would be appreciated.

Thanks for viewing,
Steve K.

EDIT - As a workaround I thought I could just set the source as a range that is bigger than I would ever need. Something like = P33:P2222. However, when I click on the dropdown arrow it displays the last cell in the range which is blank. If there was a way to display range in reverse order would be great. I tried setting the source to P2222:P33 but that did not work. gain, any other ideas would be appreciated.

 

[EssKayKay]

Hello,

I’m having a problem assigning a source range to a dropdown cell where the first cell in the range is constant; however, the last cell varies. Example P33:P???.
I’m looking for a way to set the last cell range based on another cell value. I have a cell (S17) that holds the value of the last cell in the range (named LastPmtRow).

What I was looking for was a way to code the Data Validation Source that would allow the last cell to be modified. Something like one of the following unsuccessful attempts:

= ("P33:P" & "LastDataRow")
= ("P33:P" & LastDataRow)
= ("P33:P" & S17)
= ("$P$33:$P$" & S17)

Any suggestions would be appreciated.

Thanks for viewing,
Steve K.

EDIT - As a workaround I thought I could just set the source as a range that is bigger than I would ever need. Something like = P33:P2222. However, when I click on the dropdown arrow it displays the last cell in the range which is blank. If there was a way to display range in reverse order would be great. I tried setting the source to P2222:P33 but that did not work. gain, any other ideas would be appreciated.

PS - EDIT
I forgot to add - I tried setting the validation to "Ignore Blank" but that did not work.

 

[EssKayKay]

The more I think about my workaround (reverse order) I suspect that is not what I want as then all the dates would be in reverse order. I want the dates in consecutive order with the blank cells at the end.

Sorry for all these follow ups but I could not edit my post(s).