Hi,
I am trying to display a blank entry as a blank instead of Jan 00. I have tried the following formula but no joy, would anybody be able to have a look to see what I am doing wrong?
=IF''"&$A30&''!B:B"="";"";MAX(INDIRECT("'"&$A30&"'!B:B))+$B$4)
Rgds
Harry
''''''''''''''
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Sorry, I am not sure I understand, I would like to keep the max indirect formula?
Many thanks Jacc, got thee in the end!
Ok, this thread will look weird to someone stumbling on it.I posted a suggestion in my first post based on a quick hunch with zero thinking behind it. As I looked at what I posted I realized that there were most likely more issues than I first though. I edited my post with a comment about that. A few moments later I started to have doubts about the whole thing and decided to just clear the post and let someone else give it a try.
Apparently in that brief period of time the dubious post was up, you managed to get something out of it Harrytheb. I am surprised to say the least but hey, if it works it works!
I'm kind of curious of what you came up with though. Would you mind posting the formula that now works?
Right, I got a solution in one cell but not the others around it! The max and indirect original formula works fine, transferring data (dates) from one tab to the other. The problem I have is that if there is no data on the source column than it returns a date of 1 Jan 00. I am trying to get a blank in this instance, thus I thought about using the IF command but I am not sure that I have syntax right. It is late in the office and not thinking straight! Harry
Is 0 an otherwise valid result?
Jan 00 means the formula is returning 0.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I understand the 0 return, but trying to have the cell display a blank to make for easier scanning. Harry
Hi, so the cells are formatted as dates right?
You just need to change the formatting to achieve what you want. Don't need a formula.
You can use custom formatting or conditional formatting to do it.
Custom formatting
Navigate to the formatting dialogue (control-1) and select custom format at the bottom if the list. The structure of the custom formatting is like this:
Positive format; negative format; format for zero; format for text
If you enter:
dd/mm/yyyy;"";""
Then you will get a date format for positive numbers and a blank for negative or zero.
Conditional Formating
Just make formula rule for cells with zero value and set the font colour the same as the full colour. (White for example).
Sent from my iPhone using Tapatalk
OK, let's look at your formula:Originally Posted by Harrytheb
This is incorrect:=IF''"&$A30&''!B:B"="";"";MAX(INDIRECT("'"&$A30&"'!B:B))+$B$4)
''"&$A30&''!B:B"
That should also be wrapped inside an INDIRECT function:
INDIRECT("'"&$A30&"'!B:B")
However, you're testing the entire column to be = to blank "":
IF(INDIRECT("'"&$A30&"'!B:B")=""
It won't work like that. Are you wanting to test that a SINGLE cell is blank? Or, do want to test that EVERY cell is blank?
Another thing, what's in cell B4?
Again, a formula is not required based on the above message. It's just a matter of formatting zeros to be blank. Adding extra complexity to the cell formulas to do this is not a very good idea.
Sent from my iPhone using Tapatalk
The cell will still contain numeric 0. Formatting just "hides" it. There may be formulas downstream that are referencing this result so you might have to account for the 0 in one formula or the other.
Still a bad idea. 😃
If the downstream formula has a problem then it should be handled by the downstream formula.
Based on the quote on my previous post, there is no problem with the cell containing 0.
Sent from my iPhone using Tapatalk
Everyone's entitled to their own opinion!
I have created a kind of diary of dates under column headings on the input tab with the review tab showing that last date in the column. I already use conditional formatting in a traffic light format for the reviewer. I get the custom format and will have a try out in the morning, as it is easier to incorporate the columns collectively than as singly. Being a evolving geek, I would still for my piece of mind, like to understand where I went wrong with the formula? By the way thanks to both of you, love the dialogue! Rgds H
So, then the formula is to return the max date + the value of B4?
Again, what's in B4?=IF''"&$A30&''!B:B"="";"";MAX(INDIRECT("'"&$A30&"'!B:B))+$B$4)
The only way the formula can return 0 is if no dates (or numbers of any kind) are in SheetName column B AND cell B4 = 0 (or possibly a negative number that when added to the max date = 0).
Here's a guess.
=IFERROR((1/(1/MAX(INDIRECT("'"&$A30&"'!B:B")))+$B$4),"")
Harrytheb, is there any chance that you could post a sample workbook? It would make it much easier for you to get help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks