Auto-populate P.O. info

[TWienholz]

Hello all. I'm updating our P.O. form again as it has been requested that wave letter representations be placed behind our P.O. number so that anyone looking will know how many waves of collateral materials were ordered just by looking at the P.O. I tried to add on to my original formula which populated the cell with the first project number listed at the top of the form, but when trying to revise it to add the wave letters I got a generic error msg that provided no additional information or help whatsoever.


Original formula: {=IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))," ")}
RETURNED: Project #

Revised formula: {=IF(SUM(F7:W8),INDEX(F7:W8,MATCH(TRUE,ISNUMBER(F7:W8),0)), if(OR('Project Data Sheet'!B8="1",'Project Data
Sheet'!B9="English")),"A"),OR('Project Data Sheet'!B8="2",'Project Data Sheet'!B9="English")),"AB"),OR('Project Data
Sheet'!B8="3",'Project Data Sheet'!B9="English")),"ABC")}
RETURNED: ERROR MSG
SHOULD RETURN: Project#A, Project #AB , or Project #AB C, depending on #of waves and the language selected (could also be X, XY, XYZ if Spanish)

I'm sure this is a syntax error on my part, but I can't figure out where it might be. Any help would be greatly appreciated!

Thanks!

 

[TWienholz]

Yes, the first formula works because the second argument of MATCH, lookup_array, is a one dimensional array (in blue)
=IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))," ")

But, in the second formula it's a two dimensional array (in red), so it doesn't work.
=IF(SUM(F7:W8),INDEX(F7:W8,MATCH(TRUE,ISNUMBER(F7:W8),0)), if(OR('Project Data Sheet'!B8="1",'Project Data
Sheet'!B9="English")),"A"),OR('Project Data Sheet'!B8="2",'Project Data Sheet'!B9="English")),"AB"),OR('Project Data
Sheet'!B8="3",'Project Data Sheet'!B9="English")),"ABC")

As i said the MATCH function requires a one dimensional array as its second argument.

M.

Thank you for explaining! I went back and checked that formula and I don't know where that 8 came from because the formula read the single row as you stated. This file is making me crazy. Thanks again for the explanation!

 

[Marcelo Branco]

You are welcome.

By the way, it's easier to help with a small data sample along with the expected result and the logic.

M.

 

[mrhstn]

Hi

Let me see if I have the logic now.

Grab the first numeric value in F7:W7 and append 1, 2 or 3 letters (length specified in Project Data Sheet B8) of either ABC or XYZ depending on whether D9 of Project Data Sheet is English or Spanish, respectively?


Array formula requiring ctrl+shift+enter

=IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))&LEFT(LOOKUP('Project Data Sheet'!B9, {"English","Spanish"}, {"ABC","XYZ"}),'Project Data Sheet'!B8)," ")

Non-array version no need for CSE

=IFERROR(INDEX(F7:W7, AGGREGATE(15, 6, (COLUMN(F7:W7)-COLUMN(F7)+1)/ISNUMBER(F7:W7), 1))&LEFT(LOOKUP('Project Data Sheet'!B9, {"English","Spanish"}, {"ABC","XYZ"}),'Project Data Sheet'!B8), "")

 

[TWienholz]

Hi

Let me see if I have the logic now.

Grab the first numeric value in F7:W7 and append 1, 2 or 3 letters (length specified in Project Data Sheet B8) of either ABC or XYZ depending on whether D9 of Project Data Sheet is English or Spanish, respectively?


Array formula requiring ctrl+shift+enter

=IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))&LEFT(LOOKUP('Project Data Sheet'!B9, {"English","Spanish"}, {"ABC","XYZ"}),'Project Data Sheet'!B8)," ")

Non-array version no need for CSE

=IFERROR(INDEX(F7:W7, AGGREGATE(15, 6, (COLUMN(F7:W7)-COLUMN(F7)+1)/ISNUMBER(F7:W7), 1))&LEFT(LOOKUP('Project Data Sheet'!B9, {"English","Spanish"}, {"ABC","XYZ"}),'Project Data Sheet'!B8), "")

Your array formula worked perfectly! Thank you so very much! I am not as familiar with the INDEX functions and didn't know how to combine all of my requirements properly, hence the syntax errors. This is awesome! Thanks again!