• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell Value Based on Another Cells Calculation

D

Diane

New Member
I have a workbook (tracker) with a “Main Sheet” where all of the user data is entered. I want some of the information to roll up into my “Program Status” worksheet.


Cell D16 has a simple formula to pull a date from the “Main Sheet”

D16=’Mainsheet’!K2


In column A I have 2 years of monthly dates created by a formula

A20=’Mainsheet’!G2+30

A21=’Mainsheet’!G2+60

A22=’Mainsheet’!G2+90 etc. spanning 2 years


I want all of the cells in column A to validate whether they match the “LQR” date that is in D16 or the “SOP” date in cell F16. I thought it would be simple like this…


=IF(A20=D16,"LQR",IF(A20=F16,"SOP","N/A"))


I think the problem is that the cells don’t match because they are both formulas. I really want the result of A20, A21…etc.
 
Well, I guess its in the subject line really. Sorry about that.


How can I get all of the cells in column A to validate whether they match the “LQR” date that is in D16 or the “SOP” date in cell F16?
 
Fred thanks for you quick reply. I have to leave work in a few minutes but I will check back here tomorrow.
 
Your original formula should be correct...XL doesn't care whether the value is hard-entered, or from a calculation. Also, you didn't state in post what is is in F16 exactly. Now, a few trouble shooting things:

Is your formula being copied? Is it copying correctly (relative vs. absolute references)? Solution:

=IF(A20=$D$16,"LQR",IF(A20=$F$16,"SOP","N/A"))


Are the dates coming in as nice round numbers, or are there some hidden decimals? Change the formatting to check. Solution: Use a ROUND or INT function to trim the number.


Still no luck? Could you post a short example, eg., what you have and what you'd like to see (and what you're actually seeing)?
 
To the great wisdom of excel community: did I misinterpret Diane's question? How would you take on her question?
 
Dianne


According to above you have Dates in A20 which comes from Mainsheet G2 + 30 days


You then say you have an “LQR” date in D16 and a “SOP” date in F16


Now it depends what is in D16 and F16

Above you say =’Mainsheet’!K2

so If K2 is a Date, cool, if not ?


Your formula:

=IF(A20=D16,"LQR",IF(A20=F16,"SOP","N/A"))

is comparing a Date in A2 to D16 and F16

So it depends what values are in A20, D16 and F16


Can you post your file for us to review

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Shoot, it's tough being a newbie. :) Let me try to clarify.


HUI...in response to your question above....yes, the value in mainsheet K2 is a date.

My objective is to get all column A cells to validate if their value matches the value in D16 or F16.

I'm gonna try to post my workbook, have never done that before. I'll be back.
 
I tried using google docs for sharing my file but I keep getting an error message.


I still don't have a formula for my initial question. I'm hoping someone comes thru for me.
 
if K2 from mainsheet is a date then D16 is a date (a specific number).

So is F16 a date as well, are you sure?

If they are dates that mean they are numbers.


In your column A it should be a bunch of dates, as you put it, spanning over 2 years. So they are also numbers.


Your question, if I am not mistaken, is to find out if there is any dates on column A that would match that of D16 or F16. Do you mean the whole column A or just an area of column A (some cells not all cells), because your example starts from A20? There is a huge difference if your matching cells starts from A20 and not A1.
 
i have reformated my formula to


= IF ( ISNUMBER ( MATCH( $D$16, $A:$A,0) ) , "LQR",IF( ISNUMBER( MATCH($F$16, $A:$A, 0)), "SOP", "N/A") )
 
I tried this but it doesn't work. Fred, you are correct in my question. I want to find out if there are any dates in columm A 18-42 that match D16 or F16.
 
so the new formula looks like this....

=IF(ISNUMBER(MATCH($D$16,$A$18:$A$42,0)),"LQR",IF(ISNUMBER(MATCH($F$16,$A$18:$A$42,0)),"SOP","N/A"))

I did that but it's not working. It still comes up saying "N/A" in cell A27 where I KNOW it's a match to D16
 
are you sure the date doesn't have "Time" imbedded?


a date with time is different from a date with no time.
 
PPAP: Sep-11 LQR: Jun-12 SOP: Aug-12

Date Milestone Height Dummy Milestone

Sep-11 PPAP 1 0 PPAP

Oct-11 #N/A #N/A 0 #N/A

Nov-11 N/A #N/A 0 N/A

Dec-11 #N/A #N/A 0 #N/A

Jan-12 #N/A #N/A 0 #N/A

Feb-12 #N/A #N/A 0 #N/A

Mar-12 #N/A #N/A 0 #N/A

Apr-12 N/A #N/A 0 N/A

May-12 #N/A #N/A 0 #N/A

Jun-12 N/A #N/A 0

Jul-12 #N/A #N/A 0 #N/A

Aug-12 #N/A #N/A 0 #N/A

Sep-12 #N/A #N/A 0 #N/A
 
Just to double check (in case there's something screwy)

Type a formula of:

=D16=A27


Based on your response above to Hui's question, the simple formula should work. I've got a hunch though that there's some extra spaces or decimals or something messing things up.
 
Diane


your dates are only showing month and year. that's not a complete date. Try expanding your cell to include DAYS as well.
 
Luke - I applied your formula and it comes back as "FALSE". See the bold text in my prior post. Those are columns D16 and A27
 
Fred I think you have it! now that I have expanded the date to include days Cell D16 is June 15, 2012 and cell A27 is June 7 2012.


That being said....I don't want to match the DAY of the event, just the MONTH of the event. Any suggestions?
 
Ok, so, now we know that Fred/Hui's formula is not the problem, it's the data.

Change both cells formatting to Number with 16 decimals showing. Let's see where things are going wrong.
 
=IF(TEXT($A20,"mmyyyy")=TEXT($D$16,"mmyyyy"),"LQR",IF(TEXT($A20,"mmyyyy")=TEXT($F$16,"mmyyyy"),"SOP","N/A"))
 
You must log in or register to reply here.
Back
Top