Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a spreadsheet with list of persons ("players"), and for a each person I have following values:

  • ID number (unique)
  • Name (just text)
  • Score from round 1
  • Score from round 2
  • ...
  • Score from round 16

Now, I have this other spreadsheet I want to use for displaying results. Each row has these columns:

  • ID number
  • Name (fetched using ID from the Players-sheet)
  • First Round Eligible
  • Last Round Eligible
  • Score (sum of all rounds that are between First Round and Last Round).

I have problems calculating the score. Example: I have ID "17", First Round "3" and Last Round "11". This means I need to to calculate sum of score through rounds 3 to 11 for player with ID 17. I can use vlookup to fecth the Name based on ID, but I don't know how to apply First & Last round to sum calculation.

Another example would be using the following chart to calculate how many points the driver X has gained between Grand Prixes Y and Z. http://en.wikipedia.org/wiki/2012_Formula_One_season#Drivers.27_standings

share|improve this question
    
Please, post some examples (images of the tables as you are describing). –  Oneide Aug 28 '12 at 2:40
    
... or just view-share an worksheet. –  Oneide Aug 29 '12 at 0:12
    
Could you give us some feedback please, whether the answers give were OK? –  Jacob Jan Tuinstra Jan 23 '13 at 20:33
    
@Habba: your on Stack Overflow, so could you look at the answers we've come up with? –  Jacob Jan Tuinstra Mar 1 '13 at 12:46
add comment

2 Answers

The function VLOOKUP is only usable within a spreadsheet. This means that you use sheets !! Therefore I created a sample, based on sheets.

If you want to use spreadsheets however, then study the IMPORT function or Google Apps Script.

Remarks to the example:

  1. I've created a table like the wiki page you included, with the calculated total. You can hide this sheet and lock it for updates, for anyone but you.
  2. Pay attention to sheet 2, cell A11.
  3. I've hidden rows 1 to 10 to make the autocomplete function to work.
  4. See B1 in sheet 2, how it's done.

File I prepared with working example

Adding a sample of you own, helps a great deal to better understand what you mean !!

UPDATE: I've added edit rights to the example file I created, so you can play with it. Changed the names as well, each now being different (makes the auto-complete function work)

share|improve this answer
add comment

I made small example with ID, Name, Rounds 1-4. Entered data with 3 names.

  • B8 is field to enter ID.
  • B9 name lookup: =VLOOKUP(B8,A2:B4, 2)
  • B10 field to enter first round
  • B11 field to enter last round

First I made helper column G2:G4 consisting =(B2=$B$9) (copy down from G2 to G4).

Another helper row C12:F12 consisting =AND(C1>=$B$10, C1<=$B$11) (copy right C12 -> F12)

And finally the score: =SUM(FILTER(FILTER(C2:F4,G2:G4),C12:F12))

I believe there is a way to calculate without helper columns, but that's quick and dirty solution. :o)

share|improve this answer
    
If anyone interested, link o my example docs.google.com/spreadsheet/… –  Zmogas Sep 6 '12 at 11:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.