asked on

How can I return text using an array formula

I have a worksheet that is working great but in column "T" of worksheet "Nambucca_October 2011" the array formula returns a "Blank" where it should return "Text". The same code works in other colored cells in row (5) but not in Column "T", the macro places the formula in the colored cells.
I would like the code to return a "Blank" only if no data is available. In this test worksheet I have added data for testing. The worksheet "HardCopyOfMacro" shows the code with the line of code causing the problem "highlighted".
Can someone identify any issues and fix them.

THANKS
ReportTest-B.xlsm

krishnakrkc

Hi

This works for me.

=IFERROR(INDEX('ws1'!$A$4:$V$10000,MATCH(A5&TEXT(EDATE($B$2,-1),"mmmmyyyy"),'ws1'!$D$4:$D$10000 & 'ws1'!$C$4:$C$10000 & 'ws1'!$B$4:$B$10000,0), MATCH("Results2", 'ws1'!$A$2:$V$2,0)),"")

Array entered. Just remove the division part.

Kris

John Smith

ASKER

This code works a treat but returns "0" if cells are BLANK or no data available.
Is it possible to return a "BLANK" cell if no data available.

Also, can you explain "(1/ (1/" in plan english please.

THANKS

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Smith

ASKER

The talent by you guys is fantastic, you continually inspire me and show totally new areas to look at. The format changes work for me thanks.

THANKS