Solved

MS Excel Cell questions

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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