try range namimg the total in column A as say 'Total'
Add a standard code module and this code:

Public Sub GoToTotal()
dim iCol as long
icol = selection.column
Range("Total").offset(0,iCol-1).Select
End Sub

On the spreadsheet place a button at the top, and assign
the GoTotal procedure ( macro) to the button.

If rows are inserted, the code will always get to the
total.

The code gets the required column from whatever cell is
selected, the goes Total cell offset by the column count
of the selection

eg "total" is A400
select G2 & click the button
the selected column is "G" or 7 so in the code iCol is 7
The code will select A400 offset 6 columns-- G400
ie A400 is cells(400,1)
using ther offset ... cells(400,1).offset(0,icol-1)
....cells(400,1).offset(0,7-1)
....cells(400,1).offset(0,6)...same as cells(400,1+6)
....cells(400,7) ... which is G400

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi all

I?Tm having problems with a hyperlink function I
thought would work easily.

I applied a hyperlink to a cell near the top of each
column (each column represents a day and the hyperlink is
on the date cell) that will take the user to the totals
cell for that column (which is 300+ cells below).

The problem is that users insert rows and the total cell
moves causing the hyperlink to go to the wrong cell.

I tried applying ?oabsolute references?� (ie $EJ$377)
to the hyperlink but excel wiped these and reverted it
back to a basic cell reference (EJ377).

I then thought of creating a defined area for the totals
and writing a macro to go to it, but I don?Tt want to
have separate defined areas for every column (the
spreadsheet covers 6 months+ of columns = 198 columns) -
it seems to increase the file size hugely having lots of
them (or is that something I'm doing that is wrong).

Can anyone suggest either a better way to do this or a
macro that will ?ogo to?� the current column?Ts cell
within the defined area named ?oTotals?�?

The ?oTotals?� defined area covers 198 columns and 21
rows.
_________________________________________________ ________
__________________
Also, the excel spreadsheet is constantly "calculating"
which holds up and annoys the users - is there anyway to
either speed up the calculations or decrease the number
of calculations it has to do without loosing immediate
results of changes?

Note: I could put it on "manual" calculation and create
a button for the users to click to "calculate now", but I
don't want to do this because they will forget to click
it and they will use wrong results.

If you need more detail of what's contained in the
workbook here it is:
2 worksheets:
- The activity sheet (198 columns, currently 1100 rows)
Users enter sale details on each row via Validation
lists, including supplier, company, details of sale,
status of sale, placement, size and a number is entered
in the date cell.
The sheet includes formulas to group supplier totals and
show their totals by column, plus placement by company
The sheet includes filters to view specific suppliers or
companies etc.

- The Totals sheet (198 columns, 140 rows)
100% formulas to summarize the activity sheet (ie show
totals by supplier split by company), plus hidden rows to
calculate placements against each supplier which are
shown on the spreadsheet as one row using a concatenate
formula to combine the number (ie (1/0/1)


Any help on these two issues would (as usual) be greatly
appreciated ?" I am constantly amazed at how much I am
and have learnt from this discussion group and how
helpful everyone is. It?Ts enormously appreciated by
this novice.


.