formula not returning blank

[howard]

I have the following array formula that does not return a blank where there is no text in Col A in sheet "detail by month"


={IF('Detail by Month'!A6="","",TRANSPOSE('Detail by Month'!$A6:A500))}

It would be appreciated if someone could kindly assist

 

[Cosmic Wizard]

I have the following array formula that does not return a blank where there is no text in Col A in sheet "detail by month"


={IF('Detail by Month'!A6="","",TRANSPOSE('Detail by Month'!$A6:A500))}

It would be appreciated if someone could kindly assist

Stop if I am on the wrong track, but your formula only works if there is a blank cell: i.e. if the cell has text, it will remain that text.

You need a second If statement in your formula. Something like =IF(A6="","",IF(A6="?*","hot","cold"))

Basically, if A6 is Blank, it returns blank, but if there is text in it, it does the second formula.

Hope that helps, if not, reply back and we see if we can fix it!

 

[howard]

Thanks for the help

See sample data below from another workbook . If for eg A9, A10 has no data , formula must return blank

As can be seen, formula in F2 , G2 etc returns 00/01/00 and not blank

Excel 2012
	C	D	E	F	G	H
2	Mark Thompson	Craig Thomas	ASHLEY Parker	00/01/00	00/01/00	00/01/00
Sick & Training

Cell Formulas
Range	Formula
C2:CZ2	{=IF('Training & Sick Leave taken'!A6="","",TRANSPOSE('Training & Sick Leave taken'!$A6:A500))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[konew1]

For your formula to work you need to select the entire range where the formula is required and then enter the formula. You seem to be doing this.
When the result is coming from an empty cell the formula returns "0". Your cell format is date mm/dd/yy, so you get 00/01/00
If you are only expecting names to be returned you can format the cells to not display zero.
Otherwise modify the range in this =IF(TRANSPOSE(Sheet5!$A6:A500)=0,"",TRANSPOSE(Sheet5!$A6:A500)) as an array formula

 

[Cosmic Wizard]

For your formula to work you need to select the entire range where the formula is required and then enter the formula. You seem to be doing this.
When the result is coming from an empty cell the formula returns "0". Your cell format is date mm/dd/yy, so you get 00/01/00
If you are only expecting names to be returned you can format the cells to not display zero.
Otherwise modify the range in this =IF(TRANSPOSE(Sheet5!$A6:A500)=0,"",TRANSPOSE(Sheet5!$A6:A500)) as an array formula

That works like a charm!!

I got as far as =IF($A5="","",IF($A5="?*",(TRANSPOSE($E5:F5)),$A5)), which did work to move all of A5 but that it.

Great stuff konew!

 

[howard]

Hi Guys

Thanks for your input much appreciated