Game Points Bank Account

[jgthtrotih]

Hi All,

Excel Workbook
	R	S	T	U	V	W	X
5	Your Points	Points Needed To Level Up	Current Level	Your Credit	Withdrawal	Deposit	Credit
6
7
8	0	You Need 18,000 Pts.	Intermediate	Your Credit is 0 Pts
9	11,500	6,500	Beginner	Your Credit is 0 Pts	2500	4000
10	10,454	7,546	Beginner	Your Credit is 454 Pts			454
11	12500	5,500	Beginner	Your Credit is 1000 Pts	2000	3500	1000
Sheet6

i have a "Game Points Bank Account" that keeps track of the points users earn or spend in the game...what i need help is creating a running total for the bank..ive tried everything nothing works..

(this is actually like a traditional bank account)

each player starts with 10,000 points

*Row 8 is the only row to have formulas*

*Rows 9-11 are typed in to show my desired results*

Here is the point value "Ranking System" for Column T

Excel Workbook
	AG	AH
5	Tier 1
6	Beginner	18000
7	Beginner	36000
8	Beginner	54000
9	Intermediate	72000
10	Intermediate	90000
11	Intermediate	108000
12
13	Tier 2
14	Advanced	126000
15	Advanced	144000
16	Advanced	162000
17	Expert	180000
18	Expert	198000
19	Expert	216000
20
21	Tier 3
22	Elite	234000
23	Elite	252000
24	Elite	270000
25	Superior	288000
26	Superior	306000
27	Superior	325000
Sheet6

Please ask for further questions!

Thanks!

 

[snowblizz]

Another step forward. "T" column.

And a question. Column "S", is the idea that it counts off to the next numeric level, or from Beginner to Intermediate etc?

Excel Workbook
	T	AA	AB
5		0	Beginner
6		18000	Beginner
7		36000	Beginner
8	Intermediate	54000	Beginner
9	Beginner	72000	Intermediate
10	Beginner	90000	Intermediate
11	Intermediate	108000	Intermediate
12	Beginner	126000	Advanced
13	Beginner	144000	Advanced
14	Beginner	162000	Advanced
15	Beginner	180000	Expert
16		198000	Expert
17		216000	Expert
18		234000	Elite
19		252000	Elite
20		270000	Elite
21		288000	Superior
22		306000	Superior
23		325000	Superior
Sheet1

Excel 2007

Cell Formulas
Range	Formula
T8	=VLOOKUP(R8,$AA$5:$AB$23,2,TRUE)
T9	=VLOOKUP(R9,$AA$5:$AB$23,2,TRUE)
T10	=VLOOKUP(R10,$AA$5:$AB$23,2,TRUE)
T11	=VLOOKUP(R11,$AA$5:$AB$23,2,TRUE)
T12	=VLOOKUP(R12,$AA$5:$AB$23,2,TRUE)
T13	=VLOOKUP(R13,$AA$5:$AB$23,2,TRUE)
T14	=VLOOKUP(R14,$AA$5:$AB$23,2,TRUE)
T15	=VLOOKUP(R15,$AA$5:$AB$23,2,TRUE)

 

[jgthtrotih]

Snow,

I'm getting N/A error?

Probably should mention i'm using Excel 2003

Thanks!

 

[snowblizz]

Snow,

I'm getting N/A error?

Probably should mention i'm using Excel 2003

Thanks!

Mmm...

Make sure that you place the data in the appropriate place. Or change so it matches. The data is expected to be in AA5:AB23.

It should work the same in '03, though I happen to be on '07 ATM.
Also column S, counting towards next point level or just Beginner, Intermediate etc?

 

[jgthtrotih]

Snow,

i didnt have the 0 value i started at 18,000

but i added it now that works. perfect!

step 3?

Thanks!

 

[snowblizz]

I think I might have cracked it. I'm feeling oddly smug and satisfied.

Excel Workbook
	R	S	T	U
8	72001	You need 17999 Pts.	Intermediate	Your Credit is 0 Pts
9	10000	You need 8000 Pts.	Beginner	Your Credit is 0 Pts
10	18000	You Have Enough Pts. Pts.	Beginner	Your Credit is 0 Pts
11	72000	You Have Enough Pts. Pts.	Intermediate	Your Credit is 0 Pts
Sheet1

Excel 2007

Cell Formulas
Range	Formula
S8	=IF(R8<=VLOOKUP(R8,$AA$5:$AB$23,1),"You Have Enough Pts.","You need " & INDEX($AA$5:$AB$23,MATCH(R8,$AA$5:$AA$23,1)+1,1)-R8) & " Pts."
S9	=IF(R9<=VLOOKUP(R9,$AA$5:$AB$23,1),"You Have Enough Pts.","You need " & INDEX($AA$5:$AB$23,MATCH(R9,$AA$5:$AA$23,1)+1,1)-R9) & " Pts."
S10	=IF(R10<=VLOOKUP(R10,$AA$5:$AB$23,1),"You Have Enough Pts.","You need " & INDEX($AA$5:$AB$23,MATCH(R10,$AA$5:$AA$23,1)+1,1)-R10) & " Pts."
S11	=IF(R11<=VLOOKUP(R11,$AA$5:$AB$23,1),"You Have Enough Pts.","You need " & INDEX($AA$5:$AB$23,MATCH(R11,$AA$5:$AA$23,1)+1,1)-R11) & " Pts."
T8	=VLOOKUP(R8,$AA$5:$AB$23,2,TRUE)
T9	=VLOOKUP(R9,$AA$5:$AB$23,2,TRUE)
T10	=VLOOKUP(R10,$AA$5:$AB$23,2,TRUE)
T11	=VLOOKUP(R11,$AA$5:$AB$23,2,TRUE)
U8	=IF(X8>$Q$23,"You Are Credited With "&TEXT(X8-$Q$23,"#,##0")&" Pts","Your Credit is 0 Pts")
U9	=IF(X9>$Q$23,"You Are Credited With "&TEXT(X9-$Q$23,"#,##0")&" Pts","Your Credit is 0 Pts")
U10	=IF(X10>$Q$23,"You Are Credited With "&TEXT(X10-$Q$23,"#,##0")&" Pts","Your Credit is 0 Pts")
U11	=IF(X11>$Q$23,"You Are Credited With "&TEXT(X11-$Q$23,"#,##0")&" Pts","Your Credit is 0 Pts")

Also use the following in the sheet code (slightly modified from before)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 22
Target.Offset(0, -4).Value = Target.Offset(0, -4).Value - Target.Value
Case 23
Target.Offset(0, -5).Value = Target.Offset(0, -5).Value + Target.Value
Case 24
Target.Offset(0, -6).Value = Target.Offset(0, -6).Value + Target.Value
Case Else
Exit Sub
End Select
End Sub

There's a reference to Q23 that I don't know where it comes from which have left in the cells in column U.

 

[jgthtrotih]

Snow,

Everything is working so far

i changed the "code" what does it do now?

Q23= 10,000 the default start value

Thanks!

 

[snowblizz]

Snow,

Everything is working so far

i changed the "code" what does it do now?

Q23= 10,000 the default start value

Thanks!

I added a check so it won't crash if you modify several cells at once in the "edit area". I noticed that if I deleted several values in V,W,X it would crash. As deleting values is "changing them to nothing" it would still run the update code just that adding or subtracting 0 won't show anything.
As we are looking at column numbers apparently it gives a faulty value if you have several selected simultaneously.
It shouldn't be possible to put values simultaneously in several cell though so it should be safe that way.

 

[jgthtrotih]

Snow,

oh o ok.

when Column R is blank or has a value of "0"...how do i get Column S to be blank?

Than when they have excatly the amount ie 18,000 than it displays "You Have Enough Pts. Pts" how to take off the extra "Pts."

Other than that i think im set to go..all give it another once over.

Thanks!

 

[snowblizz]

This should fix both issues. There was a ")" that was placed incorrectly so ALL strings were amended with "Pts." and not just the ones with number. I added an if check for "0" on R, so it won't run S if R is 0. Of course this assumes that you aren't getting people who when playing, or whatever, who hit negatives. Is this something that needs to be considered as well? I think the S column will error if values become negative because the VLOOKUP requires the value to be present in the table. That is the reason for 0 being the first value and not 18000.

Excel Workbook
	S
8	You Have Enough Pts.
Sheet1

Excel 2007

Cell Formulas
Range	Formula
S8	=IF(R8=0," ",IF(R8<=VLOOKUP(R8,$AA$5:$AB$23,1),"You Have Enough Pts.","You need " & INDEX($AA$5:$AB$23,MATCH(R8,$AA$5:$AA$23,1)+1,1)-R8 & " Pts."))

 

[jgthtrotih]

This should fix both issues. There was a ")" that was placed incorrectly so ALL strings were amended with "Pts." and not just the ones with number. I added an if check for "0" on R, so it won't run S if R is 0. Of course this assumes that you aren't getting people who when playing, or whatever, who hit negatives. Is this something that needs to be considered as well? I think the S column will error if values become negative because the VLOOKUP requires the value to be present in the table. That is the reason for 0 being the first value and not 18000.

The Negative values in column R is not a problem as there aren't any as of right now but might be in the future..so i think if it can be addressed..lets please address it. please

Thanks!