help with combining if(isnumber) and concatenate

[jomama2]

Hello,
I’m a newbie so I hope I make sense. I need help with a formula for a key log. I’m not sure but think I need something that combines if(is number) with concatenate.
My Workbook currently has three worksheets:
Worksheet “KEY LOG”
The key box has 100 key slots. Each type of key is assigned a slot/number (1-100). On the first sheet (tab “Key Log”), each person has a column and a number is entered in the row for each key assigned to that individual.
• Column A is Total number of Keys
• Column B is Slot/Number key is assigned in Key Box
• Column C is Key Name/Label
• Column D is the total number of keys minus the number assigned in Column F – W
• Column F – W are names of the employees with the number of keys assigned in row 3 - 102.
I need a formula to find which key is assigned to each individual and the concatenate the key names/labels from column “C”.
Worksheet “Working File 1”
I have something that might work but it is incomplete and I’m having issues. The If(isnumber) formula is works but when I try to use the concatenate formula…it is very long and I only have a few rows… and the key names/labels need a comma and space between each. I am able to insert a space and comma but now I have spaces and commas for the blank rows.
Worksheet “Example of Expected Results”
This is an example of the existing Key Audit that currently has to be manually updated. This is set up with the name in one column but at this point I will be happy if I can get this to work with the name across the top at on the Working File 1 worksheet.
I’m sure there is a simpler way to do this.

[protonLeah]

A User Defined Function:

Please Login or Register  to view this content.

In cells: X103:AL103 =ChainKeys(Y3:Y102)
Copy to Results sheet (audit) with

Please Login or Register  to view this content.

Range Name Info

	D	E	F
3	range name	sheet	address
4	Employees	results	A3:A17
5	KeyHolders	key log	$X$1:$AL$1
6	Summary	key log	X103:AL103