Possible IF Statement

Sawdeeka01

New Member
Joined
Mar 31, 2020
Messages
37
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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"),""))
 
Upvote 0
Solution
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?
 
Upvote 0
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 :)
 
Upvote 0
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!
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top