Mellya (mellyjc) wrote in excel_juice,
Mellya
mellyjc
excel_juice

  • Mood:

Automatic selection from drop-down list

 Hello,

I use Excel regularly, but never in any extremely fancy capacity (thus far, lack of time to play and learn, sadly).  I've created myself a worksheet to track my work.

For the most part, this is incredibly basic.  The column headers are item-specific: person name, hotel, type, date due, status, comments.  
The first four columns will be pasted as received from an email.  Comments are entered after the fact.

I have the status column pulling from a list on a separate worksheet.

The complexity comes in where I'd like the status to update as sort of an alert to look for this new task to be available for completion.  The algorithm looks like this:

IF cell F8 is BLANK
AND IF cell E8 <= Today's date
THEN cell F8 = [entry from list "Stat", cell A2]

Is this doable?  If so, how?

Thank you so much!
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 4 comments

Deleted comment

Yells at me for circular :(

Thank you though!
Okay, well, it's somewhat difficult to have F8 both have a formula and be the target of manual entry. The entry would overwrite the formula.

Excel does not like to have formulas put data into *other* cells. At least, that's what I've seen thus far.

Perhaps something more like this would suffice?

Cell X12 (irrelevant where, just off in a corner) has the formula =NOW() and is formatted to yyyy/mm/DD or whatever format you use for dates, so that it always has today's date in it)

Then use a nested if. (two if statements in one cell)

=IF(F8="",IF(E8=$X$12,A2,""),F8)

What that'll do is the following...

First, it checks F8. If it is blank (=""), it will then perform the second IF. That one says to check E8 and see if it is the same as X12 (the dollar signs will allow you to copy & paste the formula into other cells without the cell X12 changing with it). If it is the same date, then it will equal A2. If not, it stays blank.

If the first if is false and F8 is not blank, then it will copy F8.

So you'll have one cell that you can manually enter data: F8
And another that populates via formula. It will copy F8 if it is used, otherwise it checks the A2 list.

That help?
That makes perfect sense (great explanation, thank you!), but it's giving me a circular reference error.
I'd have to see where you're putting things to know where you're getting the circular reference.

Just remember the cell where you can manually enter a date does not get a formula in it.