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

Array Formula doesn't see formula result; Does it need a value to work?

I received help from on a questions last week and the solution was perfect.


{(=IFERROR(INDEX(tblSales[[#All],[Date]],SMALL(IF(tblSales[[#All],[Employee]]=AH$3,ROW(tblSales[[#All],[Employee]])-ROW(A$2)+1),ROWS(AE$13:AE13))),"")}

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27983795.html

My original value in AH$3 changed because I needed to show the name & test # for my list instead of just the name. It showed Brent Van Scoy, but now shows Brent Van Scoy #1
To work around this problem with needing just the name for my array formula, I placed a formula in AH4

=LEFT(AH3;FIND("-";AH3;1)-1)

 But, it appears it doesn't see the results of my =left formula. It shows the name correctly in AH4 but , array formula doesn't work. I replaced the formula in AH4 with a text value and it works perfect.

Thanks
0
bvanscoy678
Asked:
bvanscoy678
  • 6
  • 3
2 Solutions
 
SteveCommented:
Surely the formula should be

=LEFT(AH3,FIND("-",AH3,1)-1)

Commas not Semicolons
0
 
bvanscoy678Author Commented:
yes, that is correct. The semi colons are the french version of excel. sorry for not switching it.

thanks
0
 
bvanscoy678Author Commented:
Thinking out loud, but I am wondering if I put an "on change event" to copy and paste special the results of my = left,  then point the array to that cell, it might solve my problem. I am in a meeting for 1 hour, but I'll read up on that once I am out.

thanks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SteveCommented:
And I do not belive that you can perform a LEFT (or any Text function eg CONCATENATE etc) on an array formula result. Though I am always amazed at what some people can acheive, so may be proved wrong.
0
 
SteveCommented:
If you are using VBA I would just write a function to do what you want.
Can you provide an example file and details of the desired outcome and I am sure we could do a User Defined Function if you would like a VBA solution.
0
 
bvanscoy678Author Commented:
Just got back. This worksheet has been stripped down pretty far, but it will show what I am trying to do.

The report page is what will be printed for each employee. Using the drop down menu to choose the employee's name, changes the value in Data!AH3. I use a name range to populate the drop down, which is the employee and their test #.

In Data!AE13 the array formula pointing towards Data!AH4 for the value to look up in the array.

I hope this makes a little sense being stripped down.

thanks
EE-Example.xlsm
0
 
barry houdiniCommented:
You probably need a TRIM function otherwise you have a "trailing space" i.e

=TRIM(LEFT(AH3,FIND("-";AH3;1)-1))

regards, barry
0
 
bvanscoy678Author Commented:
Thanks. Working on the "on change now".
0
 
bvanscoy678Author Commented:
I feel pretty stupid. I am trying to figure 6 ways around the problem and it turns out to be a simple thing that screwed up the formula. After I placed the Trim Function, it all works well. I'll have to double check it a few ways, but it appears solved!

Thanks
0
 
bvanscoy678Author Commented:
Thanks for the help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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