List if?????

[mike ryan]

Hi I was looking for a bit of help if at all possible.

I have on one worksheet 5 individual names (cell ref, c26,c36,c46,c56,c66) which only appear if certain criteria are met on a previous worksheet, and will always appear specifically in those cells.

What I need is some kind of formula that will allow me on a 3rd spreadsheet to list (in cells a1:a5) which of the 5 names are present from the previous sheet. Sounds simple eniugh right?

However, I need the formula to be dynamic so that if only 3 names are present it will only populate cells a1:a3, and if the only name present is the name in c66, this would appear in a1 etc..

Anybody got any ideas????

[Cutter]

That would be easily accomplished with a helper column (which could be hidden) in the original worksheet.

A formula in that helper column would trigger a number if a name appeared in that row's C col.
Each subsequent formula would trigger an incremented number if a name appeared.

You would use those numbers for a VLOOKUP() or INDEX()/MATCH() on your third worksheet. The first line would look for the 1, second line looks for the 2, etc.

[mike ryan]

Hi Cutter, Thanks for coming back to me. I've followed your advice and input numbers 1-5 in a hidden helper column (columnd D) on my original worksheet.

On the subsequent worksheet I have input =IF(VLOOKUP(1,'Account Calculations - FILL IN'!$D$1:$E$70,1,FALSE),'Account Calculations - FILL IN'!C26)

C26 being the cell reference for the 1st name. How would I revise this to search for the other 4???

[Cutter]

The formulas in the helper column are IF() functions.

The first would be IF(C26="","",1)
The next would be IF(C36="","",COUNT(D$26:D26)+1)
You copy this one to row D46, D56 and D66

On your 3rd worksheet you make your VLOOKUP() within an IF() to check for the COUNT().

So your first line (if it's on row 1):
=IF(COUNT(Sheet1!D$26:D$66)<ROW(),"",your VLOOKUP)

and drag down.

EDIT - I was using C column referencing based on your original post but it appears that the names are in E column. Change references accordingly.

If you have trouble with implementing this upload a small sample file (without private/personal data) and I'll do it for you.

[Marcol]

Don't know if this will help.

If there is nothing in Sheet1 Column C other than the required names.

Try this Array Formula in Sheet2 A1

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter not just Enter

This will remove all blanks from the list in Sheet1 Column C and it is not restricted to fixed cells.

If there is other data in Sheet1 Column C then add a helper column that only refers to the required cells and apply the formula to the helper column.

Hope this helps.

[mike ryan]

Hi Fellas, thank you so much for your responses. After a bit of playing around I managed to get the VLOOKUP function working correctly using helper cells, so can mark this one as closed now.

Thanks again!

[Cutter]

You're welcome.

Click on the FAQ link at top of page for instructions to mark your thread as SOLVED.

[LtMatt]

Hi, I'm doing something similar to Mike Ryan, but can't quite work out the formula. I'm hoping someone on here will have some advice.

I play a battle game with a friend and have tried to set up a spreadsheet to track who is in each of our small teams.

On sheet 1 I have all my troop details... on sheet 2 is my friend's... They are pretty much identical so just focussing on Sheet 1 data for now.

On each sheet there is a number of sections containing various information about each troop member, such as his unique name, point cost, stats, etc... There is also a cell with a value manually set at a 1 (if the member is taking part in the next battle) or a 0 (misses the next battle).

I would like to create a sheet 3, which lists all the unique names of the members taking part in the next battle (with a 1 in the relevant cell), along with their point cost.

This is my progress so far:

As mentioned in this thread, I have created a helper column on Sheet 1 (column A) which is used to count the number of members taking part.

For this example I will just focus on my first 3 warriors.

In the area with their information, the cells stating whether the hero is in the next battle or not are as follows: I24, I43 and I62. As mentioned these are manually set to a 1 if the hero is in the next game, or a 0 if they are missing.

So in the helper column I have the following formula:

First warrior (A7) =IF(I24=1,COUNT($A$1:A6)+1,"")

Second warrior (A26) =IF(I43=1,COUNT($A$1:A25)+1,"")

Third warrior (A45) =IF(I62=1,COUNT($A$1:A44)+1,"")

This causes a chronological number to appear if the warrior is taking part, or leaves a blank cell if they are absent.

I would like column A on Sheet 3 to look down the helper column and be populated in the same manner as Mike suggested, if there are 3 members populate A1:A3, 5 members = A1:A5, etc...

I'd like this to populate with the members unique name which is located in the following cells on the first sheet: D8, D27 and D46.

In column B on Sheet 3, I would also like their point cost to be displayed. The point costs are in the following cells on the first sheet: E24, E43, and E62.

Sorry for the essay, and I hope it makes sense.

I’ll appreciate any help or advice, I’m still a bit of a noob when it comes to excel but trying to learn.

Thanks in advance!

[protonLeah]

LtMatt,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.