Please Help with offset formula.

[balois]

Ok I attached a screen capture of the sheet.

I am trying to take just The players name and cumalitive score to appear on another sheet. The first column starting with Brandon and score of 41 all the way down is fine using the formula;

=OFFSET(Sheet1!B1,(ROW()-1)*7,0)

The problem is I can't get it to work for the column starting G2.

I tried "=OFFSET(Sheet1!H1,(ROW()-1)*7,0) " to get the cumalitive score in H8 as I figured it would be the same as the other formula just changing the B1 to H1 but just not working.

Any help would be awesome. Thanks.

[AndyLitch]

Try =OFFSET(Sheet1!H1,(ROW()-1)*7,0,1,1)

[balois]

That didn't work, it returned a value of 0.

[AndyLitch]

It might possibly be your arithmetic... It returns 0 when it sees a blank cell... Try different values in H1

[balois]

The value is 15. All that I want to get is the Cumulative score value of 15. I can just type = and click that cell but I would have to do that 100 times and keep switching from Sheet 1 to Sheet 2.

[AndyLitch]

There is a problem with your offset formula.. Without the spreadsheet I'm working blind but try this instead

=OFFSET($H$1,(ROW(M1)*8)-1,0,1,1)

[Kevin UK]

Hi balois

Are you entering the formula in G2, works for me.

Please Login or Register  to view this content.

Result: 15;16;0;0

[balois]

No I am entering it in A140 of a different sheet. I basically have 275 entries so I have 138 scores in the column then I want to display the scores from the second column of players if that makes sense.

[Kevin UK]

Then may be:

=OFFSET(H1,(ROW()-139)*7,0)

[balois]

Ok this formula worked but now I am having trouble getting the players name.

=OFFSET(Sheet1!$H$1,(ROW(M1)*8)-1,0,1,1)

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[balois]

I guess I am not understanding the M1 part for the row. M1 is a blank cell on all sheets.

[balois]

Ok, I attached the actual workbook. I run a pool for the Masters where everyone selects 5 golfers. I am really inexperienced with excel but think I have done pretty good until this point. Breakdown of Sheets.

Sheet 1 - Players name and the 5 golfers they select.
Sheet 2 - Where I will enter the scores from the PGA players to appear on sheet 1
Sheet 3 - The list of PGA players I used to create drop down menu on sheet 1.
Sheet 4 (The sheet in question) - Cells A1-A139 are the cumulative scores from all players in the first column and B1 through B139 are going to be the players names. Cells A140-A260 are the players in column 2 cumulative scores but I can't get their names in Cells B140-B260.

Thank you all for your help.

[popipipo]

Please Login or Register  to view this content.

Try these 2 formulas

[balois]

Those formulas worked. Thank you so much.

[Kevin UK]

@ Pepe Le Mokko

Makes life a lot easier uploading a file, but for some reason the posters like pictures!
So you spend half the time trying to second guess, in this case they were sitting in row 140 trying to use "=OFFSET(Sheet1!H1,(ROW()-1)*7,0)"!
All they had to do was add the sheet to my post #9: =OFFSET(H1,(ROW()-139)*7,0)! No response though