Jul 04 2022 06:16 AM
I have an interesting problem with excel sheet.
[Refer to attached file]
I am trying to input a validation/formula in 'Sheet 3 - Hiring Planning - column B' - such that if the Position Number (Sheet 3- A2:A9) shows up in either 'Sheet 1 - B2:B~' or 'Sheet 2 - B2:B~', the validation field appears a 'Closed', else it should appear 'Open'
I have tried IF formula, mixed with other - index, match, or even lookups, but unable to arrive at a solution.
Any Help will be much appreciated.
Jul 04 2022 06:47 AM
Here's a solution. I consolidated your two databases--no need for two sheets for Internal vs External; just differentiate by a column showing whether it's an internal or external. Makes the formula simpler.
=IFERROR(IF(MATCH(A2,'Offer Sheet'!$B$2:$B$20,0)>0,"Closed"),"Open")
Jul 04 2022 07:33 AM
thanks @mathetes
However, I cannot have the sheets for Internal & External separate as they are managed by 2 separate teams. Would it be possible to help me design a formula for 2 separate sheets?
Thanks.
Jul 04 2022 08:18 AM - edited Jul 04 2022 09:11 AM
Solution@dpenhadavid Perhaps this does what you need:
=IF(IFERROR(MATCH(A2,'Offer Sheet - External'!B:B,0),0)+IFERROR(MATCH(A2,'Offer Sheet - Internal'!B:B,0),0),"Closed","Open")
This formula tries to match A2 in both lists. It returns a number or #NA. IFERROR captures the #NA errors and sets the value to 0. If the sum of the two is anything other than 0, it means that the Position Number was found in either of the lists (External or Internal), and will return Closed. Otherwise Open.
Jul 11 2022 01:25 AM
Hi,
I really need help with this excel formula please. I am creating a Risk Matrix so basically in a dropdown you pick the Likelihood and in another dropdown you pick the Consequence and the result should be the Low, Medium or high in the same colours.
Can you please help me with this?
Jul 04 2022 08:18 AM - edited Jul 04 2022 09:11 AM
Solution@dpenhadavid Perhaps this does what you need:
=IF(IFERROR(MATCH(A2,'Offer Sheet - External'!B:B,0),0)+IFERROR(MATCH(A2,'Offer Sheet - Internal'!B:B,0),0),"Closed","Open")
This formula tries to match A2 in both lists. It returns a number or #NA. IFERROR captures the #NA errors and sets the value to 0. If the sum of the two is anything other than 0, it means that the Position Number was found in either of the lists (External or Internal), and will return Closed. Otherwise Open.