• Log InLog In
  • Register
Liquid`
Team Liquid Liquipedia
EDT 11:50
CEST 17:50
KST 00:50
  • 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 RO4 & Finals Preview: herO, Rogue, Classic, GuMiho0TL 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 & Preview27
Community News
Classic & herO RO8 Interviews: "I think it’s time to teach [Rogue] a lesson."2Rogue & 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.8Code S RO8 Results + RO4 Bracket (2025 Season 2)14BGE Stara Zagora 2025 - Replay Pack2Weekly Cups (June 2-8): herO doubles down1
StarCraft 2
General
Turning Real Purpose Into Real-World Brand Power Jim claims he and Firefly were involved in match-fixing How herO can make history in the Code S S2 finals 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. Code S RO8 Results + RO4 Bracket (2025 Season 2)
Tourneys
[GSL 2025] Code S: Season 2 - Semi Finals & Finals Sparkling Tuna Cup - Weekly Open Tournament $3,500 WardiTV European League 2025 Sea Duckling Open (Global, Bronze-Diamond) SOOPer7s Showmatches 2025
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/ ASL20 Preliminary Maps BW General Discussion Recent recommended BW games FlaSh Witnesses SCV Pull Off the Impossible vs Shu
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
Stormgate/Frost Giant Megathread Path of Exile Nintendo Switch Thread Beyond All Reason What do you want from future RTS games?
Dota 2
Best crypto recovery experts in the world 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
SKT1 Classic Fan Club! Maru Fan Club
Media & Entertainment
Korean Music Discussion [Manga] One Piece
Sports
2024 - 2025 Football Thread Formula 1 Discussion NHL Playoffs 2024 TeamLiquid Health and Fitness Initiative For 2023
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: 32423 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 10m
[ Submit Event ]
Live Streams
Refresh
StarCraft 2
Hui .330
trigger 76
StarCraft: Brood War
Britney 31374
Calm 9356
Rain 6339
Horang2 2161
EffOrt 1340
Stork 744
PianO 565
BeSt 289
actioN 218
sSak 105
[ Show more ]
Hyun 68
Sea.KH 67
Shinee 51
Movie 39
Terrorterran 25
Aegong 21
Killer 16
zelot 16
Noble 14
ivOry 3
Zeus 0
Dota 2
Gorgc5677
qojqva2617
Dendi2591
XcaliburYe289
LuMiX1
Counter-Strike
fl0m4583
Foxcn920
rGuardiaN77
Super Smash Bros
Mew2King138
Heroes of the Storm
Khaldor690
Other Games
tarik_tv9170
singsing1914
B2W.Neo1484
Beastyqt472
C9.Mang0368
Fuzer 192
KnowMe133
RotterdaM128
FrodaN24
Organizations
Dota 2
PGL Dota 2 - Main Stream13650
Other Games
EGCTV603
StarCraft 2
Blizzard YouTube
StarCraft: Brood War
BSLTrovo
sctven
[ Show 15 non-featured ]
StarCraft 2
• maralekos20
• AfreecaTV YouTube
• intothetv
• Kozan
• IndyKCrew
• LaughNgamezSOOP
• Migwel
• sooper7s
StarCraft: Brood War
• BSLYoutube
• STPLYoutube
• ZZZeroYoutube
Dota 2
• C_a_k_e 3194
• WagamamaTV536
• Ler90
League of Legends
• Jankos3174
Upcoming Events
WardiTV Qualifier
10m
BSL: ProLeague
2h 10m
Bonyth vs Dewalt
Cross vs Doodle
MadiNho vs Dragon
Replay Cast
8h 10m
Wardi Open
19h 10m
Replay Cast
1d 8h
Replay Cast
1d 18h
RSL Revival
1d 18h
Cure vs Percival
ByuN vs Spirit
RSL Revival
2 days
herO vs sOs
Zoun vs Clem
Replay Cast
3 days
The PondCast
3 days
[ Show More ]
RSL Revival
3 days
Serral vs SHIN
Solar vs Cham
Replay Cast
4 days
RSL Revival
4 days
Reynor vs Scarlett
ShoWTimE vs Classic
uThermal 2v2 Circuit
4 days
SC Evo League
5 days
Circuito Brasileiro de…
6 days
Sparkling Tuna Cup
6 days
Liquipedia Results

Completed

Proleague 2025-06-11
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
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.