Help ! a couple of newbie questions

[Kevin Smith]

Hi

I’m very new to excel. I’ve created a workbook that is being used to keep track of a small informal golf league. I’ve figured out how to calculate and keep an ongoing handicap (basically used the fill handle to keep the formula each time I add a new round).

Now, my stupid little questions:

1. My handicap scores are kept in column H10 through H15. Each time I enter a new round (for example in row 16), the handicap score in H16 is updated to reflect the new revised handicap. What I’d like to do is have the new handicap number show up in my heading area in cell E1. Eventually there will be quite a few rounds posted. That way, at a glance, you can see what your handicap is without having to scroll down to H15. How do I do this? I've tried to use the copy / paste special, but that didn't work...I know it's probably simple, but I don't have an excel book and am trying to teach myself using the help files (gotta get down to Barnes & Nobles!

2. I would also like to have this same cell, or perhaps an entire row of cells, transfer the new information from worksheet 1 to worksheet 2 automatically. (Basically, one worksheet per golfer with individual stats, and a separate worksheet within the workbook that will show a summary of selected items from the individual stat sheets). Is this possible

Thanks in advance! (hope it makes sense….

 

[Frank Kabel]

Hi Kevin
for your first issue use the following formula in E1:
=OFFSET($H$10,COUNTA($H$10:$H$9999)-1,0)

for the second issue you can use the same formula as above to get the
information from the last row. Put this on you second worksheet:
=OFFSET('Sheet1'!$H$10,COUNTA('Sheet1'!$H$10:$H$9999)-1,0)
for other columns just change the thirs parameter of the OFFSET
function. e.g.
=OFFSET('Sheet1'!$H$10,COUNTA('Sheet1'!$H$10:$H$9999)-1,-1) -> will
give you the information from column G on sheet 1 for the last row
=OFFSET('Sheet1'!$H$10,COUNTA('Sheet1'!$H$10:$H$9999)-1,+1) -> will
give you the information from column I on sheet 1 for the last row

HTH
Frank

 

[macropod]

Hi Kevin,

For your first problem, try:
=INDEX(H:H,MATCH(9.99999999999999E+307,H:H))
This will give you the last numeric value in column H, regardless of whether
there are empty cells or cells with text in that column.

Your description of your second problem has two parts. If you want to
duplicate Sheet1 as a one-off exercise, a simple copy & paste will do. But
if you want Sheet2 to update whenever Sheet1 changes, you could put
something like:
=Sheet1!A1
into A1 on Sheet2, then copy down/across as far as you need. Bear in mind,
though that empty cells on Sheet1 will show up as 0s on Sheet2 (you can use
zero suppression to fix this) and Sheet1's formatting won't be copied. Also,
any text wider than 1 column on Sheet1 will be truncated unless you clear
the adjacent cells on Sheet2.

Creating a summary sheet would, presumably, mean cross-referencing just one
or a few rows from each of the individual sheets, much like the =Sheet1!A1
example. Perhaps the simplest way to build the cross references is to select
a cell on the summary sheet that you want to have linked, then press the '='
sign and select the cell on the source sheet that you want to link it to.

Cheers

Hi,

I'm very new to excel. I've created a workbook that is being used to keep

track of a small informal golf league. I've figured out how to calculate
and keep an ongoing handicap (basically used the fill handle to keep the
formula each time I add a new round).

Now, my stupid little questions:

1. My handicap scores are kept in column H10 through H15. Each time I

enter a new round (for example in row 16), the handicap score in H16 is
updated to reflect the new revised handicap. What I'd like to do is have
the new handicap number show up in my heading area in cell E1. Eventually
there will be quite a few rounds posted. That way, at a glance, you can
see what your handicap is without having to scroll down to H15. How do I
do this? I've tried to use the copy / paste special, but that didn't
work...I know it's probably simple, but I don't have an excel book and am
trying to teach myself using the help files (gotta get down to Barnes &
Nobles!)

2. I would also like to have this same cell, or perhaps an entire row of

cells, transfer the new information from worksheet 1 to worksheet 2
automatically. (Basically, one worksheet per golfer with individual stats,
and a separate worksheet within the workbook that will show a summary of
selected items from the individual stat sheets). Is this possible?

 

[WYN]

HI KEVIN,FOR YOUR QUESTION #1 TRY IN CELL E1=H15
THE SECOND QUESTION WILL HAVE TO BE ANSWERED BY SOMEONE ELSE

HOPE THIS WORKS
REGARDS WY

----- Kevin Smith wrote: ----

Hi

I’m very new to excel. I’ve created a workbook that is being used to keep track of a small informal golf league. I’ve figured out how to calculate and keep an ongoing handicap (basically used the fill handle to keep the formula each time I add a new round).

Now, my stupid little questions:

1. My handicap scores are kept in column H10 through H15. Each time I enter a new round (for example in row 16), the handicap score in H16 is updated to reflect the new revised handicap. What I’d like to do is have the new handicap number show up in my heading area in cell E1. Eventually there will be quite a few rounds posted. That way, at a glance, you can see what your handicap is without having to scroll down to H15. How do I do this? I've tried to use the copy / paste special, but that didn't work...I know it's probably simple, but I don't have an excel book and am trying to teach myself using the help files (gotta get down to Barnes & Nobles!

2. I would also like to have this same cell, or perhaps an entire row of cells, transfer the new information from worksheet 1 to worksheet 2 automatically. (Basically, one worksheet per golfer with individual stats, and a separate worksheet within the workbook that will show a summary of selected items from the individual stat sheets). Is this possible

Thanks in advance! (hope it makes sense….

 

[Kevin S.]

Thanks everyone! I'll give them a try and see which solution works the best!

Appreciate the help!

Kevin