Formula to find first non-blank value above in adjacent column

Hello and thank you for your time.

I have a named ranges called GroupPhase - the data looks like this - Div_00, Div_01, Div_03...,

Each of the Group Phases is a named Range for example Div_00 returns 00-10-00, 00-30-00, 00-40-00, ...

In the data validation (list) I am using =INDIRECT() and selecting the value in B Column to return the appropriate Named Range

I am trying to determine if there is a formula I can put into the Data Validation (List) for the values in column C that will return the previous value in column B in relation to the location of the data in column C.

Example of desired result
I assign Div_00 to Cell B10 - In C11:C11+n I can then select values from the Named Range Div_00 in C11+n

By row 20 I am finished with my Div_00 entries, in Cell B21, I assign the value as Div_01. I would like C22+n to be able to pull from the named range in cell B21

Maybe 30 or 40 rows later I am finished with Div_01. There is no Div_02 in this specific project so I can skip to Div_03 which I assign to the next cell (B64, for example) after Div_01. From B65+ I want to see the Div_03 as my Data Validation list.

Thanks much,
Mark

Added the attachment

p.s. how do I change my excel version? When it posted, it showed 2007 - I am actually on Office 365

mh

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

p.s. how do I change my excel version? When it posted, it showed 2007 - I am actually on Office 365

If you are not familiar with how to update your profile find the dark blue menu at the top of this page that says

Forum Actions

. Click that and 'Edit Profile'.

Try to create a name refer to Phase code of each Div in the left column.
Name: "Ph"
Stay in C10, Ctrl-F3 to create:

Please Login or Register  to view this content.

Copy/ paste value/Validation down.