IF Error or Conditional Formatting

freckles527

New Member
Joined
Apr 3, 2009
Messages
6
1st thank you for any help.

I have written formulas and used conditional formatting to hide error values. The specific one I am trying to hide is #Value!

I have a drop-down list in Column D that calculates a year result in Column E. This is based on using Defined Names.

Cell E formula is =IF(D6="","",INDEX(RP,MATCH(D6,RCC,0))). When my dropdown list in D is populated with a code, then E poulates with a year.

The user will put in an effective date in Column F. Column G calculates the Expiration Date from what populated in Column E.

Column G formula is: =DATE(YEAR(F6)+E6,MONTH(F7),DAY(F6)) this formula works and calculates the correct expiration date.

My issue is I have to copy all of these formulas down so the user doesn't have to remember to copy them or calculate the future date. I get a #Value! error.

I have tried using some ISERROR formulas, conditional formatting using ;;; and nothing is working.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
Have you tried
=IFERROR()
=IFERROR(DATE(YEAR(F6)+E6,MONTH(F7),DAY(F6)),"")
 

freckles527

New Member
Joined
Apr 3, 2009
Messages
6
Hi etaf. I did try that and it isn't working. I will try it again in a new spreadsheet. Thank you for your reply.
 

freckles527

New Member
Joined
Apr 3, 2009
Messages
6
Got it finally - ugh. I thought I had tried this, but now it is working and leaves the cell blank if nothing is populated in F6.

=IF(F6<>"",(DATE(YEAR(F6)+E6,MONTH(F7),DAY(F6)),"")
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

=IF(D6="","",IFERROR(INDEX(RP,MATCH(D6,RCC,0)),""))
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
ok, glad its working OK now
 

freckles527

New Member
Joined
Apr 3, 2009
Messages
6
Thank you, everyone. I will copy the suggestions you provided. You never know when those are needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

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