Aug 20 2022 04:53 AM
Hi all!
I have a sheet which tracks a process with several stages.
This contains a username in the first column, and then several columns where dates are populated when each stage of the process is complete.
E.g
User: iurhgirhg Stage 1: 10/08/2022 Stage 2: 15/08/2022 Stage 3: blank as not completed
I am building a search function on another sheet to provide an overview of the current status.
So I want to lookup using that username, and then if there is a date in the relevant cell return "Yes" into my search function. I.e. to answer the question 'Is this stage complete for this user' yes or no.
If there is no data in the cell yet it means that stage isn't complete, so it would return "No"
Any help appreciated!
Aug 20 2022 05:11 AM - edited Aug 20 2022 05:12 AM
Perhaps the attached file will help you do something similar on your own file.
Aug 20 2022 06:39 AM
Aug 20 2022 06:44 AM
Aug 20 2022 07:11 AM
@hmwepp1989 Not sure I follow, but perhaps this:
=if(AND(VLOOKUP(G8,'raw data'!A1:P20500,16,FALSE),ISBLANK('raw data'!P:P)),"No","Yes")
Although I can't test it or really understand what you have in mind.
Perhaps you can share your workbook via OneDrive or something similar.
Aug 20 2022 02:20 PM
@Riny_van_Eekelen thanks for your reply.
I have simplified and added it here - hopefully it makes sense. It doesn't seem to be differentiating still between ones with values in that last column and ones without.
So it's the formula in G16 I'm trying to work out. Basically if all stages are completed then we will have results in, so that's how I need to determine the yes or no answer.
Aug 20 2022 09:06 PM
@hmwepp1989 Perhaps I'm missing the purpose of your schedule, but perhaps this formula is what you need.
=IF(ISNUMBER(VLOOKUP(G8,'Raw Data'!A1:G19,7,FALSE)),"Yes","No")
entered in the attached (revised) file. Though the hard-coded number 7 isn't very dynamic. If I were you, I would enter the relevant column index in a cell, as shown in the file I sent earlier.
Aug 21 2022 06:53 AM