Question description:
This user has given permission to use the problem statement for this
blog.
I have a question on an IF BLANK statement.
Solved by C. L. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/08/2018 - 01:14
Hello
User
10/08/2018 - 01:14
Hello
Excelchat Expert
10/08/2018 - 01:14
How are you?
User
10/08/2018 - 01:14
I Hi, fine thank you.
User
10/08/2018 - 01:15
I don't know if this request is possible or not...
Excelchat Expert
10/08/2018 - 01:15
Nice to hear that.
Excelchat Expert
10/08/2018 - 01:15
Please tell me and I will try my level best to assist you.
User
10/08/2018 - 01:15
I manage a large spreadsheet set up in a table an dlots of data feeds into it via lookups, etc.
Excelchat Expert
10/08/2018 - 01:15
Ok.
User
10/08/2018 - 01:15
There are several columns where people make manual updates.
User
10/08/2018 - 01:16
This is a list of projects that we are tracking on the spreadsheet.
Excelchat Expert
10/08/2018 - 01:16
Ok
Excelchat Expert
10/08/2018 - 01:17
Please tell me further.
User
10/08/2018 - 01:17
I was asked on one of the columns (a manual entry column) to do a formula where the field defaults to "No" until the person goes in an enters a date in the cell.
User
10/08/2018 - 01:18
so the cell has to have the MM/DD/YYYY format
User
10/08/2018 - 01:18
so the only thing that can be typed in that field is a date. Yet the cell is to default to "no" automatically first, until a date is entered.
Excelchat Expert
10/08/2018 - 01:19
so basically we are looking for a column to show No as default until the date is entered in the format MM/DD/YYY
User
10/08/2018 - 01:19
correct
User
10/08/2018 - 01:20
it's a column for project managers and they will see a big fat NO in this column which is for an important milestone, until they update it with a completion date.
Excelchat Expert
10/08/2018 - 01:20
Ok.
Excelchat Expert
10/08/2018 - 01:22
Please allow me to check few things.
User
10/08/2018 - 01:23
ugh so this makes it more complicated...there is already a formula in the field. I misspoke earlier when I said it was a manual field. Should I send you file with the formula?
Excelchat Expert
10/08/2018 - 01:23
Yes, that would help
Excelchat Expert
10/08/2018 - 01:23
Please share the file so that I can relate with it.
User
10/08/2018 - 01:23
sorry, just give me a sec and I will send.
Excelchat Expert
10/08/2018 - 01:24
Ok.
Excelchat Expert
10/08/2018 - 01:24
There is a way around
Excelchat Expert
10/08/2018 - 01:24
if you want to hear this.
User
10/08/2018 - 01:24
okay
Excelchat Expert
10/08/2018 - 01:25
we can keep the cell colored using conditional formatting until data is manually entered or until we have the desired result of the formula.
Excelchat Expert
10/08/2018 - 01:26
But please share the file, looking at formula, I might able to do something as per your requirement as this is working through formula.
User
10/08/2018 - 01:26
my sorry I had to strip some data. Just about done.
Excelchat Expert
10/08/2018 - 01:26
No problem. Please take your time.
User
10/08/2018 - 01:29
I think this is going to be too confusing. shoot.
User
10/08/2018 - 01:29
I need more time to prepare a dummy file. Here is something I did real quick
Excelchat Expert
10/08/2018 - 01:29
Ok. Please send it across.
User
10/08/2018 - 01:29
but there are tons of tabs this spreadsheet feeds from which all I am giving you is a copy of the final tab.
Excelchat Expert
10/08/2018 - 01:30
Ok.
User
10/08/2018 - 01:30
the column I am needing this functionality on is column W
[Uploaded an Excel file]
User
10/08/2018 - 01:30
the current formula in column W is :
User
10/08/2018 - 01:30
=IFERROR(IF(INDEX('Daniel PDS'!Y:Y,MATCH($A2,'Daniel PDS'!J:J,0))="","",INDEX('Daniel PDS'!Y:Y,MATCH($A2,'Daniel PDS'!J:J,0))),"")
Excelchat Expert
10/08/2018 - 01:31
Let me open the file
User
10/08/2018 - 01:31
of which the tab Daniel PDS is not included in what I sent you
User
10/08/2018 - 01:31
you're a genius if you can figure this all out! lol
Excelchat Expert
10/08/2018 - 01:31
Ok. allow me a minute
User
10/08/2018 - 01:32
of course
Excelchat Expert
10/08/2018 - 01:32
so these dates are populated using the above formula
User
10/08/2018 - 01:33
yes so the sequence would be ....populate column with the date from that formula if there is one available
User
10/08/2018 - 01:33
if not, populate with NO
Excelchat Expert
10/08/2018 - 01:33
Ok.
User
10/08/2018 - 01:33
Lastly, the person should be able to go in and replace NO with a date.
Excelchat Expert
10/08/2018 - 01:33
Manually?
Excelchat Expert
10/08/2018 - 01:34
replacing No manually means - deleting the formula.
Excelchat Expert
10/08/2018 - 01:35
=IFERROR(IF(INDEX('Daniel PDS'!Y:Y,MATCH($A2,'Daniel PDS'!J:J,0))="","No",INDEX('Daniel PDS'!Y:Y,MATCH($A2,'Daniel PDS'!J:J,0))),"No")
Excelchat Expert
10/08/2018 - 01:35
use this formula, it will give you a "No", if date is not found in Column J on Daniel PDS.
User
10/08/2018 - 01:37
maybe I was making it too complicated. okay, then I guess I set the format to date so they can only enter in the cell with a date format?
User
10/08/2018 - 01:38
or can I do some Data Validation ?
Excelchat Expert
10/08/2018 - 01:38
You can do Data Validation
Excelchat Expert
10/08/2018 - 01:38
this will not allow user to enter anything else other than date
Excelchat Expert
10/08/2018 - 01:38
and you can set the date format
User
10/08/2018 - 01:40
okay I didn't know if I could do the date format data validation considering there are "no's" in some cells.
User
10/08/2018 - 01:40
okay
Excelchat Expert
10/08/2018 - 01:40
Go to data validation, in the Allow section - Select - date then in the Data section: choose as per your requirement like greater than or less than or between,
User
10/08/2018 - 01:41
I gives me the green triangle errors....
Excelchat Expert
10/08/2018 - 01:41
You enter the formula first and then do the data validation
User
10/08/2018 - 01:41
but I guess I just select "ignore errors"?
Excelchat Expert
10/08/2018 - 01:41
Yes, if everything is working fine, you can ignore errors.
User
10/08/2018 - 01:42
okay thank you very much.
Excelchat Expert
10/08/2018 - 01:42
I hope, I could help as much as I can with the limited information I have.
User
10/08/2018 - 01:42
yes it was great.thanks!
Excelchat Expert
10/08/2018 - 01:42
I appreciate your patience.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
