Pull Data from another sheet if cell is blank

[MKE_APA]

Hi all -

Been pulling my hair out trying to figure out how to do this. I run a pool league and have a workbook where I document patch winners. Each sheet in that workbook documents each different patch (who won and, most importantly, when said patch was delivered). I have a sheet in the same workbook where I want to corral all of the patches that I have yet to deliver in order to view it all in one place. Once I deliver the patches, I note the date in the corresponding sheet and want that data to be removed from the "To Be Delivered" sheet.

For example, column A from the "Patches to Hand Out" sheet should list any of the names of people (column A from the "8 Break" sheet) who still have yet to receive their 8 on the Break patch, which is determined by an empty cell in column F of the "8 Break" sheet (which is the date delivered column). I want this formula to list ALL of the names, not just the first/last iteration.

Can this be done and could someone please lead me in the right direction? I have tried INDEX and LOOKUP/VLOOKUP formulas but it has not yet worked.

Zach

 

[MagnarH]

If I understand the problem, you can use FILTER, and INDIRECT for this.

In 'Patchers to Hand Out' you use the function below in cell A2. and autofill it as long as you need in row 2.

=LET(
player;INDIRECT("'"&A1&"'!"&"A2:A200");
Delivered;INDIRECT("'"&A1&"'!"&"F2:F200");
SORT(UNIQUE(FILTER(player;(player<>"")*(Delivered="");"All okey"))))

The headers in row one must be 100% equal to the wanted sheetnames.
Also change the the references in player and Delivered as needed.

 

[MKE_APA]

If I understand the problem, you can use FILTER, and INDIRECT for this.

In 'Patchers to Hand Out' you use the function below in cell A2. and autofill it as long as you need in row 2.

=LET(
player;INDIRECT("'"&A1&"'!"&"A2:A200");
Delivered;INDIRECT("'"&A1&"'!"&"F2:F200");
SORT(UNIQUE(FILTER(player;(player<>"")*(Delivered="");"All okey"))))

The headers in row one must be 100% equal to the wanted sheetnames.
Also change the the references in player and Delivered as needed.
View attachment 98112

This isn't working for me as presented. The formula prompts Excel to display the message "The first argument of LET must be a valid name. Assuming there should be some commas inserted there somewhere but I can't wrap my brain around exactly where they should go.

 

[Fluff]

Change all the semi-colons to commas.

 

[MKE_APA]

If I understand the problem, you can use FILTER, and INDIRECT for this.

In 'Patchers to Hand Out' you use the function below in cell A2. and autofill it as long as you need in row 2.

=LET(
player;INDIRECT("'"&A1&"'!"&"A2:A200");
Delivered;INDIRECT("'"&A1&"'!"&"F2:F200");
SORT(UNIQUE(FILTER(player;(player<>"")*(Delivered="");"All okey"))))

The headers in row one must be 100% equal to the wanted sheetnames.
Also change the the references in player and Delivered as needed.
View attachment 98112

Incredible. Thank you so much! This will help out a ton!

 

[MKE_APA]

Here's another wrinkle, everyone. Wanting to format cells if there are multiple cells returning the same name. For instance, if John Doe sinks 2 8 balls on the break, he gets two patches. I input each patch individually. The way the formula works as it stands, is that only one iteration of that player's name is returned, so there is no way to know that John Doe actually needs two patches instead of one. Is there a way to add formatting based on the number of iterations that name shows up?

 

[MagnarH]

Here's another wrinkle, everyone. Wanting to format cells if there are multiple cells returning the same name. For instance, if John Doe sinks 2 8 balls on the break, he gets two patches. I input each patch individually. The way the formula works as it stands, is that only one iteration of that player's name is returned, so there is no way to know that John Doe actually needs two patches instead of one. Is there a way to add formatting based on the number of iterations that name shows up?

Sorry, for forgetting about the ; vs. , in my first reply. Good it got sorted. (thanks @Fluff)

In your case it feels like it is easier to just add the number for times the players show up:

=LET(
player,INDIRECT("'"&A1&"'!"&"A2:A20"),
Delivered,INDIRECT("'"&A1&"'!"&"F2:F20"),
SORT(UNIQUE(FILTER(player & " - No:  " & COUNTIF(player, player),(player<>"")*(Delivered=""),"All okey"))))

Just change the text between the text questions to your liking.

My result:

 

[MKE_APA]

Sorry, for forgetting about the ; vs. , in my first reply. Good it got sorted. (thanks @Fluff)

In your case it feels like it is easier to just add the number for times the players show up:

=LET(
player,INDIRECT("'"&A1&"'!"&"A2:A20"),
Delivered,INDIRECT("'"&A1&"'!"&"F2:F20"),
SORT(UNIQUE(FILTER(player & " - No:  " & COUNTIF(player, player),(player<>"")*(Delivered=""),"All okey"))))

Just change the text between the text questions to your liking.
View attachment 98695

My result:
View attachment 98696

We're almost there, the only thing left is that it is counting all of the instances of the name from the sheet instead of how many instances of the name have not yet been delivered (Column F (delivered column)=blank cell next to their name). Thank you all so much for your help thus far!

 

[MKE_APA]

We're almost there, the only thing left is that it is counting all of the instances of the name from the sheet instead of how many instances of the name have not yet been delivered (Column F (delivered column)=blank cell next to their name). Thank you all so much for your help thus far!

As you can see, the one marked out in black is getting an overall count of 3 even though she only needs one patch delivered. This is because she has earned 3 of these patches, but has already received the other 2.

 

[MagnarH]

I was a bit haysty...

But I think I may have it this time. Just changed COUNTIF to COUNTIFS.

=LET(
player,INDIRECT("'"&A1&"'!"&"A2:A20"),
Delivered,INDIRECT("'"&A1&"'!"&"F2:F20"),
SORT(UNIQUE(FILTER(player & " - No:  " & COUNTIFS(player, player,Delivered,""),(player<>"")*(Delivered=""),"All okey"))))