Linking to referenced cell automatically

**rhua5436** · 08-05-2009, 11:34 PM

Hi Everyone!
I would like to know how I can automatically link a cell to its referenced cell.
For example... from my attached file.

A1 - A5 are all referenced to cells somewhere else. I need a script/method to automatically create a link linking those cells to their referenced cells.
So that A1 would be referenced and linked to E1.

Please help me. Greatly appreciated!
Thank you!

**JBeaucaire** · 08-06-2009, 12:57 AM

I'm confused, they're already linked.

**rhua5436** · 08-06-2009, 01:13 AM

oh, sorry, i linked them manually.
That's the problem.
I have spreadsheets with a lot of references, and they all need to be linked...
i would like to have an automated way, would save me a lot of grunt work.

Thank you

**JBeaucaire** · 08-06-2009, 01:47 AM

Your "sample" sheet needs to not be dumbed down to the point we cant' offer a usable suggestion.

I imagine this will be a macro then, yes? Post up one of your real workbooks, or a reasonably sufficient excerpt to fully represent everything a macro would have to get right.

**rhua5436** · 08-06-2009, 02:05 AM

This is a scorecard that I am in charge of producing.
It is just one of many similar ones as we have many servicelines.

On the SSO tab, you would notice that only the financial's section has figures in it. I deleted the other tabs (kpi,compliance etc) because the spreadsheet initially is at least 2 Mb.

Most of the cells on the scorecard are referenced to the other tabs in the spreadsheet (eg. sso financial). and i manually linked them all.

Yes, i would like a macro that would
1.recognise a cell thats being reference on the scorecard.
2.link that cell to the data source, which is in other tabs of the spreadsheet.

Thank you very much for your help

**JBeaucaire** · 08-06-2009, 02:24 AM

The choatic nature of the design is going to make simplification of formulas very difficult. In this sample, I've eliminated all the duplicate Jan-Dec values on the SSO Finance sheet and created a standard table format where the values like "Utilization" (sp?) is on the same row as the pertinent data.

Also Jan-Dec are actually date values now that match the values you've used on the SSO sheet.

Once those changes were made, you could change the direct links on the SSO sheet at cell C15 with a standard INDEX(Range,Match(value),Match(Date)) format.

=INDEX('SSO Finance'!$R:$AC,MATCH(SSO!$B15,'SSO Finance'!$Q:$Q,0),MATCH(SSO!C$5,'SSO Finance'!$R$12:$AC$12,0))

You can simply copy that to the right and it grabs the correct values without having to rewrite the formulas for each cell. This is the benefit of organized TABLE formats.

But the random design is far too complex to solve all your problems this way without you starting over with a great deal of the layout. This is just to give you an understanding of what you would need to do and what you could do once you had.