Linking to Max result?

D

Duncan

Hi Guys,

I dont know where to start with this one! basically on a figures sheet
I am displaying a table with lines showing the highest amounts on
another sheet based on their category. (=MAX(data!AZ2:AZ65534))

now on this other sheet it has a line for each 'entry', on one of the
cells for each 'entry' are the numbers that I'm maxing to give a
highest figure.

Now what I would love to be able to do, is hyperlink that formula that
is showing the highest entry, so that when you click on it it takes to
the other sheet and shows you its answer (the entry with the highest
amount)

have i made any sense at all? is this possible?

Hoping someone can help

Duncan
 
P

Puppet_Sock

Duncan said:
Hi Guys,

I dont know where to start with this one! basically on a figures sheet
I am displaying a table with lines showing the highest amounts on
another sheet based on their category. (=MAX(data!AZ2:AZ65534))

now on this other sheet it has a line for each 'entry', on one of the
cells for each 'entry' are the numbers that I'm maxing to give a
highest figure.

Now what I would love to be able to do, is hyperlink that formula that
is showing the highest entry, so that when you click on it it takes to
the other sheet and shows you its answer (the entry with the highest
amount)

have i made any sense at all? is this possible?

Hoping someone can help
Click to expand...

So, you want to click on a cell that shows the max of a range,
and you want the selection to jump to the cell that has that value.

It strikes me that this would be annoying to most users, but
sure, it's possible. Probably you don't want to deal with handling
events. That would be even more annoying. "Hey! Why can't I
put my cursor on that cell? Fricken spreadsheet is busted.
Where's that Duncan, I'm gonna..." See, if you can't click on
the cell, you can't copy the value anywhere else.

What you want is a button that fires some VBA. So, it reads
the value in the cell that receives the maximum value. Then
it does a standard old Search for that value in the range of
cells that the max was taken from.

In order to learn how to put VBA behind a button, you will need
to do the reading on buttons and adding code. Basically though,
you can just show the correct toolbar, drag the button out, and
attach the code.

To learn how to do the search in VBA, the macro recorder is
your friend. Select Tools > Macro > Record New Macro,
then do the search steps for a value, then stop the recording.
Then look at the recorded results in the Macro editor.

Then you want to think about some things. For example,
the maximum might not be unique. You might only see
the first one.What do you want to do about that?
Try some examples: Say the max is 12, and that shows
up in rows 5000, 6000, and 7000. What now?

If the selection pops to some line thousands of rows down
the sheet, your user may want to get back to the button.
So now you are into interface design and will need to think
about all sorts of things like frozen sections, navigation, etc.
Socks
 
D

Duncan

Thank you Socks,

I was kinda hoping I could do it without VBA as this spreadsheet is so
critical I dont want to fill it with code and rely on users enabling
macros.

I think that excel should allow for this though, it seems like it might
be a task that would be required by many users if they knew it existed,
its like linking to a summary of the result.

Anyway, I'll explore vba for the answer and see how simple it could be.

Many thanks

Duncan
 
P

Puppet_Sock

Duncan said:
I was kinda hoping I could do it without VBA as this spreadsheet is so
critical I dont want to fill it with code and rely on users enabling
macros.
Click to expand...

This is about clicking and getting the selection to jump to the
cell a max from a range comes from.

The problem is, changing selection in response to clicking is
only going to happen with VBA.
I think that excel should allow for this though, it seems like it might
be a task that would be required by many users if they knew it existed,
its like linking to a summary of the result.
Click to expand...

Sure. It does allow for it. Through VBA.

On the other hand, if your users want to see "where did that come
from" kind of things, then maybe what you really want is to adjust
your design a bit. I think I twittered on about that in my previous
post. What you want to try to do is discover from your users what
it is they want to accomplish, then find the "best" way to get them
where they want to go.

What it *seems* like to me with this is, you've got an incomplete
picture of what your users want to do. So you've settled
on a "computer space" description of the requirements. What
you really need is a "user space" description of the requirement.
Always try to solve your problem in "user space" first.

Like so: Maybe what they really need is to see the context of
the maximum value. (Or values, as I mentioned previously.)
They may only need to see the record with the max in it.
To do that, you have way other choices, like updating an
offset or something. Then you could display the record with
the max in it right beside the max. Or you could just display
the record(s) with the max in it(them) at some designated
spot on a worksheet, and so not have any popping to the
max value record at all.

Or maybe what you need is that the max value records get
highlighted. Or sorted to the top of the list. Or something
else depending on what your users really need.

Ah me. I've been doing nothing but QA for several months
now. I ache to do some real design work. So I get fancy.
Socks
 
D

Duncan

I have managed to solve this in other ways now, I spoke to the users
and they felt that some highlighting at input stage would alert them to
the max, this solves the problem of putting the horse after the cart
and catching the mistakes afterwards on a figures sheet.

I used some simple conditional formatting to show 'alerts' where is
exceeds the max, I suppose I should have thought of this at design
stage but im a bit like that!
Sure. It does allow for it. Through VBA.
Click to expand...

(I meant excel allowing for it without having to code, I dont mind
coding that much, im learning vba code all the time now that I've
started to see its benefits and what it can do but in this instance i
would have preferred to avoid it)

Many thanks for your help anyway

Duncan
 
Top