Solved

MS Excel Cell questions

Posted on 2016-10-11
5
87 Views
Last Modified: 2016-10-12
Hello, my issue is that i have a sheet full of formulas that give different values and we need to make the cells with formulas have the values and not the formulas because the conditional formatting and the VBA Code we are using is not recognizing the cell value as the result, it is recognized as a formula and is formatting wrong.
0
Comment
Question by:Julian Solano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 41839350
Conditional formatting will not distinguish between a value that's a result of a formula or is hard-coded.

Can you attach a sample workbook?
0
 

Author Comment

by:Julian Solano
ID: 41840975
I have attached the file I need to do the formatting for the date of birth that is on the file.
Thanks for the help.
Auditing-Tool.xlsm
0
 
LVL 1

Accepted Solution

by:
Tim Berry earned 500 total points
ID: 41841095
OK I have your answer here.  Your formula in each field is returning a pure string.  Excel doesn't understand how to handle this because it is looking for a number value for dates.

Best way is to cast it from a string to a number that we can manage a bit better.  We can trick excel into doing this by putting your formula into ( )  and adding a math operator.   + 0  so that the date doesnt change.

EXAMPLE:
IN P3 you have:
=IF('Input Search Criteria'!$K$8='Input Search Criteria'!$J$8,'Do not touch 3'!P2,'Do not touch 2'!P2)& " "

I recommend you trick excel by changing the value of  P3 to:
=(IF('Input Search Criteria'!$K$8='Input Search Criteria'!$J$8,'Do not touch 3'!P2,'Do not touch 2'!P2)& " ")+0

Notice after the the = I added "(" and at the end I added ")" +0
This will cast your string back to a value excel can use..... THUS you can now use conditional formatting on the cell.

So you will need 3 formatting rules in the first cell that you just modified your formula in.
ORANGE
1.)   Format only cells that contain:   Cell Value   between     =TODAY()-100000   and  =TODAY()-29221
GREEN
2.)   Format only cells that contain:    Cell Value  between      =TODAY()-29221  and  = TODAY()-6576
RED
3.)   Format only cells that contain:    Cell Value   between     =TODAY()-6676    and   =TODAY()

Remember dates are NOT dates.. they are number values in excel.  So if you enter today 10/12/2016   Excel sees the value as 42655 instead.  

After you change your formula in the  P3 cell  AND after you add all 3 conditions to your formating,  just drag the handel in bottom right hand corner down through ALL your dates.  it should do what you want..

FYI they will all be GREEN

This looks like a lab assignment?  I will let you figure out how to correct the rest of it  (did you think about Leap Year?)
1
 
LVL 1

Expert Comment

by:Tim Berry
ID: 41841097
Trying to send back your sheet here...
Auditing-Tool.xlsm
0
 

Author Closing Comment

by:Julian Solano
ID: 41841108
AWESOME! Thanks you fixed the issue I was having!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question