PDA

View Full Version : Break each external links in a cell and replace with the current value of each link



maderlach
01-29-2014, 02:46 AM
Hi all,

I am new to this forum but I find it very useful. Thank everybody for contribution!

I have a problem I cannot tackle - I have to break links in cell but after that I need to have all amounts presented in cell.

An example:

In cell A1 I have: = '\\LINK\ [FILE NAMES.xlsb] SheetName'!$C$7 + '\\LINK\ [FILE NAMES.xlsb] SheetName'!$C$8 + '\\LINK\ [FILE NAMES.xlsb] SheetName'!$C$9.
I need a macro that would show me (instead of links): = 5+10+27.

So basically it is a break link function but conducted on the separate links. Is that possible?

Kind regards,
Tom

Bob Phillips
01-29-2014, 02:53 AM
This is a bit long-winded but works. In the formula bar, select each of the separate portions individually ('\\LINK\ [FILE NAMES.xlsb] SheetName'!$C$7) such as and hit F9, this will evaluate that part. Move onto the next part, repeat, and so on, until done.

maderlach
01-29-2014, 02:56 AM
Hi, thanks it works - it would be good if there are only few links - but I have dozens of them.. So maybe there is a macro for this? :)

Bob Phillips
01-29-2014, 03:14 AM
I can give you a macro, but is the conjoin charcater always + (no - or *)? Where is the data, column, rows?

maderlach
01-29-2014, 03:23 AM
hmm there might be + / - or *.. but still with the code from you I could create a few Cases to include "-" and "*"....