?
Solved

MS Excel Cell questions

Posted on 2016-10-11
5
Medium Priority
?
107 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
  • 2
  • 2
5 Comments
 
LVL 35

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 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