I’m trying to work on a GoogleDocs Excel type spreadsheet and am a little stuck with a calculation. (It does seem to be able to do most of the things that Excel can do, but not everything.)
I’m trying to have one sheet for each month of the year and another “main” sheet.
Each monthly sheet pulls the users from the main sheet, and shows the points that each user has accrued for the month. The method that points are added to the sheet is by pasting the result of a script – but as sometimes there are new users from one month to the next, and some users aren’t on the subsequent month, this isn’t likely to be in the same order as the previous month/s…
What I’m looking to do is have the ‘main’ sheet review the other sheets and add all of their points into there, so that they can see where they stand in the group.
Here’s an excerpt of the headers from the ‘main’ sheet, in case it helps:
Username | Rank | Points
I might also want to sort out a %age change from one month to the next, but I know how to do that already…
I was thinking about doing a DSUM type thing, but considering that there are hundreds of users this doesn’t seem like it would work without a lot of manual adjustments – and then I was thinking about the VLOOKUP formula instead, but IDK how to put that…
Any help gratefully accepted..!
As you have the data in the order Name … Nickname, VLOOKUP will not work for returning a Name for a given Nickname.
VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.
You could try a combination of Match and Offset
=OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1)
Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.
If there is no match, then you get an #NA error, so use this:
=IF(ISNA(MATCH(B7,Sheet1!$C$2:$C$61,0)),”No match”,OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1))
Regards
Hi,
As you have the data in the order Name … Nickname, VLOOKUP will not work for returning a Name for a given Nickname.
VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.
You could try a combination of Match and Offset
=OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1)
Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.
If there is no match, then you get an #NA error, so use this:
=IF(ISNA(MATCH(B7,Sheet1!$C$2:$C$61,0)),”No match”,OFFSET(Sheet1!$C$1,MATCH(B7,Sheet1!$C$2:$C$61,0),-1))
Regards