Hyperlink doesn't work - need a macro....

[BeSmart]

Hi al

I’m 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 “absolute 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’t 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 “go to†the current column’s cell within the defined area named “Totalsâ€

The “Totals†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 compan
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’s enormously appreciated by this novice

 

[PaulyT]

~x (How is that pronounced?)
I run a sales database that my people enter similar data to what yo
are describing.

Bypass the hyperlink problem all together. Put your formul
=sum(R4:R16000) at the top of your column where everyone can see it.
I would
1) insert a few rows at the top of your sheet.
2) Start your database at, say, at row 4,
3) Your filters can then be applied to the database and maybe even
macro atached to a button to access the Data Form command.

I can't really help you with the slow Calculation question short of
RAM or processor upgrade. Your suggestion of a Macro to manua
recalulate with an additional function =on.time(Now()+5 minutes) and
LOOP may do the trick. I can't really help with the code

 

[PaulyT]

~x (How is that pronounced?)
I run a sales database that my people enter similar data to what you
are describing.

Bypass the hyperlink problem all together. Put your formula
=sum(R4:R16000) at the top of your column where everyone can see it.
I would
1) insert a few rows at the top of your sheet.
2) Start your database at, say, at row 4,
3) Your filters can then be applied to the database and maybe even a
macro atached to a button to access the Data Form command.

I can't really help you with the slow Calculation question short of a
RAM or processor upgrade. Your suggestion of a Macro to manual
recalulate with an additional function =on.time(Now()+5 minutes) and a
LOOP may do the trick. I can't really help with the code.

 

[Patrick Molloy]

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.