Link one sheet to another

[mingali]

Hi,

I attached a file that show worksheet 'Report' and worksheet 'Pivot'. Data from 'Report' is based on 'Pivot'. i used direct linking ,ie = Pivot!A5. However, when i change something in 'Pivot', the linking will be in the wrong place.

May i know is there a better function to 'fix' the linking? FYI, in 'Report', cell A3 is the same as cell B5 in 'Pivot'. I am not sure whether the info is useful or not.

Thanks in advance

[mingali]

Hi,

Anyone out there can provide the solution? the sole "=" function required to amend everytime when a new line is added. It is not an efficient way to do the work.

Thanks

[Whizbang]

A combination of INDIRECT, ADDRESS and MATCH will do the trick.

Enter this formula into cell B12 of the Report sheet. Then just copy it down and across.

=INDIRECT("Pivot!" & ADDRESS(MATCH("*" & TRIM(Report!$A12) & "*",Pivot!$C:$C,0),MATCH("*" & TRIM(Report!B$8) & "*",Pivot!$4:$4,0)))


What this does is use MATCH to find the row and column numbers, then use ADDRESS to convert those numbers into a cell address. Then Indirect takes that address and pulls the value.

[mingali]

Thanks for the solution, Whizbang.

I wonder whether offset function can be used in this case because i am thinking in that direction but cant really figure out.

[Whizbang]

I'll have to think about the OFFSET thing. I've only used that function a few times.

Another option would be to do

=INDIRECT("Pivot!"&ADDRESS(CELL("Row",B12)-7,CELL("Col",B12)+2))

The difference between this and the last is that match is not used. Match gives you the ability to rearrange columns and rows in either sheet and still have the correct data display. If you don't expect to rearrange, then the formula above might be simmpler/quicker.