Make my formula equal the value of a cell on another sheet.

Question

1.00/5 (1 vote)

See more:

Okay so I feel like this is going to be complicated to explain.
I have this formula:

VB

=IF(LEN(C12)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,MoMonthNum,C12))>0,"Assignment due!",""))

So with this formula, the first value in my calendar will say that an assignemtn is due, if an assignment on my Assignment sheet has a Due date that matches to the calendar ie. if an assigment is due on October 4th, 2015 the phrase 'Assignment Due!' will be shown on that date.

My problem is, I want that phrase to say the exact assigment corresponding to the date. For instance, if I have 'Work' in my assigment sheet corresponding to October 4th, 2015 I would like the value to say 'Work' instead of 'Assigment Due'. Now I know that I can just go into the formula and change where it says Assignment due>work, but then I would have to do that on every formula on the calendar.

Is there another formula I could put after the DATE(MoYear,MoMonthNum,C12))>0, to where when the IF statement is true, it will give the value of that due date?

Posted 25-Sep-15 3:55am

Member 12011495

Updated 27-Sep-15 8:34am

Maciej Los

v2

Add a Solution

Comments

Richard MacCutchan 25-Sep-15 11:09am

Don't you just need to use the value of the cell with the assignment title rather than the string "Assignment Due"?

Member 12011495 25-Sep-15 11:18am

Doing that just gives a static answer. If i change value of the cell of the assignment title then value on the calendar will not change with this formula. If that makes sense

Richard MacCutchan 25-Sep-15 11:23am

It should do if you have auto recalculate on.

Member 12011495 25-Sep-15 12:06pm

Well there has to be something in place of the Assignment Due portion in order for that to happen. Like some other formula so that when the IF part is true, it will look and see what is in the cell that makes the statement true. Currently it just says Assignment Due when the statment is true.

Richard MacCutchan 25-Sep-15 12:19pm

I guess the use of COUNTIF is not sufficient here, as you need to find the actual cell that contains that text.

Member 12011495 25-Sep-15 12:36pm

Yeah im kind of confused on it. And im not too sure if im really explaining it right lol

Richard MacCutchan 25-Sep-15 12:40pm

Well pretty close, but it's difficult to visualise what your worksheet(s) actually look like. There are a number of forums on the net dedicated to Excel, you might like to try some of them.

Member 12011495 25-Sep-15 12:46pm

its the student assignment planner template if that helps

Richard MacCutchan 25-Sep-15 13:07pm

Sorry, never heard of it.

ZurdoDev 27-Sep-15 20:02pm

If you want to return the value from a different cell, reference that cell instead of the words "Assignment due."

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)