I have a spreadsheet for recording staff holidays but some staff hold key
positions where cover is needed if that individual is off. The names are
entered by two admin staff as holidays are requested but they don't always
know for which staff cover is needed. To make this easier i would like to add
a function whereby the spreadsheet displays a message along the lines of
'cover is required for this position' when a particular name is entered. Each
cell in Row A has a drop down list of names from which to select.
Any help would be appreciated.

Thanks

Two ways

In column B insert an IF statement which looks up whether the person holds a
key position. If true, show the message.

Say you are using a list of names from your staff database, and that this
database is located in Sheet2, from A2:K50, showing the normal info such as
job title, name address and so forth. You will adapt to suit your needs.
Add a new column right at the end, say L. In this column, mark key positions
with the word Key, or any other word you prefer. I would name this range
StaffFull, or something like that.

Now, in B2, if the data validation is in A2 insert the following formula
=IF(A2=:"","",IF(ISERROR(VLOOKUP(A2,StaffFull,12,0 ),"","Cover is required
for this position". To make sure that this is noted, you can also use
conditional formatting to colour the background, should this cell contain any
text.

The other way, allow values not in the list, exclude your key personnel from
the list, and create an error message that gives this message. Also change
the Stop option to warning. I would use the first option though.

--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

I have a spreadsheet for recording staff holidays but some staff hold key
positions where cover is needed if that individual is off. The names are
entered by two admin staff as holidays are requested but they don't always
know for which staff cover is needed. To make this easier i would like to add
a function whereby the spreadsheet displays a message along the lines of
'cover is required for this position' when a particular name is entered. Each
cell in Row A has a drop down list of names from which to select.
Any help would be appreciated.

Thanks

Hi Kassie,

Thanks for the reply. I entered the formula as you said but it returns an
error message. Do i need to do something different with the parentheses?

Thanks
Craig

"Kassie" wrote:

Two ways

In column B insert an IF statement which looks up whether the person holds a
key position. If true, show the message.

Say you are using a list of names from your staff database, and that this
database is located in Sheet2, from A2:K50, showing the normal info such as
job title, name address and so forth. You will adapt to suit your needs.
Add a new column right at the end, say L. In this column, mark key positions
with the word Key, or any other word you prefer. I would name this range
StaffFull, or something like that.

Now, in B2, if the data validation is in A2 insert the following formula
=IF(A2=:"","",IF(ISERROR(VLOOKUP(A2,StaffFull,12,0 ),"","Cover is required
for this position". To make sure that this is noted, you can also use
conditional formatting to colour the background, should this cell contain any
text.

The other way, allow values not in the list, exclude your key personnel from
the list, and create an error message that gives this message. Also change
the Stop option to warning. I would use the first option though.

--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

I have a spreadsheet for recording staff holidays but some staff hold key
positions where cover is needed if that individual is off. The names are
entered by two admin staff as holidays are requested but they don't always
know for which staff cover is needed. To make this easier i would like to add
a function whereby the spreadsheet displays a message along the lines of
'cover is required for this position' when a particular name is entered. Each
cell in Row A has a drop down list of names from which to select.
Any help would be appreciated.

Thanks

Hi Craig,

Sorry for the long delay, but I was out of town.

The formula is correct, but you have to adapt to suit your specific needs,
Have you named the entire area that now holds your staff data, ie, from
employee nr, down to the last column, where you indicate whether the person
is a key employee, StaffFull?
How many columns are included in this range? Remember that the quoted
formula assuemes that the last column is Col L Count the number of columns,
and substitute 12 with that number.

Let me know!
--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

Hi Kassie,

Thanks for the reply. I entered the formula as you said but it returns an
error message. Do i need to do something different with the parentheses?

Thanks
Craig

"Kassie" wrote:

Two ways

In column B insert an IF statement which looks up whether the person holds a
key position. If true, show the message.

Say you are using a list of names from your staff database, and that this
database is located in Sheet2, from A2:K50, showing the normal info such as
job title, name address and so forth. You will adapt to suit your needs.
Add a new column right at the end, say L. In this column, mark key positions
with the word Key, or any other word you prefer. I would name this range
StaffFull, or something like that.

Now, in B2, if the data validation is in A2 insert the following formula
=IF(A2=:"","",IF(ISERROR(VLOOKUP(A2,StaffFull,12,0 ),"","Cover is required
for this position". To make sure that this is noted, you can also use
conditional formatting to colour the background, should this cell contain any
text.

The other way, allow values not in the list, exclude your key personnel from
the list, and create an error message that gives this message. Also change
the Stop option to warning. I would use the first option though.

--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

I have a spreadsheet for recording staff holidays but some staff hold key
positions where cover is needed if that individual is off. The names are
entered by two admin staff as holidays are requested but they don't always
know for which staff cover is needed. To make this easier i would like to add
a function whereby the spreadsheet displays a message along the lines of
'cover is required for this position' when a particular name is entered. Each
cell in Row A has a drop down list of names from which to select.
Any help would be appreciated.

Thanks

Got it now. Thanks for your help.

"Kassie" wrote:

Hi Craig,

Sorry for the long delay, but I was out of town.

The formula is correct, but you have to adapt to suit your specific needs,
Have you named the entire area that now holds your staff data, ie, from
employee nr, down to the last column, where you indicate whether the person
is a key employee, StaffFull?
How many columns are included in this range? Remember that the quoted
formula assuemes that the last column is Col L Count the number of columns,
and substitute 12 with that number.

Let me know!
--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

Hi Kassie,

Thanks for the reply. I entered the formula as you said but it returns an
error message. Do i need to do something different with the parentheses?

Thanks
Craig

"Kassie" wrote:

Two ways

In column B insert an IF statement which looks up whether the person holds a
key position. If true, show the message.

Say you are using a list of names from your staff database, and that this
database is located in Sheet2, from A2:K50, showing the normal info such as
job title, name address and so forth. You will adapt to suit your needs.
Add a new column right at the end, say L. In this column, mark key positions
with the word Key, or any other word you prefer. I would name this range
StaffFull, or something like that.

Now, in B2, if the data validation is in A2 insert the following formula
=IF(A2=:"","",IF(ISERROR(VLOOKUP(A2,StaffFull,12,0 ),"","Cover is required
for this position". To make sure that this is noted, you can also use
conditional formatting to colour the background, should this cell contain any
text.

The other way, allow values not in the list, exclude your key personnel from
the list, and create an error message that gives this message. Also change
the Stop option to warning. I would use the first option though.

--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

I have a spreadsheet for recording staff holidays but some staff hold key
positions where cover is needed if that individual is off. The names are
entered by two admin staff as holidays are requested but they don't always
know for which staff cover is needed. To make this easier i would like to add
a function whereby the spreadsheet displays a message along the lines of
'cover is required for this position' when a particular name is entered. Each
cell in Row A has a drop down list of names from which to select.
Any help would be appreciated.

Thanks

Great!
--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

Got it now. Thanks for your help.

"Kassie" wrote:

Hi Craig,

Sorry for the long delay, but I was out of town.

The formula is correct, but you have to adapt to suit your specific needs,
Have you named the entire area that now holds your staff data, ie, from
employee nr, down to the last column, where you indicate whether the person
is a key employee, StaffFull?
How many columns are included in this range? Remember that the quoted
formula assuemes that the last column is Col L Count the number of columns,
and substitute 12 with that number.

Let me know!
--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

Hi Kassie,

Thanks for the reply. I entered the formula as you said but it returns an
error message. Do i need to do something different with the parentheses?

Thanks
Craig

"Kassie" wrote:

Two ways

In column B insert an IF statement which looks up whether the person holds a
key position. If true, show the message.

Say you are using a list of names from your staff database, and that this
database is located in Sheet2, from A2:K50, showing the normal info such as
job title, name address and so forth. You will adapt to suit your needs.
Add a new column right at the end, say L. In this column, mark key positions
with the word Key, or any other word you prefer. I would name this range
StaffFull, or something like that.

Now, in B2, if the data validation is in A2 insert the following formula
=IF(A2=:"","",IF(ISERROR(VLOOKUP(A2,StaffFull,12,0 ),"","Cover is required
for this position". To make sure that this is noted, you can also use
conditional formatting to colour the background, should this cell contain any
text.

The other way, allow values not in the list, exclude your key personnel from
the list, and create an error message that gives this message. Also change
the Stop option to warning. I would use the first option though.

--
HTH

Kassie

Replace xxx with hotmail


"Craig" wrote:

I have a spreadsheet for recording staff holidays but some staff hold key
positions where cover is needed if that individual is off. The names are
entered by two admin staff as holidays are requested but they don't always
know for which staff cover is needed. To make this easier i would like to add
a function whereby the spreadsheet displays a message along the lines of
'cover is required for this position' when a particular name is entered. Each
cell in Row A has a drop down list of names from which to select.
Any help would be appreciated.

Thanks