Possible IF Statement

Sawdeeka01

New Member
Joined
Mar 31, 2020
Messages
26
Office Version
  1. 365
Hi, I am really hoping you could assist a novice please.

I have developed a spreadsheet to track exemptions, and auto populate a unique reference number for departmental use. What I need to do is come up with a formula to achieve this, as well as build in a couple of variances.

When completing the spreadsheet, the Exemption End Date (column I (locked cell)) will highlight when the date is within a 4 week period of the exemption ending, this is a sum of Date Exemption Received + Exemption Duration * 7 (F2+H2*7)

When the Committee Approval (column K) contains 'Yes' the Approval Reference Number (column L (locked cell)) will auto populate with the unique reference number using information from A2, C2, E2, I2, no matter what I do I cannot get this to run correctly and really need your expertise here please!

I also need to add variances into column L so that if a date is not available in column I, the unique reference number replaces the blank with 'Pregnancy'. Is this something that is achievable? I really hope I have explained that ok!

1633917626598.png
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,288
Office Version
  1. 365
Platform
  1. Windows
Try this is I2.
Excel Formula:
=IF(I2="","Pregnancy",IF(K2="Yes",LEFT(A2,6)&LEFT(C2,10)&LEFT(E2,10)&LEFT(I2,10)&"-"&COUNTIFS(A$2:A2,A2,C$2:C2,C2,E$2:E2,E2,I$2:I2,I2,K$2:K2,"Yes"),""))

You may find this format helpful
Uses Alt+Enter and spaces to format it
Excel Formula:
=IF(I2="","Pregnancy",
                 IF(K2="Yes",
                       LEFT(A2,6)&
                       LEFT(C2,10)&
                       LEFT(E2,10)&
                       LEFT(I2,10)&
                       "-"&COUNTIFS(
                                 A$2:A2,A2,
                                 C$2:C2,C2,
                                 E$2:E2,E2,
                                 I$2:I2,I2,
                                 K$2:K2,"Yes"),""))

In case anyone else wants a go here is a very basix XL2BB
20211011 If statement.xlsx
ABCDEFGHIJKL
1Employee IDFirst NameLast NameExempt ReasonTypeDate recvdDate ProcessedDurationEnd DateOutcomeApprovalRef No
21234BrownTemporary31/10/2021Yes1234BrownTemporary44500-1
31234BrownTemporary31/10/2021Yes1234BrownTemporary44500-2
Sheet1
Cell Formulas
RangeFormula
L2:L3L2=IF(I2="","Pregnancy",IF(K2="Yes",LEFT(A2,6)&LEFT(C2,10)&LEFT(E2,10)&LEFT(I2,10)&"-"&COUNTIFS(A$2:A2,A2,C$2:C2,C2,E$2:E2,E2,I$2:I2,I2,K$2:K2,"Yes"),""))
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,843
Office Version
  1. 365
Platform
  1. Windows
Left(E2,4) ?

...LEFT(I2,10)&"-"&COUNTIFS(A$2:A2,....
FWIW you can also replace that blue part with just -

I also wonder what the LEFT(I2,10) is there for in the original formula?
 

Sawdeeka01

New Member
Joined
Mar 31, 2020
Messages
26
Office Version
  1. 365
Try this is I2.
Excel Formula:
=IF(I2="","Pregnancy",IF(K2="Yes",LEFT(A2,6)&LEFT(C2,10)&LEFT(E2,10)&LEFT(I2,10)&"-"&COUNTIFS(A$2:A2,A2,C$2:C2,C2,E$2:E2,E2,I$2:I2,I2,K$2:K2,"Yes"),""))

You may find this format helpful
Uses Alt+Enter and spaces to format it
Excel Formula:
=IF(I2="","Pregnancy",
                 IF(K2="Yes",
                       LEFT(A2,6)&
                       LEFT(C2,10)&
                       LEFT(E2,10)&
                       LEFT(I2,10)&
                       "-"&COUNTIFS(
                                 A$2:A2,A2,
                                 C$2:C2,C2,
                                 E$2:E2,E2,
                                 I$2:I2,I2,
                                 K$2:K2,"Yes"),""))

In case anyone else wants a go here is a very basix XL2BB
20211011 If statement.xlsx
ABCDEFGHIJKL
1Employee IDFirst NameLast NameExempt ReasonTypeDate recvdDate ProcessedDurationEnd DateOutcomeApprovalRef No
21234BrownTemporary31/10/2021Yes1234BrownTemporary44500-1
31234BrownTemporary31/10/2021Yes1234BrownTemporary44500-2
Sheet1
Cell Formulas
RangeFormula
L2:L3L2=IF(I2="","Pregnancy",IF(K2="Yes",LEFT(A2,6)&LEFT(C2,10)&LEFT(E2,10)&LEFT(I2,10)&"-"&COUNTIFS(A$2:A2,A2,C$2:C2,C2,E$2:E2,E2,I$2:I2,I2,K$2:K2,"Yes"),""))

Thank you so much Alex, it worked perfectly, I really appreciate the time you have taken to help me with this! I feel I will never be an excel guru!

Rox :)
 

Sawdeeka01

New Member
Joined
Mar 31, 2020
Messages
26
Office Version
  1. 365

ADVERTISEMENT

Left(E2,4) ?

...LEFT(I2,10)&"-"&COUNTIFS(A$2:A2,....
FWIW you can also replace that blue part with just -

I also wonder what the LEFT(I2,10) is there for in the original formula?

Thank you Peter, the reference to I2 was part of the original formula and an oversight on my behalf, have a great day!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,843
Office Version
  1. 365
Platform
  1. Windows
the reference to I2 was part of the original formula and an oversight on my behalf
OK, no problem.
BTW, when marking "Accept as solution", you need to mark the actual post that has the solution. I have changed it to Alex's post for you this time. :)
 

Forum statistics

Threads
1,148,193
Messages
5,745,272
Members
423,941
Latest member
CluelessAboutExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top