Hi,
I have a workbook where I have a sheet (sheet1) that contains document names in column A and a days until deadline in column J.
In a sheet called sheet5 I have a IF function that says that if the "days until deadline" is over 0 but under 120 then the document name will be copied from the specific cell in sheet1 column A to sheet5.
To give an idea of what I mean, this is what I have written in column A in sheet5. I have the same code in columns B,C,D,E,F,G in sheet5 but for different sheets (sheet2, sheet3, sheet4 etc.)
=IF(AND('sheet1'!J1>0;'sheet1'!J1<120);'sheet1'!A1;"")
=IF(AND('sheet1'!J2>0;'sheet1'!J2<120);'sheet1'!A2;"")
=IF(AND('sheet1'!J3>0;'sheet1'!J3<120);'sheet1'!A3;"")
=IF(AND('sheet1'!J4>0;'sheet1'!J4<120);'sheet1'!A4;"")
Now, this works fine. But if A2 in sheet 1 is higher than 120, then this will return a blank in cell A2 in sheet5.
If then the column B2 contains a document name from sheet2, this is where the problem starts.
If I filter the blanks away in column A, then excel removes the whole 2-row which means that the cell B2 containing a document name will also go away.
How do I get excel to only filter away blanks in that one specific column and not filter complete rows?
I have a workbook where I have a sheet (sheet1) that contains document names in column A and a days until deadline in column J.
In a sheet called sheet5 I have a IF function that says that if the "days until deadline" is over 0 but under 120 then the document name will be copied from the specific cell in sheet1 column A to sheet5.
To give an idea of what I mean, this is what I have written in column A in sheet5. I have the same code in columns B,C,D,E,F,G in sheet5 but for different sheets (sheet2, sheet3, sheet4 etc.)
=IF(AND('sheet1'!J1>0;'sheet1'!J1<120);'sheet1'!A1;"")
=IF(AND('sheet1'!J2>0;'sheet1'!J2<120);'sheet1'!A2;"")
=IF(AND('sheet1'!J3>0;'sheet1'!J3<120);'sheet1'!A3;"")
=IF(AND('sheet1'!J4>0;'sheet1'!J4<120);'sheet1'!A4;"")
Now, this works fine. But if A2 in sheet 1 is higher than 120, then this will return a blank in cell A2 in sheet5.
If then the column B2 contains a document name from sheet2, this is where the problem starts.
If I filter the blanks away in column A, then excel removes the whole 2-row which means that the cell B2 containing a document name will also go away.
How do I get excel to only filter away blanks in that one specific column and not filter complete rows?