Linking to a cell after complex sort

[fatdave42]

Hi.

I have a master sheet that I put a list of people attend a camp on. I then do a sort by section and then name. I have a hyper link to another sheet in the same workbook that will have the information for the person on. I would like to get thinks like the name cell etc across on the personal sheet. I would normal use =Numbers!B5 but after a complex sort this gives the information for someone else.

How can I reference a cell that moves after the sort?

Thanks

 

[Chrisdontm]

Probably the OFFSET formula is your answer.

It has five parts to it, and it basically " defines a range ".

So the range you want may be one cell, that is ok.

With OFFSET you could delete rows and it will not be affected.

The way it works is five parts;

1st - A cell to start in.
2nd - how many rows down or up.
3rd - How many columns over.
Then you will be at the first cell of the range...
4th - How high or tall do you want the range.
5th - How wide do you want the range.

To make it dynamic, you put formulas in each or some of those sections.
So it depends what the answer to the formulas are as to how many rows down you go, or columns across.