• Log InLog In
  • Register
Liquid`
Team Liquid Liquipedia
EDT 20:34
CEST 02:34
KST 09:34
  • 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
Firefly suspended by EWC, replaced by Lancer1Classic & 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 Pack2
StarCraft 2
General
Firefly suspended by EWC, replaced by Lancer 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 WardiTV Mondays Sparkling Tuna Cup - Weekly Open Tournament $3,500 WardiTV European League 2025 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 # 478 Instant Karma Mutation # 477 Slow and Steady Mutation # 476 Charnel House Mutation # 475 Hard Target
Brood War
General
ASL20 Preliminary Maps BW General Discussion BGH Auto Balance -> http://bghmmr.eu/ 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
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
Vanilla Mini Mafia TL Mafia Community Thread
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
Formula 1 Discussion 2024 - 2025 Football Thread 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: 33526 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
Replay Cast
00:00
2025 GSL S2 - Ro8 Group A
CranKy Ducklings121
EnkiAlexander 55
Liquipedia
[ Submit Event ]
Live Streams
Refresh
StarCraft: Brood War
Artosis 910
ajuk12(nOOB) 12
Icarus 4
Dota 2
LuMiX1
Counter-Strike
fl0m6051
Heroes of the Storm
Khaldor190
Other Games
summit1g9578
FrodaN3546
shahzam1187
C9.Mang0999
JimRising 501
ViBE231
Maynarde183
Mew2King58
Trikslyr47
Organizations
Other Games
gamesdonequick1119
StarCraft 2
Blizzard YouTube
StarCraft: Brood War
BSLTrovo
sctven
[ Show 15 non-featured ]
StarCraft 2
• Berry_CruncH285
• Hupsaiya 103
• AfreecaTV YouTube
• intothetv
• Kozan
• IndyKCrew
• LaughNgamezSOOP
• Migwel
• sooper7s
StarCraft: Brood War
• Azhi_Dahaki15
• BSLYoutube
• STPLYoutube
• ZZZeroYoutube
Dota 2
• Ler60
League of Legends
• Doublelift6228
Upcoming Events
Wardi Open
10h 26m
Replay Cast
23h 26m
Replay Cast
1d 9h
RSL Revival
1d 9h
Cure vs Percival
ByuN vs Spirit
RSL Revival
2 days
herO vs sOs
Zoun vs Clem
Replay Cast
2 days
The PondCast
3 days
RSL Revival
3 days
Serral vs SHIN
Solar vs Cham
Replay Cast
3 days
RSL Revival
4 days
Reynor vs Scarlett
ShoWTimE vs Classic
[ Show More ]
uThermal 2v2 Circuit
4 days
SC Evo League
5 days
Circuito Brasileiro de…
5 days
Sparkling Tuna Cup
6 days
Liquipedia Results

Completed

Proleague 2025-06-11
2025 GSL S2
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
Copa Latinoamericana 4
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

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.