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!

 

[mrhstn]

Hi

First point to note is I think F7:W8 should be on just row 7 - F7:W8

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

 

[Marcelo Branco]

Is hard to help without a data sample, but in a quick look i can see an error in your formula.
This doesn't work
MATCH(TRUE,ISNUMBER(F7:W8),0))
The MATCH function works only when the 2nd argument (lookup_array) is a vertical/horizontal one dimensional array.
In your formula F7:W8 is a matrix (two dimensional array)

M.

 

[TWienholz]

Hello Marcelo and thank you for the input! But that first MATCH formula worked fine. It populated the first project number in the array with no problem. It's combining that with the IF statement that references the number of waves and the language that I'm having problems with. Does that make sense? Is this even something that can be combined without me having to redo the entire formula?

 

[TWienholz]

Thank you so much for responding. I can't get that information you mentioned in one row because of how it is represented on the form itself. It is working fine however by itself, it's just when I am trying to combine it with an IF formula that references the number of waves and the language to give me that WAVE LETTER designation behind the P.O. number. The formula you provided doesn't account for a single wave ("A") or a double wave ("AB"), or take into consideration the reference to the cell that indicates the number of waves needed which is what will tell the formula which WAVE LETTER(S) to use. Does that make sense? The post won't let me send you a screenshot of what I'm talking about so I hope I've described it well enough.

 

[TWienholz]

Hi

First point to note is I think F7:W8 should be on just row 7 - F7:W8

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

Thank you so much for responding. I can't get that information you mentioned in one row because of how it is represented on the form itself. It is working fine however by itself, it's just when I am trying to combine it with an IF formula that references the number of waves and the language to give me that WAVE LETTER designation behind the P.O. number. The formula you provided doesn't account for a single wave ("A") or a double wave ("AB"), or take into consideration the reference to the cell that indicates the number of waves needed which is what will tell the formula which WAVE LETTER(S) to use. Does that make sense? The post won't let me send you a screenshot of what I'm talking about so I hope I've described it well enough.

 

[TWienholz]

Is hard to help without a data sample, but in a quick look i can see an error in your formula.
This doesn't work
MATCH(TRUE,ISNUMBER(F7:W8),0))
The MATCH function works only when the 2nd argument (lookup_array) is a vertical/horizontal one dimensional array.
In your formula F7:W8 is a matrix (two dimensional array)

M.

Hello Marcelo and thank you for the input! But that first MATCH formula worked fine. It populated the first project number in the array with no problem. It's combining that with the IF statement that references the number of waves and the language that I'm having problems with. Does that make sense? Is this even something that can be combined without me having to redo the entire formula?

 

[mrhstn]

I think you missed the point Marcelo Branco made (I made a typo trying the same)

You have this

=IF(SUM(F7:W8),INDEX(F7:W8,MATCH(TRUE,ISNUMBER(F7:W8),0))

The bits in red won't work, see your first for the difference

{=IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))," ")}

And i don't get the second part or your revised formula, I get an error even if I remove the sheet reference. If I untangle the spaghetti I get that

if D8 = 1 then return A
if D8 = 2 then return AB
if D8 = 3 then return ABC

So returning the first [D8] letters in ABC...no??

Really you need to be clear, provide an example of what you have and the result you need.

 

[Marcelo Branco]

Hello Marcelo and thank you for the input! But that first MATCH formula worked fine. It populated the first project number in the array with no problem. It's combining that with the IF statement that references the number of waves and the language that I'm having problems with. Does that make sense? Is this even something that can be combined without me having to redo the entire formula?

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.

 

[TWienholz]

I think you missed the point Marcelo Branco made (I made a typo trying the same)

You have this

=IF(SUM(F7:W8),INDEX(F7:W8,MATCH(TRUE,ISNUMBER(F7:W8),0))




The bits in red won't work, see your first for the difference

{=IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))," ")}

And i don't get the second part or your revised formula, I get an error even if I remove the sheet reference. If I untangle the spaghetti I get that

if D8 = 1 then return A
if D8 = 2 then return AB
if D8 = 3 then return ABC

So returning the first [D8] letters in ABC...no??

Really you need to be clear, provide an example of what you have and the result you need.

Basically, the format needs to read Project #+# of Wave Letter, i.e. in this instance, 9138000AB since there are 2 waves needed for this order of envelopes. The formula that is pulling that 9138000 number currently is the array formula =IF(SUM(F7:W7),INDEX(F7:W7,MATCH(TRUE,ISNUMBER(F7:W7),0))," ") which references the range of numbers in green.

Sample Number(s):	9138000	9138001	9138002	9138003	9138004	9138005
	9138006	9138007	9138008	9138009	9138010	9138011
MPL #:	101020
Client Name:	ABC Company
Parent Company:									P.O. Number:	C–	9138000
Project Description:									Need By Date:	7/27/2018
LETTERHEAD	#10.5 ENVELOPES	6 x 10 ENVELOPES	CUSTOM ENVELOPES
Waves w/Letterhead:	2	# of Waves:	2	# of Waves:	2	# of Waves: 2

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I need to add (I think) an IF formula to that that also incorporates the formula you originally gave me but for English AND Spanish so that one of the following 2 conditions is met:

IF('Project Data Sheet'!B9="English",LEFT("ABC",'Project Data Sheet'!B8)," ") OR
IF('Project Data Sheet'!B9="Spanish",LEFT("XYZ",'Project Data Sheet'!B8)," ")

The formula that you provided is still giving me the project number, but no letter designation.

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

Does this explain things a bit better?