VBA - Display Cell Value in a Range Based on Another Range

[foolishpiano]

Hello Mr. Excel! You guys are always extremely helpful, so I have another question I would greatly appreciate your help with:

In column P I have a list of Unit #'s that need to be updated in an online system. In column BA I mark off units that have been updated (I type the word "Yes"). For example, Unit # 101 is in P15, and once the unit information is updated I will enter "Yes" into BA15.

I would like to create a macro so that when it's run, the next Unit # that has not been updated is displayed in another cell on another worksheet (I then use VLOOKUP to get information about that unit). The Unit #'s are not always just numbers, and they aren't always sequential. For example, I could have a project with Unit # 5A and the next unit is 9B, which is why I'd like the macro to just run down the range in column P. Continuing the first example, once Unit # 101 is updated and I've entered in "Yes" to BA15, when I run the macro I'd like the value in cell P16 to be displayed. When the end of the range in column P is reached, rather than trying to display a Unit #, a message box pops up and says something like, "All Units are Updated!"

I apologize if I have not explained what I'm trying to accomplish in an understandable way; I am happy to clarify, after all, you all are the one's trying to help me!

Thank you all in advance!

 

[mumps]

A few questions for clarification:
When you say:

the next Unit # that has not been updated is displayed in another cell on another worksheet

do you mean the next unit in column P that does not have a "Yes" in column BA? Also, to which cell and which sheet do you want that Unit# displayed?

 

[WaterGypsy]

I think you can do this without a macro if you can add another column (say BB).

In BB2 (assuming your data starts on row 2) enter this formula and copy down to the end: =IF(AND(BA2="",BA1="Yes"),IF(P16="","Complete","Next"),"")

This should identify where the next row is (or if it is complete)

Then in the cell where you want to show the part number put: =IF(COUNTIF(BB:BB,"Complete"),"All units updated",INDEX(P:BB,MATCH("Next",BB:BB,0),1))

Then you can use that cell to drive your VLOOKUP

 

[foolishpiano]

A few questions for clarification:
When you say: do you mean the next unit in column P that does not have a "Yes" in column BA? Also, to which cell and which sheet do you want that Unit# displayed?

Hi Mumps! Yes; I do mean the next unit in column P that does not have a "Yes" in column BA. In answer to your second question, I would like the Unit # displayed in cell A2 in a sheet called "Cheatsheet to Upload Data"

Thank you for your help!

 

[foolishpiano]

I think you can do this without a macro if you can add another column (say BB).

In BB2 (assuming your data starts on row 2) enter this formula and copy down to the end: =IF(AND(BA2="",BA1="Yes"),IF(P16="","Complete","Next"),"")

This should identify where the next row is (or if it is complete)

Then in the cell where you want to show the part number put: =IF(COUNTIF(BB:BB,"Complete"),"All units updated",INDEX(P:BB,MATCH("Next",BB:BB,0),1))

Then you can use that cell to drive your VLOOKUP

Hi WaterGypsy! Your formulas worked like a charm; thank you very much! For future reference and my own personal knowledge, I still would like to know how to do this in VBA (I'm slowly learning VBA), just in case there is a time where I cannot add a helper column. Thank you again though! This has got me going in the right direction!