• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

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
0
user2073
Asked:
user2073
  • 2
  • 2
1 Solution
 
krishnakrkcCommented:
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
0
 
user2073Author Commented:
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
0
 
krishnakrkcCommented:
Select the range and custom format.

Format cells > Custom > Type: [=0]"";General > OK

(1/(1/ creates #DIV/0! error if no values found.

But when return value is text it always generates the #DIV/0! error, so it displays blank.

Kris
0
 
user2073Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now