Razz spreadsheet for sports cards

[Tom and Rons Pools]

I am looking for a few things here.

1st - if i input names in the H column under name. How do I get them to go to the name column in column B if they take more than 1 spot?
2nd - in c7 and c8 I want to know if there is a formula to create a deal based of spots taken. for example 1 spot = 5.50 per spot, but if you buy 3 spots its $5 per spot and if you buy 10 spots its $4.75 p/ spot. Something along those lines.
3rd - the current formula in c9 is a vlookup formula based on the c6. can i incorporate the values in c7 and c8 based off of how many spots people buy.

I know this may seem confusing but I will be happy to answer any questions you may have.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last 3 Comps:		$ 600.00	$ 445.00	$ 525.00		Spots	1	2	3	4	5	6	7	8
2	Average:		$ 523.33				Price/p	$ 530.00	$ 265.00	$ 176.50	$ 132.50	$ 106.00	$ 88.50	$ 75.50	$ 66.50
3	RoundUp w/extras		$ 530.00
4
5		# of Spots:	100
6		$ p/spot:	$ 5.50
7		$ p/spot (3 spots)
8		$ p/spot (10 spots)
9
10
11	# of Spots	Name	$/Spot					Name	Spots	Total	Paid			Source	Amt.
12	1		$ 5.50											Venmo	$ -
13	2		$ 5.50											PayPal	$ -
14	3		$ 5.50											Zelle	$ -
15	4		$ 5.50											CashApp	$ -
16	5		$ 5.50
17	9		$ 5.50
18	10		$ 5.50
19	11		$ 5.50
20	12		$ 5.50
21	13		$ 5.50
22	14		$ 5.50
23	15		$ 5.50
24	16		$ 5.50
25	17		$ 5.50
26	18		$ 5.50
27	19		$ 5.50
28	20		$ 5.50
29	21		$ 5.50
30	22		$ 5.50
31	23		$ 5.50
32	24		$ 5.50
33	25		$ 5.50
Sheet1

Cell Formulas
Range	Formula
H2:O2	H2	=MROUND($C$3/H1,0.5)
C2	C2	=AVERAGE(C1:E1)
C3	C3	=ROUNDUP(C2,0)+6
C6	C6	=VLOOKUP(C5,price_list[#All],2,0)
O12:O15	O12	=SUMIF($K$12:$K$129,N12,$J$12:$J$129)
A12:A16	A12	=VLOOKUP($C$5,Spots_List,ROW(B2),0)
A17:A33	A17	=VLOOKUP($C$5,Spots_List,ROW(B10),0)
C12:C33	C12	=IF(A12>0,$C$6,"")

Named Ranges
Name	Refers To	Cells
money_list	=price_list[Spots]	C6
Spot_dd	=Spots_List[Column1]	A12:A33
Spots	=Sheet1!$H$1:$AI$1	H2

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=Spot_dd
K12	List	Venmo,PayPal,Zelle,CashApp

 

[Eric W]

Is this what you're looking for?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last 3 Comps:		600	445	525		Spots	1	2	3	4	5	6	7	8
2	Average:		523.3333				Price/p	530	265	176.5	132.5	106	88.5	75.5	66.5
3	RoundUp w/extras		530
4
5		# of Spots:	100
6		$ p/spot:	$ 5.50
7		$ p/spot (3 spots)	$ 5.00
8		$ p/spot (10 spots)	$ 4.75
9
10
11	# of Spots	Name	$/Spot					Name	Spots	Total	Paid			Source	Amt.
12	1	Al	$ 5.50					Al	2	$ 11.00				Venmo	0
13	2	Al	$ 5.50					Bob	1	$ 5.50				PayPal	0
14	3	Bob	$ 5.50					Cal	14	$ 68.00				Zelle	0
15	4	Cal	$ 4.75					Dan	6	$ 30.00				CashApp	0
16	5	Cal	$ 4.75					Ed	5	$ 26.00
17	6	Cal	$ 4.75							$ -
18	7	Cal	$ 4.75
19	8	Cal	$ 4.75
20	9	Cal	$ 4.75
21	10	Cal	$ 4.75
22	11	Cal	$ 4.75
23	12	Cal	$ 4.75
24	13	Cal	$ 4.75
25	14	Cal	$ 5.00
26	15	Cal	$ 5.00
27	16	Cal	$ 5.00
28	17	Cal	$ 5.50
29	18	Dan	$ 5.00
30	19	Dan	$ 5.00
31	20	Dan	$ 5.00
32	21	Dan	$ 5.00
33	22	Dan	$ 5.00
34	23	Dan	$ 5.00
35	24	Ed	$ 5.00
36	25	Ed	$ 5.00
37	26	Ed	$ 5.00
38	27	Ed	$ 5.50
39	28	Ed	$ 5.50
40
Sheet2

Cell Formulas
Range	Formula
A12:A39	A12	=SEQUENCE(SUM(I12:I30))
B12:B40	B12	=LET(s,SUBTOTAL(9,OFFSET(I$11,0,0,SEQUENCE(COUNTA($H$11:$H$30)))),r,ROWS(B$12:B12),IF(r>SUM(I$12:I$30),"",INDEX($H$12:$H$30,MATCH(r-1,s))))
C12:C40	C12	=IFERROR(LET(a,VLOOKUP(B12,$H$12:$I$30,2),b,COUNTIF($B$12:$B12,B12),c,INT(a/10)*10,IFS(b<=c,$C$8,MOD(b,10)<=(INT(MOD(a,10)/3)*3),$C$7,1,$C$6)),"")
J12:J17	J12	=LET(n,I12,c,INT(n/10)*$C$8*10,m,MOD(n,10),d,INT(m/3)*$C$7*3,c+d+$C$6*MOD(m,3))
Dynamic array formulas.

The B formula can be simplified, but it uses functions my Excel doesn't have yet.

 

[Tom and Rons Pools]

Is this what you're looking for?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last 3 Comps:		600	445	525		Spots	1	2	3	4	5	6	7	8
2	Average:		523.3333				Price/p	530	265	176.5	132.5	106	88.5	75.5	66.5
3	RoundUp w/extras		530
4
5		# of Spots:	100
6		$ p/spot:	$ 5.50
7		$ p/spot (3 spots)	$ 5.00
8		$ p/spot (10 spots)	$ 4.75
9
10
11	# of Spots	Name	$/Spot					Name	Spots	Total	Paid			Source	Amt.
12	1	Al	$ 5.50					Al	2	$ 11.00				Venmo	0
13	2	Al	$ 5.50					Bob	1	$ 5.50				PayPal	0
14	3	Bob	$ 5.50					Cal	14	$ 68.00				Zelle	0
15	4	Cal	$ 4.75					Dan	6	$ 30.00				CashApp	0
16	5	Cal	$ 4.75					Ed	5	$ 26.00
17	6	Cal	$ 4.75							$ -
18	7	Cal	$ 4.75
19	8	Cal	$ 4.75
20	9	Cal	$ 4.75
21	10	Cal	$ 4.75
22	11	Cal	$ 4.75
23	12	Cal	$ 4.75
24	13	Cal	$ 4.75
25	14	Cal	$ 5.00
26	15	Cal	$ 5.00
27	16	Cal	$ 5.00
28	17	Cal	$ 5.50
29	18	Dan	$ 5.00
30	19	Dan	$ 5.00
31	20	Dan	$ 5.00
32	21	Dan	$ 5.00
33	22	Dan	$ 5.00
34	23	Dan	$ 5.00
35	24	Ed	$ 5.00
36	25	Ed	$ 5.00
37	26	Ed	$ 5.00
38	27	Ed	$ 5.50
39	28	Ed	$ 5.50
40
Sheet2

Cell Formulas
Range	Formula
A12:A39	A12	=SEQUENCE(SUM(I12:I30))
B12:B40	B12	=LET(s,SUBTOTAL(9,OFFSET(I$11,0,0,SEQUENCE(COUNTA($H$11:$H$30)))),r,ROWS(B$12:B12),IF(r>SUM(I$12:I$30),"",INDEX($H$12:$H$30,MATCH(r-1,s))))
C12:C40	C12	=IFERROR(LET(a,VLOOKUP(B12,$H$12:$I$30,2),b,COUNTIF($B$12:$B12,B12),c,INT(a/10)*10,IFS(b<=c,$C$8,MOD(b,10)<=(INT(MOD(a,10)/3)*3),$C$7,1,$C$6)),"")
J12:J17	J12	=LET(n,I12,c,INT(n/10)*$C$8*10,m,MOD(n,10),d,INT(m/3)*$C$7*3,c+d+$C$6*MOD(m,3))
Dynamic array formulas.

The B formula can be simplified, but it uses functions my Excel doesn't have yet.

So that works if the person wants whats called "straight fill" is there a way i could do ... Bob wants spot 7 and 21 , Caleb wants 11, 25, 28, 31, 32, 33, 35, 60, 65, 72 ... etc.

 

[Eric W]

As you can imagine, that would be a tougher proposition. I'll think about it a bit more, but I'm not sure that's possible with formulas. What I'd suggest is to change the H:K table to something like:

Book1
	H	I	J	K
19	Name	Spots	Total	Paid
20	Al	2,4,6,8
21	Bob	9-20
22	Cal	21-30,32,35,37
23	Dan	38-45,88,5-10
24	Ed	1,3,5
Sheet2

This allows you to put in multiple ranges per individual. Instead of formulas in columns B, C, and J, I'd create a macro that triggers when you put something in column I. First thing it does is to ensure that there aren't any overlaps with previous buyers. If not, it fills in the cost in column J, then puts the names in column B, and the unit price in column C. Let me know if that's of interest.