Auto-populate P.O. info

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
54
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!
 
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!
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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
Code:
=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
Code:
=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), "")
 
Upvote 0
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
Code:
=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
Code:
=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!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top