Last column used

[Guest]

I exported from QuickBooks a list frm cust. total of sales by the month. I
need in the last column a formula that should come up with the last month
purchased.


NAME Jan Feb Mar Apr Last month purchased
Abe 0 250 0 450 =Apr
Mark 500 0 0 0 =Jan
Larry 0 0 600 0 =Mar
Jack 0 350 0 0 =Feb


Thanks. ......."


__._,_.___

 

[Don Guillett]

Look in the help index for MATCH

 

[Ragdyer]

Say your headers are in Row1, with Jan to Dec in B1 to M1,
And names down Column A, starting in A2.

Enter this *array* formula in N2:

=INDEX($B$1:$M$1,MAX(IF(B2:M2>0,COLUMN($A$1:$L$1))))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy the formula down Column N as needed.

 

[Don Guillett]

try this
=TEXT(DATE(YEAR(TODAY()),MATCH(999999,B2:m2),1),"mmm")

 

[Ragdyer]

I also started out that way Don, but those 0's throw a monkey wrench into
the formula, since they *are* numbers.


Regards,

RD

 

[Guest]

B1:E1 - hold months Jan to April
B2:E2 - hold values

In F2: =INDEX($B$1:$E$1,MAX(INDEX((B2:E2>0)*(COLUMN($A$1:$D$1)),0)))

Just hit ENTER

 

[Don Guillett]

RD, Thanks. I didn't think of that

 

[Guest]

You can use this formula:
=TEXT(DATE(YEAR(TODAY()),MATCH(999999,B2:m2),1),"mmm")