Excel/Blanks inserted by
QUESTION: I have set up a workbook to compile four worksheets into one. The trouble is that the people who provide the original data have slight amendments in each of their submissions. I used "=IF(EAC!A11="","",IF(EAC!$D11="","",EAC!A11))" to tell the sheet only to pick out those items where a specific cell in each row contained a value. My view was, if the original cell was blank, I wanted a blank, if the original cell was not blank but the specific column cell was blank, I wanted a blank. If they both contained values, I wanted the value of the original cell.
I then copied my table, just values, and sorted it on the column which identified whether I was interested in the row, to put all the blanks at the bottom, so that I could concentrate only on the active data.
What I wanted to do next was to use end an down to establish the address of the lowest active entry, but the cursor moved to the bottom of the maximum number of inputs; as I was starting with four 200 row tables, this was 800 lines down.
How do I make the IF function leave the cell empty, so that end down only goes to the bottom of the data?
but the cell isn't empty - it has a formula in it and there is nothing that will make excel think it is empty.
Why not apply a filter to the data and only show rows that have a value.
Then you wouldn't even need to sort it - but that would work with it sorted as well.
when you did your end down arrow it would stop on the last filled row (in either case) because it would not consider the hidden rows.
---------- FOLLOW-UP ----------
QUESTION: The table containing the formulae has a formula in every cell of the data range but, when I copy it to another sheet using Paste Special, Values, why does the "" not just become a blank cell?
I have checked that you solution works, so many thanks for that, but I am intrigued as to why "" is not a blank, when copied as a value.
Because it is a null string and not empty.
I can't tell you much beyond that. I have looked at how information is stored in the old file format and there are "types" of records that store information about cells. I suspect the record for the cell that determines whether it is empty says that it is not empty. It is just a decision that was made by the designers/programmers of the excel application. There may be a legitimate reason it is that way - it may have to do with the definition of the used range - I don't really know - I just know it is that way.
if you click in your data and do Ctrl+Shift+8 those null string cells will be selected. If you select the null string cells and clear them, then do the same (click in the data and do Ctrl+Shift+8), they won't be included in the selection. that isn't showing you anything new, but it does emphasize that the cells are still considered not to be empty.