drop down list..if cells contain data

[jw01]

Hello

I have a drop down list of employees. the file is a template and thus i have dummy names in there for a total of 20 names.

now, my data validation list contains all 20 names, but more than likely the branch ppl will use a max of 5 ppl to add to this list. so instead of having all the blank spaces in the drop down list, is it possible for it to show only the active names?

can anyone help, thx you very much!

[NBVC]

No attachment??

[jw01]

Hello there,

I have attached a sample. the "CSA" worksheet has the drop down selection...i have highlighted the cells in red.

so if the drop down list can be dynamic enough to only show the names that are showing?...is that possible? thx you again!!

[NBVC]

Redefine the Name named range as:

=OFFSET('ISE Performance'!$C$10,1,0,COUNTA('ISE Performance'!$C$11:$C$30),1)

[jw01]

thx you oh so much!...have a great day!

[scottylad2]

open the name manager and type in C11:INDEX(Name,COUNTA(Name))

and call it something, i used dynamicnamelist then in your cells requiring the DV list, insert the dv list and in the refers to hit F3 and paste the dynamicnamedlist

you should now have only populated cells in your dv list

[jw01]

Hello

thx you for your prompt response and help, it works like a charm.

i have another question.

i need to be able to sum or count rather the # of CSA (contracts) a sales person has signed.

here is a formula i had in place, but not sure if im executing it correctly.

=vlookup($C11,CSA!$D$14:$E$263,2,(countif(CSA!E14:E263,"="&'ISE Performance'!AM10)),false)

basically, on the "ISE Perf" worksheet, i need to vlookup the sales person name, and count the # of contracts they have signed in a time period i.e. P1, P2 etc....thxs for your help!

[NBVC]

If you have 2007 as your profile indicates, then perhaps:


=COUNTIFS(CSA!$D$14:$D$263,C11,CSA!$E$14:$E$263,AM10)

if not,

=SUMPRODUCT(--(CSA!$D$14:$D$263=C11),--(CSA!$E$14:$E$263=AM10))

[jw01]

thx you very much

[jw01]

Hello

thxs for your help earlier. im having some issues with sumif

i have attached a sample workbook. basically,

the quota/budget is input early in the year for all 12 months.

how can i make the column highlighted in "red" to sum only period we are in and past?
i.e.
we are in P2 (month 2)
Period1 = $2000
Period 2 = $2000
Period 3 = $2000
Period 4 = $2000

there is a cell highlighted in yellow "Period 2"...so the YTD sum should be or column AJ should read $4000, instead of $8000.

ur help is much appreciated. thx you!

[scottylad2]

in AJ11 and drag down =SUM(X11:INDEX($X11:$AI11,1,MATCH($D$6,$X$8:$AI$8,0)))

but i'm not totally sure if this is the ytd you're totaling,

[jw01]

omg thx you so much!....

that is exactly what i was looking for. to sum only the period we are in and what has passed.!!!! thx you so so much!