• Log InLog In
  • Register
Liquid`
Team Liquid Liquipedia
EDT 22:28
CEST 04:28
KST 11:28
  • 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 Energy4Code 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
Classic & herO RO8 interviews: "I think it’s time to teach [Rogue] a lesson."0Rogue & GuMiho RO8 interviews: "Lifting that trophy would be a testament to all I’ve had to overcome over the years and how far I’ve come on this journey.2Code S RO8 Results + RO4 Bracket (2025 Season 2)11BGE Stara Zagora 2025 - Replay Pack2Weekly Cups (June 2-8): herO doubles down1
StarCraft 2
General
Classic & herO RO8 interviews: "I think it’s time to teach [Rogue] a lesson." Rogue & GuMiho RO8 interviews: "Lifting that trophy would be a testament to all I’ve had to overcome over the years and how far I’ve come on this journey. The SCII GOAT: A statistical Evaluation Code S RO8 Results + RO4 Bracket (2025 Season 2) feardragon: Blizzards biggest blunder with SC was…
Tourneys
RSL: Revival, a new crowdfunded tournament series [GSL 2025] Code S: Season 2 - Ro8 - Group A [GSL 2025] Code S: Season 2 - Ro8 - Group B 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/ Recent recommended BW games BW General Discussion FlaSh Witnesses SCV Pull Off the Impossible vs Shu StarCraft & BroodWar Campaign Speedrun Quest
Tourneys
[Megathread] Daily Proleagues [BSL 2v2] ProLeague Season 3 - Friday 21:00 CET Small VOD Thread 2.0 [BSL20] ProLeague Bracket Stage - Day 4
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
Path of Exile Nintendo Switch Thread Stormgate/Frost Giant Megathread Beyond All Reason What do you want from future RTS games?
Dota 2
Official 'what is Dota anymore' discussion
League of Legends
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
2024 - 2025 Football Thread NHL Playoffs 2024 TeamLiquid Health and Fitness Initiative For 2023 Formula 1 Discussion
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: 27494 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
Next event in 33m
[ Submit Event ]
Live Streams
Refresh
StarCraft 2
PiGStarcraft497
RuFF_SC2 197
Nina 97
StarCraft: Brood War
Icarus 7
Dota 2
monkeys_forever646
LuMiX1
League of Legends
JimRising 566
Counter-Strike
Foxcn220
Super Smash Bros
amsayoshi44
Other Games
summit1g10179
C9.Mang01128
shahzam745
ViBE239
Trikslyr70
FunKaTv 14
Organizations
Dota 2
PGL Dota 2 - Secondary Stream1737
Other Games
gamesdonequick999
StarCraft 2
Blizzard YouTube
StarCraft: Brood War
BSLTrovo
sctven
[ Show 14 non-featured ]
StarCraft 2
• practicex 8
• gosughost_ 1
• AfreecaTV YouTube
• intothetv
• Kozan
• IndyKCrew
• LaughNgamezSOOP
• Migwel
• sooper7s
StarCraft: Brood War
• BSLYoutube
• STPLYoutube
• ZZZeroYoutube
League of Legends
• Doublelift5850
Other Games
• Scarra616
Upcoming Events
Korean StarCraft League
33m
SOOP
6h 33m
sOs vs Percival
CranKy Ducklings
7h 33m
WardiTV Invitational
8h 33m
ByuN vs MaNa
MaxPax vs Solar
Reynor vs Creator
Gerald vs Spirit
Cheesadelphia
12h 33m
CSO Cup
14h 33m
BSL: ProLeague
15h 33m
Hawk vs UltrA
Sziky vs spx
TerrOr vs JDConan
GSL Code S
1d 5h
Rogue vs herO
Classic vs GuMiho
Sparkling Tuna Cup
1d 7h
BSL: ProLeague
1d 15h
Bonyth vs Dewalt
Cross vs Doodle
MadiNho vs Dragon
[ Show More ]
Replay Cast
1d 21h
Wardi Open
2 days
Replay Cast
2 days
Replay Cast
3 days
RSL Revival
3 days
Cure vs Percival
ByuN vs Spirit
RSL Revival
4 days
herO vs sOs
Zoun vs Clem
Replay Cast
4 days
The PondCast
5 days
RSL Revival
5 days
Serral vs SHIN
Solar vs Cham
Replay Cast
5 days
RSL Revival
6 days
Reynor vs Scarlett
ShoWTimE vs Classic
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
Murky Cup #2
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
BLAST Open Fall 2025
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.