Solved

MS Excel Cell questions

Posted on 2016-10-11
5
75 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 33

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

839 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