If function across multiple columns with empty cells

B

Bob_ipc

Board Regular
Joined
Oct 18, 2017
Messages
67
Office Version
  1. 365
Platform
  1. Windows
I could really use some help on this one. I currently have a formula that goes like this. =IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7<TODAY()+90,"Expires Soon",""),IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7,"","") the idea is to have "Expires Soon" notification pop up when the expire date is within 90 days. Some columns contain empty or blank cells. how can I make this work? Please Help.

Thank you.
 
Last edited by a moderator:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi Bob,

What are you trying to achieve and do you have some sample data we can see to help you?
 
Upvote 0
Bob_ipc said:
I currently have a formula that goes like this. =IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7<TODAY()+90,"Expires Soon",""),IF(H7:J7:M7:O7:Q7:S7:U7:W7:Y7:AA7:AC7,"","")
Click to expand...
Ignoring the blank cell issue for a moment, that isn't a valid Excel formula. Do you have one this is working if there are no blank cells?
 
Last edited:
Upvote 0
Hi Peter_SSs, thank you for the reply. I can use =if(h7< today()+90,"Expires Soon","") but how do I get it to tell me when 90 days is close in any of the other cells? and some cells are blank.< today()+90,"expires soon","")="" and="" it="" works,="" but="" how="" do="" i="" get="" to="" tell="" me="" if="" any="" of="" the="" cells="" listed="" come="" close="" 90="" days?="" also="" some="" cell="" contain="" blanks="" as="" well.

Thank you.
 
Last edited by a moderator:
Upvote 0
The forum software thinks that a < sign followed immediately by a letter is some HTML code that it should act on. Add a space between to stop your formula or text being cut off, as I have done in your last post.

Do the columns that you are interested in have a heading that is different to all the other headings? Or contain at least something that we could identify the relevant columns by?

You seemed to be indicating columns H, J, M, O, Q, .. which, except for the jump from J to M, the rest are every second column. Can you clarify? Or better still post a small set of sample data and expected results that we can copy to test with? My signature block below has a link with help on that.
 
Upvote 0
So sorry it took so long for a response I had something come up, first thank you again for your response, below is an example of what I have, there are some training courses, each of them have and expire date. If the Name column is column "B" then I would like column "A" to display "Expires Soon" any time that any of the expire dates come close in that row for any of the employees.

I hope that helps.


[TABLE="width: 959"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Orientation
[/TD]
[TD="colspan: 2"]Annual IPC Values Orientation
[/TD]
[TD="colspan: 2"]First Aid
[/TD]
[TD]WHIMIS
[/TD]
[TD="colspan: 2"]TDG
[/TD]
[TD="colspan: 2"]Cargo Securement
[/TD]
[/TR]
[TR]
[TD]First Last
[/TD]
[TD]Employment Orientation
[/TD]
[TD]Issue Date
[/TD]
[TD]Expire Date
[/TD]
[TD]Date Issued
[/TD]
[TD]Expire Date
[/TD]
[TD]Date Issued
[/TD]
[TD]Date Issued
[/TD]
[TD]Expire Date
[/TD]
[TD]Date Issued
[/TD]
[TD]Expire Date
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]24-Jul-17
[/TD]
[TD]05-Jun-18
[/TD]
[TD]05-Jun-19
[/TD]
[TD]14-Dec-16
[/TD]
[TD]14-Dec-19
[/TD]
[TD]18-May-18
[/TD]
[TD]22-Oct-18
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Abe
[/TD]
[TD]
[/TD]
[TD]05-Jun-18
[/TD]
[TD]05-Jun-19
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Abe
[/TD]
[TD]
[/TD]
[TD]05-Jun-18
[/TD]
[TD]05-Jun-19
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]18-May-18
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank You.
 
Upvote 0
See if you can adapt something like this
A1 holds the current date by formula.
A3 is copied down

Excel Workbook
ABCDEFGHIJKLMNO
119/10/2018NameOrientationAnnual IPC Values OrientationFirst AidWHIMISTDGCargo Securement
2First LastEmployment OrientationIssue DateExpire DateDate IssuedExpire DateDate IssuedDate IssuedExpire DateDate IssuedExpire Date
3Expires SoonBob24-Jul-175-Jun-185-Jun-1914-Dec-1614-Dec-1918-May-1822-Oct-1815-Aug-1822-Nov-18
4Abe5-Jun-185-Jun-19
5Expires SoonAbe5-Jun-185-Jun-195/01/201918-May-18
Expiring Soon



Another option you might want to consider is to highlight the dates in question. This could be done instead of or as well as the previous suggestion.
To apply this select from the top left data cell (I used G3) down to the bottom right and apply the Condition Formatting formula rule as shown. The column/row references in the formula should relate to the active cell (G3 for me) or the heading row of that first cell column (eg where I used G$2).

Excel Workbook
ABCDEFGHIJKLMNO
119/10/2018NameOrientationAnnual IPC Values OrientationFirst AidWHIMISTDGCargo Securement
2First LastEmployment OrientationIssue DateExpire DateDate IssuedExpire DateDate IssuedDate IssuedExpire DateDate IssuedExpire Date
3Expires SoonBob24-Jul-175-Jun-185-Jun-1914-Dec-1614-Dec-1918-May-1822-Oct-1815-Aug-1822-Nov-18
4Abe5-Jun-185-Jun-19
5Expires SoonAbe5-Jun-185-Jun-195/01/201918-May-18
Expiring Soon (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G31. / Formula is =AND(G$2="Expire Date",G3>=$A$1,G3<=$A$1+90)Abc
 
Upvote 0
Sorry it took me such a long while to get back to you again, But this is awesome it works 100%!!!! Thanks so much!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Average Function
Replies
8
Views
381
Peter_SSs
Peter_SSs
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
F
  • Solved
Show result from other columns (without knowing which of the 4 columns has value in it)
Replies
4
Views
221
Fluff
Fluff
J
  • Question Question
using sumifs function across multiple sheets
Replies
4
Views
560
KRice
K

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

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