• Log InLog In
  • Register
Liquid`
Team Liquid Liquipedia
EDT 02:00
CEST 08:00
KST 15:00
  • 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
Code S RO8 Preview: herO, Zoun, Bunny, Classic3Code S RO8 Preview: Rogue, GuMiho, Solar, Maru3BGE Stara Zagora 2025: Info & Preview27Code S RO12 Preview: GuMiho, Bunny, SHIN, ByuN3The Memories We Share - Facing the Final(?) GSL47
Community News
BGE Stara Zagora 2025 - Replay Pack2Weekly Cups (June 2-8): herO doubles down1[BSL20] ProLeague: Bracket Stage & Dates9GSL Ro4 and Finals moved to Sunday June 15th13Weekly Cups (May 27-June 1): ByuN goes back-to-back0
StarCraft 2
General
Jim claims he and Firefly were involved in match-fixing Code S RO8 Preview: herO, Zoun, Bunny, Classic The SCII GOAT: A statistical Evaluation DreamHack Dallas 2025 - Official Replay Pack BGE Stara Zagora 2025 - Replay Pack
Tourneys
[GSL 2025] Code S:Season 2 - RO8 - Group A RSL: Revival, a new crowdfunded tournament series SOOPer7s Showmatches 2025 Sparkling Tuna Cup - Weekly Open Tournament Sea Duckling Open (Global, Bronze-Diamond)
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
BW General Discussion BGH auto balance -> http://bghmmr.eu/ FlaSh Witnesses SCV Pull Off the Impossible vs Shu StarCraft & BroodWar Campaign Speedrun Quest Will foreigners ever be able to challenge Koreans?
Tourneys
[ASL19] Grand Finals NA Team League 6/8/2025 [BSL20] GosuLeague RO16 - Tue & Wed 20:00+CET [Megathread] Daily Proleagues
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
Nintendo Switch Thread Stormgate/Frost Giant Megathread Beyond All Reason Path of Exile 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
US Politics Mega-thread Things Aren’t Peaceful in Palestine 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 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: 28008 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 3h 30m
[ Submit Event ]
Live Streams
Refresh
StarCraft 2
Nina 319
Livibee 85
trigger 43
StarCraft: Brood War
Rain 2908
actioN 645
Leta 217
Snow 208
ggaemo 130
PianO 62
yabsab 29
Dota 2
XBOCT459
League of Legends
JimRising 756
Counter-Strike
Stewie2K1184
Heroes of the Storm
Khaldor143
Other Games
summit1g7866
C9.Mang0910
WinterStarcraft354
Mew2King220
Maynarde144
Organizations
Other Games
gamesdonequick734
Dota 2
PGL Dota 2 - Secondary Stream273
StarCraft 2
Blizzard YouTube
StarCraft: Brood War
BSLTrovo
sctven
[ Show 15 non-featured ]
StarCraft 2
• intothetv
• AfreecaTV YouTube
• Kozan
• IndyKCrew
• LaughNgamezSOOP
• Migwel
• sooper7s
StarCraft: Brood War
• BSLYoutube
• STPLYoutube
• ZZZeroYoutube
League of Legends
• Doublelift4815
• Lourlo1127
• Rush1033
• Stunt347
• HappyZerGling83
Upcoming Events
GSL Code S
3h 30m
herO vs Zoun
Classic vs Bunny
The PondCast
4h
OSC
15h
Replay Cast
18h
OSC
18h
WardiTV Invitational
1d 5h
OSC
1d 7h
Korean StarCraft League
1d 21h
SOOP
2 days
sOs vs Percival
CranKy Ducklings
2 days
[ Show More ]
WardiTV Invitational
2 days
Cheesadelphia
2 days
CSO Cup
2 days
GSL Code S
3 days
Sparkling Tuna Cup
3 days
Replay Cast
3 days
Wardi Open
4 days
Replay Cast
4 days
Replay Cast
5 days
RSL Revival
5 days
Cure vs Percival
ByuN vs Spirit
RSL Revival
6 days
herO vs sOs
Zoun vs Clem
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
BLAST Open Spring 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.