• Log InLog In
  • Register
Liquid`
Team Liquid Liquipedia
EDT 01:13
CEST 07:13
KST 14:13
  • Home
  • Forum
  • Calendar
  • Streams
  • Liquipedia
  • Features
  • Store
  • EPT
  • TL+
  • StarCraft 2
  • Brood War
  • Smash
  • Heroes
  • Counter-Strike
  • Overwatch
  • Liquibet
  • Fantasy StarCraft
  • TLPD
  • StarCraft 2
  • Brood War
  • Blogs
Forum Sidebar
Events/Features
News
Featured News
TL Team Map Contest #5: Presented by Monster Energy2Code S RO8 Preview: herO, Zoun, Bunny, Classic7Code S RO8 Preview: Rogue, GuMiho, Solar, Maru3BGE Stara Zagora 2025: Info & Preview27Code S RO12 Preview: GuMiho, Bunny, SHIN, ByuN3
Community News
Code S RO8 Results + RO4 Bracket (2025 Season 2)4BGE Stara Zagora 2025 - Replay Pack2Weekly Cups (June 2-8): herO doubles down1[BSL20] ProLeague: Bracket Stage & Dates9GSL Ro4 and Finals moved to Sunday June 15th13
StarCraft 2
General
The SCII GOAT: A statistical Evaluation Code S RO8 Results + RO4 Bracket (2025 Season 2) How herO can make history in the Code S S2 finals TL Team Map Contest #5: Presented by Monster Energy Jim claims he and Firefly were involved in match-fixing
Tourneys
[GSL 2025] Code S: Season 2 - Ro8 - Group A [GSL 2025] Code S: Season 2 - Ro8 - Group B RSL: Revival, a new crowdfunded tournament series SOOPer7s Showmatches 2025 Sparkling Tuna Cup - Weekly Open Tournament
Strategy
[G] Darkgrid Layout Simple Questions Simple Answers [G] PvT Cheese: 13 Gate Proxy Robo
Custom Maps
[UMS] Zillion Zerglings
External Content
Mutation # 477 Slow and Steady Mutation # 476 Charnel House Mutation # 475 Hard Target Mutation # 474 Futile Resistance
Brood War
General
BGH auto balance -> http://bghmmr.eu/ BW General Discussion FlaSh Witnesses SCV Pull Off the Impossible vs Shu StarCraft & BroodWar Campaign Speedrun Quest Will foreigners ever be able to challenge Koreans?
Tourneys
[BSL20] ProLeague Bracket Stage - Day 4 [BSL20] ProLeague Bracket Stage - Day 3 [Megathread] Daily Proleagues [ASL19] Grand Finals
Strategy
I am doing this better than progamers do. [G] How to get started on ladder as a new Z player
Other Games
General Games
Stormgate/Frost Giant Megathread Path of Exile Nintendo Switch Thread Beyond All Reason What do you want from future RTS games?
Dota 2
Official 'what is Dota anymore' discussion
League of Legends
Who’s Getting the Effortless-Chic Look Just Right?
Heroes of the Storm
Simple Questions, Simple Answers Heroes of the Storm 2.0
Hearthstone
Heroes of StarCraft mini-set
TL Mafia
TL Mafia Community Thread Vanilla Mini Mafia
Community
General
Things Aren’t Peaceful in Palestine US Politics Mega-thread UK Politics Mega-thread Russo-Ukrainian War Thread Vape Nation Thread
Fan Clubs
Maru Fan Club Serral Fan Club
Media & Entertainment
Korean Music Discussion [Manga] One Piece
Sports
TeamLiquid Health and Fitness Initiative For 2023 2024 - 2025 Football Thread Formula 1 Discussion NHL Playoffs 2024
World Cup 2022
Tech Support
Computer Build, Upgrade & Buying Resource Thread
TL Community
The Automated Ban List
Blogs
A Better Routine For Progame…
TrAiDoS
StarCraft improvement
iopq
Heero Yuy & the Tax…
KrillinFromwales
I was completely wrong ab…
jameswatts
Need Your Help/Advice
Glider
Trip to the Zoo
micronesia
Customize Sidebar...

Website Feedback

Closed Threads



Active: 27605 users

Excel Help

Blogs > Fzero
Post a Reply
Fzero
Profile Blog Joined October 2007
United States1503 Posts
Last Edited: 2009-02-06 23:39:15
February 06 2009 23:25 GMT
#1
Does anyone in here have an extensive knowledge of Excel? I'm trying to build a database and I'm having a little trouble.

My eventual goal for one of the pages is to create a system that does the following steps:

1. Contains a list of Players, Fantasy Costs, and Scoring. (Complete)
2. Contains a formula to generate random numbers based on the number of players. (Complete)
3. Takes the random number generated, points to that particular Cell in the spreadsheet, and returns the value of that cell... ie: Say I random 16, I want to return the value of Cell C16. (Complete)
4. Compile a function that has a maximum cost associated to it which generates M players that cost no more than N. (Incomplete)
5. Generate the total score of that random team. (Incomplete)

Just some hints in the right direction would be amazing, thanks.

Never give up on something that you can't go a day without thinking about.
JeeJee
Profile Blog Joined July 2003
Canada5652 Posts
February 06 2009 23:42 GMT
#2
So.. what's the problem? i was about to put an answer to your first "incomplete" (3) but i see you've edited your post a few minutes later to 'complete' instead.

fwiw i would've used indirect()

excel is one of those things where you just tinker and figure stuff out (although the last 2 steps are just simple math formulas now)
(\o/)  If you want it, you find a way. Otherwise you find excuses. No exceptions.
 /_\   aka Shinbi (requesting a name change since 27/05/09 ☺)
Fzero
Profile Blog Joined October 2007
United States1503 Posts
February 06 2009 23:50 GMT
#3
Yeah, I got help on IRC about Indirect. Thanks anyways.. kind of a useless post now. Hope to finish this up sometime tomorrow for another massive MSL thread.
Never give up on something that you can't go a day without thinking about.
Chill
Profile Blog Joined January 2005
Calgary25977 Posts
February 06 2009 23:56 GMT
#4
If you link the Excel file and are a little more specific I can definitely help. I've spent a lot of time on reasonably complex Excel functions and macros.
Moderator
Fzero
Profile Blog Joined October 2007
United States1503 Posts
February 07 2009 00:08 GMT
#5
I can't post the spreadsheet right now, but here's what I'm working on..

Cells A1-A32 = Player Names
Cells B1-B32 = Player Cost
Cells C1-C32 = Amount Scored
Cells F1-F5 =Randbetween(1,32)
Cells G1-G5 =INDIRECT("B"&F1-F5)
Cells H1-H5 =INDIRECT("A"&F1-F5)
Cells i1-i5 =INDIRECT("C"&F1-F5)

I'm generating a list so far that has the following components:

Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score

All these returned components are based on 5 randomly generated numbers which assign me to one of the 32 players in the list.

My next function must sum the cost of the players, if the cost is less than or equal to 10, then I need to print the 5 player's names and scores to 5 new cells on Sheet 2, A1-A5, if not - nothing
The next time I run the function I need the names and scores to be printed on A6-A10, etc.

Then I'm going to sort sheet 2 based on the highest sum of the scores in column B.

I want to eliminate any duplicate teams on Sheet 2.

None of this is especially difficult, I just couldn't find that damn INDIRECT function. If you still want to help and save me some time learning functions.. feel free. It's more time to spend on the other thing I'm working on.
Never give up on something that you can't go a day without thinking about.
omninmo
Profile Blog Joined April 2008
2349 Posts
February 07 2009 01:19 GMT
#6
Have you worked with V-LOOKUP?
GHOSTCLAW
Profile Blog Joined February 2008
United States17042 Posts
February 07 2009 01:56 GMT
#7
On February 07 2009 10:19 Kunty wrote:
Have you worked with V-LOOKUP?


this is easily the most powerful function that i've worked with. However, it's really tough to troubleshoot, and it can get super frustrating. If you do this you need to figure out how to name ranges, and it's sometimes easier to learn how to write your own macros
PhotographerLiquipedia. Drop me a pm if you've got questions/need help.
Chill
Profile Blog Joined January 2005
Calgary25977 Posts
February 07 2009 02:09 GMT
#8
Maybe I'm stupid but I can't imagine it without seeing the spreadsheet ;_;
Moderator
Fzero
Profile Blog Joined October 2007
United States1503 Posts
February 07 2009 03:33 GMT
#9
I'm thinking my idea is easier to do in C++ I'm exploring those options at the moment. I can design a decent GUI in VB and code it up.

Had to leave for a while, the US Navy wants to hire me to go to sea for 5 months a year. WTF. -_-
Never give up on something that you can't go a day without thinking about.
dyodyo
Profile Blog Joined December 2005
Philippines578 Posts
February 07 2009 09:19 GMT
#10
If you just want to find a random team costing no more than N, you should look into "Goal Seek" and "Scenario Manager". They are under the "Data" tab in the Excel2007 Ribbon.
TeamLiquid CJ Entusman #26
Please log in or register to reply.
Live Events Refresh
OSC
00:00
OSC Elite Rising Star #15
TBD vs ArTLIVE!
Liquipedia
Replay Cast
00:00
uThermal 2v2 Circuit: May
Liquipedia
OSC
21:00
Mid Season Playoffs
ArT vs ReBellioN
HonMonO vs Ziomek
Shameless vs LunaSea
MilkiCow vs GgMaChine
Moja vs HiGhDrA
Jumy vs TBD
Demi vs NightPhoenix
Solar vs Cham
Liquipedia
[ Submit Event ]
Live Streams
Refresh
StarCraft 2
Nina 276
Livibee 137
EnDerr 21
StarCraft: Brood War
Leta 331
JulyZerg 229
ToSsGirL 37
Dota 2
monkeys_forever474
Counter-Strike
Stewie2K1145
Other Games
summit1g10044
C9.Mang01429
shahzam1201
ViBE224
Organizations
Dota 2
PGL Dota 2 - Secondary Stream3580
StarCraft 2
Blizzard YouTube
StarCraft: Brood War
BSLTrovo
sctven
[ Show 18 non-featured ]
StarCraft 2
• Berry_CruncH306
• Light_VIP 124
• Hupsaiya 74
• practicex 37
• IndyKCrew
• AfreecaTV YouTube
• sooper7s
• intothetv
• Kozan
• LaughNgamezSOOP
• Migwel
StarCraft: Brood War
• RayReign 92
• STPLYoutube
• ZZZeroYoutube
• BSLYoutube
League of Legends
• Doublelift5228
• Stunt346
Other Games
• Scarra1601
Upcoming Events
WardiTV Invitational
5h 47m
HiGhDrA vs Nicoract
MaNa vs HiGhDrA
HiGhDrA vs Reynor
Nicoract vs Reynor
MaNa vs Nicoract
MaNa vs Reynor
MaxPax vs Spirit
Krystianer vs Spirit
OSC
7h 47m
BSL 2v2 ProLeague S3
13h 47m
Korean StarCraft League
21h 47m
SOOP
1d 3h
sOs vs Percival
CranKy Ducklings
1d 4h
WardiTV Invitational
1d 5h
Cheesadelphia
1d 9h
CSO Cup
1d 11h
BSL: ProLeague
1d 12h
Hawk vs UltrA
Sziky vs spx
TerrOr vs JDConan
[ Show More ]
GSL Code S
2 days
Rogue vs herO
Classic vs GuMiho
Sparkling Tuna Cup
2 days
BSL: ProLeague
2 days
Bonyth vs Dewalt
Cross vs Doodle
MadiNho vs Dragon
Replay Cast
2 days
Wardi Open
3 days
Replay Cast
3 days
Replay Cast
4 days
RSL Revival
4 days
Cure vs Percival
ByuN vs Spirit
RSL Revival
5 days
herO vs sOs
Zoun vs Clem
Replay Cast
5 days
The PondCast
6 days
RSL Revival
6 days
Serral vs SHIN
Solar vs Cham
Replay Cast
6 days
Liquipedia Results

Completed

CSL Season 17: Qualifier 2
BGE Stara Zagora 2025
Heroes 10 EU

Ongoing

JPL Season 2
BSL 2v2 Season 3
BSL Season 20
KCM Race Survival 2025 Season 2
NPSL S3
Rose Open S1
CSL 17: 2025 SUMMER
2025 GSL S2
BLAST.tv Austin Major 2025
ESL Impact League Season 7
IEM Dallas 2025
PGL Astana 2025
Asian Champions League '25
BLAST Rivals Spring 2025
MESA Nomadic Masters
CCT Season 2 Global Finals
IEM Melbourne 2025
YaLLa Compass Qatar 2025
PGL Bucharest 2025

Upcoming

Copa Latinoamericana 4
CSLPRO Last Chance 2025
CSLPRO Chat StarLAN 3
K-Championship
SEL Season 2 Championship
Esports World Cup 2025
HSC XXVII
Championship of Russia 2025
Murky Cup #2
Esports World Cup 2025
BLAST Bounty Fall 2025
BLAST Bounty Fall Qual
IEM Cologne 2025
FISSURE Playground #1
TLPD

1. ByuN
2. TY
3. Dark
4. Solar
5. Stats
6. Nerchio
7. sOs
8. soO
9. INnoVation
10. Elazer
1. Rain
2. Flash
3. EffOrt
4. Last
5. Bisu
6. Soulkey
7. Mini
8. Sharp
Sidebar Settings...

Advertising | Privacy Policy | Terms Of Use | Contact Us

Original banner artwork: Jim Warren
The contents of this webpage are copyright © 2025 TLnet. All Rights Reserved.