The formula returns a text value in a column of a table, when it encounters a blank cell it returns a 0, I would like it to return a blank. Conditional formatting in the destination cell won't work because I'll be using that data in a pivot table and it picks up the 0 even when it is not visible.
My formula is an array: =(INDEX(Analyze[#All],SMALL(IF(Analyze.Dog='3from analyze movavgtable'!$C$1,ROW(Analyze.Dog)),ROW(1:1)),10))
Thanks for your help.
Doug
Maybe this...
=IF(INDEX(Analyze[#All],SMALL(IF(Analyze.Dog='3from analyze movavgtable'!$C$1,ROW(Analyze.Dog)),ROW(1:1)),10)=0,"",INDEX(Analyze[#All],SMALL(IF(Analyze.Dog='3from analyze movavgtable'!$C$1,ROW(Analyze.Dog)),ROW(1:1)),10))
Another option would be to include a test for 0 on your CF rules?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks, but I think I figured it out as soon as I posted the question. I added &"" to the end of the formula like this =(INDEX(Analyze[#All],SMALL(IF(Analyze.Dog='3from analyze movavgtable'!$C$1,ROW(Analyze.Dog)),ROW(1:1)),10))&""
I'm checking to make sure it works through out the workbook.
Doug
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks