Solved

MS Excel Cell questions

Posted on 2016-10-11
5
79 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 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

730 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