Get max value on unsorted row with 3 different number groups.

[cosima]

The table in Excel 2010 is used to regeister all details for 3 different types of invoices each
in a column and each ID is a sequence within each group. I want to use a formula to solve this.


The groups of invoice ID's :
Type1 range 20000-30000
Type2 range 4000-10000
Type3 range 50000-60000


Excample row should look like this :

Type	Type1	Type1	Type2	Type3	Type1	Type1	Type2
InvoiceId	20000	20001	4000	50000	20002	20003	4001
ClientId	333	444	55	6666	777	888	999
Amount	1220	333	666	555	888	666	333

<tbody>
</tbody>

I have tried using HLOOKUP :
HLOOKUP ("Max-value range";$B$2:Z$3;1;TRUE)+1 to get max value from row 1. THe result is not
as expected so can anyone help me ?

 

[QU4487]

Try this, is you want to return the Max value for a particular "Type";

Excel 2012

	A	B	C	D	E	F	G	H
1	Type	Type1	Type1	Type2	Type3	Type1	Type1	Type2
2	InvoiceId	20000	20001	4000	50000	20002	20003	4001
3	ClientId	333	444	55	6666	777	888	999
4	Amount	1220	333	666	555	888	666	333
5
6
7		Max Invoice ID
8	Type1	20003
9	Type2	4001
10	Type3	50000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B8 {=MAX(IF($B$1:$H$1=A8,$B$2:$H$2))} B9 {=MAX(IF($B$1:$H$1=A9,$B$2:$H$2))} B10 {=MAX(IF($B$1:$H$1=A10,$B$2:$H$2))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope this is what you're looking for.

Q

 

[cosima]

Try this, is you want to return the Max value for a particular "Type";

Excel 2012

	A	B	C	D	E	F	G	H
1	Type	Type1	Type1	Type2	Type3	Type1	Type1	Type2
2	InvoiceId	20000	20001	4000	50000	20002	20003	4001
3	ClientId	333	444	55	6666	777	888	999
4	Amount	1220	333	666	555	888	666	333
5
6
7		Max Invoice ID
8	Type1	20003
9	Type2	4001
10	Type3	50000

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B8 {=MAX(IF($B$1:$H$1=A8,$B$2:$H$2))} B9 {=MAX(IF($B$1:$H$1=A9,$B$2:$H$2))} B10 {=MAX(IF($B$1:$H$1=A10,$B$2:$H$2))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope this is what you're looking for.

Q

Hi and thanks for helping but when I put the formula on row 2 (IncoiceID) I get circle ref and that is also a problem I got.

Do you have any proposal to solve it ?

Best regards!

 

[QU4487]

Where do you need the formula to be? You're getting a circular reference because the formula is using row 2.

 

[cosima]

I need to register each invoice per column by selecting a dropdown with Type1-3 and then get it's next available invoiceID on row 2 for InvoiceID's.

 

[QU4487]

I think I get what you're attempting to do. Maybe. You want to keep adding columns to the right of Column A. In Row 1 you then have a drop down of which type it is, and in row 2 you then want your formula. This formula will then be one number higher than the previous highest for that type. Is this right?

The below formula will do this. But it isn't perfect. As you insert the column and paste the formula in you need to make sure that column C stays as column C.

=MAX(IF($C$1:$I$1=B1,$C$2:$I$2))+1

My only other recommendation is to find somebody who can write a macro which does this. Macro's are most definitely not my specialty.

Q

 

[cosima]

Hi and thanks for your support but I can't make it work properly. I'm pasting the formula into d2, e2 and onto the rest of the row. Result is not OK ?
Perhaps I have done it wrong ? Also I try to stick with using formulas not makros and Vb.

 

[cosima]

Hi and thanks for your support but I can't make it work properly. I'm pasting the formula into d2, e2 and onto the rest of the row. Result is not OK ?
Perhaps I have done it wrong ? Also I try to stick with using formulas not makros and Vb.

I forgot to feedback that your understanding of how it should work is correct. The other question is did you test your formula in row 2 in the sample table ?

Lokk forward to you and others feedback !

 

[cosima]

Invoice Type	Type1	Type1	Type2	Type3	Type1	Type1	Type2
InvoiceId	20000	20001	#VERDI!	#VERDI!	#VERDI!	#VERDI!	#VERDI!
ClientId	333	444	55	6666	777	888	999
Amount	1220	333	666	555	888	666	333
↓	↓	↓	↓	↓	↓	↓	↓
Table Types	Min ID	Max ID		Translations :
Type1	20000	29999		#VERDI =	#VALUE
Type2	4000	4999		MAKSA =	MAX
Type3	50000	59999		HVIS =	IF
				; =	,
Formula :	MAKSA(HVIS($B$1:C$1=D1;$B$2:C$2+1;HVIS(D1="Type1";20000;HVIS(D1="Type2";4000;HVIS(D1="Type3";50000)))))
translated:	MAX(IF($B$1:C$1=D1;$B$2:C$2+1,HVIS(D1="Type1",20000,HVIS(D1="Type2",4000;HVIS(D1="Type3",50000)))))
Error :	In d2 warning is given (!) --> A value used in the formula has wrong datatype / In dropdown warning : Wrong value
Debug :	When step/by step in drop down warning :
	Evaluate -->	#VALUE=Type2
Datatype:	D1 and D2 have datatype Standard (Tested with text but same result)

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

COMMENT : I have now tested the formula but it ends with #VERDI = #VALUE. Inside the formula it states "Formula result" correctly but shows #VALUE ???

I will be very glad to receive any help and feedback !!!