How to follow a cell from a linked cell

**Milkman00** · 03-07-2014, 04:50 PM

I have two sheets.

On sheet one (named Master) I have:

Apples
Cantaloupes
Bananas

On sheet two (named Customer) I have the following formulas

='Master'!A1
='Master'!A2
='Master'!A3

Now, if I install a filter in the Master sheet, and sort alphabetically the link formulas on sheet two remain the same, and because of that, in sheet two they would be in alphabetical order as well. BUT what if I wanted the links on sheet two to follow the actual cell in the master sheet, so that even after a sort, the links on sheet 2 would show the same values, so the formulas on sheet two would end up looking like this (DESPITE being sorted on the master sheet)

='Master'!A1
='Master'!A3
='Master'!A2

Basically I would want the linked cells on sheet 2 to represent a static value no matter where it was moved to on the static sheet.

Hope this makes sense, and thanks in advance!

**Ron Coderre** · 03-07-2014, 04:58 PM

This will do that:

Please Login or Register  to view this content.

Is that something you can work with?

**Milkman00** · 03-07-2014, 05:17 PM

That didn't seem to work. It gave me the same value as just posting a link like ='Master'!A3

So using the index command it showed me the same thing in the same order as on the master page

Apple
Bananas
Cantaloupes

But AFTER SORTING the master page through a filter, I still wanted sheet two to show me

Apple
Cantaloupes
Bananas

and I wanted the link formulas to look like this automatically:

='Master'!A1
='Master'!A3
='Master'!A2

Basically I want sheet two to know that on row two I want you to say Cantaloupes no matter WHERE it moves on the master sheet.

**Milkman00** · 03-11-2014, 09:05 PM

Please look at the attached sheet.

Forum Excel.xls

As you see I have a master sheet where all input is done, then I have a standard price sheet which is fine. Then I have a special customer price sheet. This particular customer ONLY EVER ORDERS these 5 items, and JUST these 5 items. Now if you go back to the master sheet and sort A -> Z, this changes the links on the special customer page because the link remains the same and doesn't follow the item.

Basically what I am looking to do is no matter where it moves on the master sheet, have the same item referenced here.

Hope that makes sense.