[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I return text using an array formula

Posted on 2012-08-31
4
Medium Priority
?
518 Views
Last Modified: 2012-08-31
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
Comment
Question by:user2073
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38356565
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
 

Author Comment

by:user2073
ID: 38356592
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38356613
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
 

Author Closing Comment

by:user2073
ID: 38356624
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question