Wildcard troubleshooting

[servcoor]

Greetings all.

I am having problems using a wildcard.

My workbook contains two sheets. Column A of rows 2 thru 16 of Sheet 1 contains a list of people's names. I need the cells in column A, rows 3 thru 17 of Sheet 2 to populate with names from Sheet 1 IF the name is found there. If not, the cells on Sheet 2 should remain blank.

My wildcard formula is as follows: =IF(Sheet1!$A$2:$A$30>"Gold*","GoldDavis, B","")

This formula works flawlessly, but only for the first 5 rows on Sheet 2. For some reason, the other rows are showing blank cells and this is what I cannot figure out.

I did discover that if I copied the formula above, EXACTLY to the remaining rows of Sheet 2, it actually does work and will populate those cells with the name GoldDavis, B. However, if I change the wildcard filter from "Gold" to "Martinez" - which name does appear in my list on Sheet 1, the cell containing the wildcard ends up blank again.

Any suggestions as to what I'm doing wrong would be greatly appreciated!

 

[Aladin Akyurek]

Are you trying to return GoldDavis, B for anything that start with Gold?

 

[T. Valko]

Greetings all.

I am having problems using a wildcard.

My workbook contains two sheets. Column A of rows 2 thru 16 of Sheet 1 contains a list of people's names. I need the cells in column A, rows 3 thru 17 of Sheet 2 to populate with names from Sheet 1 IF the name is found there. If not, the cells on Sheet 2 should remain blank.

My wildcard formula is as follows: =IF(Sheet1!$A$2:$A$30>"Gold*","GoldDavis, B","")

This formula works flawlessly, but only for the first 5 rows on Sheet 2. For some reason, the other rows are showing blank cells and this is what I cannot figure out.

I did discover that if I copied the formula above, EXACTLY to the remaining rows of Sheet 2, it actually does work and will populate those cells with the name GoldDavis, B. However, if I change the wildcard filter from "Gold" to "Martinez" - which name does appear in my list on Sheet 1, the cell containing the wildcard ends up blank again.

Any suggestions as to what I'm doing wrong would be greatly appreciated!

The IF function doesn't support the use of wildcards. If the results you're getting appear to be correct it's just "dumb luck".

It's not real clear what you're trying to do. You want to extract all names from sheet1 that start with the word "Gold" ?

Can you post a few lines of sample data and tell us what result you expect?

 

[servcoor]

Sorry if I wasn't perfectly clear. And I do appreciate all efforts to help me!

Ok, here are the first 5 rows of data in column A of Sheet 1. There are 10 more rows/names in column A, Sheet 1, for a total of 15 rows/names.

ISS BlackwellReed, J
ISS Bustillo, Grace
ISS Castilleja, Juli
ISS Garcia, Juanita
ISS GoldDavis, B

In column A of Sheet 2, I need to display any/all names that appear in the list in column A of Sheet 1 - on any given day, the list on Sheet 1 may not include some of the 15 names, so the appropriate cells on Sheet 2 need to show blank if a name is not found on Sheet 1.

The sample formula I supplied before =IF(Sheet1!$A$2:$A$30>"Gold*","GoldDavis, B","")
worked fine for the first 5 names in my list. Note that the formula only specified "Gold*" in the fifth iteration. The previous four were, respectively, "Blackwell*", "Bustillo*", "Castilleja*" and "Garcia*". All five iterations did correctly return the names from the list on Sheet 1. If this was dumb luck, as one member stated, then it was dumb luck five times in a row. I won't dispute that it shouldn't work, because I'm no expert, but it did work for five rows, so I thought I was on the right track but couldn't understand why it wouldn't work beyond the first five.

I hope this explains clearly enough what I need to do. My hope is that the solution doesn't require VBA, as I'm really not up to speed on that. Will a wildcard work?

Again, I appreciate all efforts to help!

 

[T. Valko]

Sorry if I wasn't perfectly clear. And I do appreciate all efforts to help me!

Ok, here are the first 5 rows of data in column A of Sheet 1. There are 10 more rows/names in column A, Sheet 1, for a total of 15 rows/names.

ISS BlackwellReed, J
ISS Bustillo, Grace
ISS Castilleja, Juli
ISS Garcia, Juanita
ISS GoldDavis, B

In column A of Sheet 2, I need to display any/all names that appear in the list in column A of Sheet 1 - on any given day, the list on Sheet 1 may not include some of the 15 names, so the appropriate cells on Sheet 2 need to show blank if a name is not found on Sheet 1.

The sample formula I supplied before =IF(Sheet1!$A$2:$A$30>"Gold*","GoldDavis, B","")
worked fine for the first 5 names in my list. Note that the formula only specified "Gold*" in the fifth iteration. The previous four were, respectively, "Blackwell*", "Bustillo*", "Castilleja*" and "Garcia*". All five iterations did correctly return the names from the list on Sheet 1. If this was dumb luck, as one member stated, then it was dumb luck five times in a row. I won't dispute that it shouldn't work, because I'm no expert, but it did work for five rows, so I thought I was on the right track but couldn't understand why it wouldn't work beyond the first five.

I hope this explains clearly enough what I need to do. My hope is that the solution doesn't require VBA, as I'm really not up to speed on that. Will a wildcard work?

Again, I appreciate all efforts to help!

OK, it sounds like you want Sheet2 column A to "mirror" Sheet1 column A.

Does that sound right?

Will the data in column A of Sheet1 be in a contiguous block (no empty cells within the range)?

 

[servcoor]

Thanks, T. Valko - yes, I think "mirror" might work. And, yes, contiguous...no blank cells in Column A of Sheet 1.

 

[T. Valko]

Thanks, T. Valko - yes, I think "mirror" might work. And, yes, contiguous...no blank cells in Column A of Sheet 1.

Ok, let's assume the range of interest is Sheet1 A2:A20. The number of entries will vary from day to day.

Enter this formula on Sheet2 A2:

=IF(ROWS(A$2:A2)>COUNTA(Sheet1!A$2:A$20),"",INDEX(Sheet1!A$2:A$20,ROWS(A$2:A2)))

Copy down to A20.

 

[servcoor]

Nice, nice, nice! Thanks, T. Valko!

Looks like I need to bone up on INDEX. Worked like a charm.

 

[T. Valko]

Nice, nice, nice! Thanks, T. Valko!

Looks like I need to bone up on INDEX. Worked like a charm.

You're welcome. Thanks for the feedback!