# Possible IF Statement

#### Sawdeeka01

##### New Member
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!

### 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
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"),""))

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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

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
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.

#### Alex Blakenburg

##### Well-known Member
And thanks for the feedback from me too.

Replies
3
Views
177
Replies
2
Views
266
Replies
4
Views
361
Replies
3
Views
266
Replies
0
Views
124

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

### 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.

### Which adblocker are you using?

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

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