Harlan,
Thanks for you reply, I've tried you suggestion, in a slightly different
format, and it works a treat. I've used
'=the962|IQVALUE!'L14O011 in B1
S in B2 (Letters change down this column)
1 in C1 (numbers increase along this row upto 240)
(V)' in A2 (Change down this colomn to)
Then using =$B$1&$B$2&C$1&$A$2 I cna just drag across and the jobs done. A
smal alteration for the next letter down Col B and I'm off again.
You've unwittingly solved another problem I knew was coming, but hadn't
solved yet which was having a very large 2D array with 'Live' DDE links as
the 3rd party sofware struggles with more than about 200 active links and by
the time I've assembled all the possiblities, it will be over a 1000! so by
stopping before the paste special part of you suggestion, problem solved.
I'm planning on a
VB solution to reference this table from another sheet
using the 'S1' value, split into 2 cells, then using OFFSET and 2 MATCH
functions find the correct string and copy and paste special (as your
suggestion) into the active sheet and finally run a macro for replacing the =
with = to make them live.
So thanks once again youv'e helped more than you realised, Ive just got to
figure out the
VB bit now *!?
"Harlan Grove" wrote:
Mark Dullingham wrote...
....
The liks look like this
=the962|IQVALUE!'L14O011S1(V)'
=the962|IQVALUE!'L14O011 this bit stays the same
But S1 will change and so will the text inside the brackets.
if I place =the962|IQVALUE!'L14O011 in one cell, S1 in another and
(V)' in another and use =CONCATENATE(Cell1, Cell2, Cell3) the = in
cell 1 makes it return "Cell1" in the CONCATENATE result.
Is there a way around this?
DDE links can't be dynamic. If they wouldn't change (at least not
frequently) once created, an easy way to generate them as a batch
involves using formulas to create text strings that look like the
final DDE links, converting those formulas to values (as text
strings), then converting those text strings to DDE link formulas.
If you put either
="=the962|IQVALUE!'L14O011"
or
'=the962|IQVALUE!'L14O011
in cell A1 (note that the 1st ' in the second possibility is a label
prefix character that will force Excel to treat the rest of the cell
contents as a text string), S1 in cell A2 and (V)' in cell B1, enter
the formula
=$A$1&$A2&B$1
in cell B2. This should result in the text string
=the962|IQVALUE!'L14O011S1(V)'
in B2. Run the menu commands Edit Copy followed by Edit Paste
Special, as value. That should convert the formula to a text constant.
Then run the menu command Edit Replace, finding = and replacing it
with = (yes, replace the equal sign with itself). That'll effectively
enter the text constant as a formula, so the cell should then return
the DDE link's value.
If you had several other values like S1, enter them in column A below
cell A2, and if you had several other values like (V)', enter them in
row 1 to the right of cell B1. Fill the cell B2 formula down and right
to match up with the column A and row 1 entries. Then run the Edit
Copy, Edit Paste Special and Edit Replace menu commands on the
entire range of such formulas.
Finally, don't use CONCATENATE. Use the & operator. There may be no
difference in results, but there's a lot less typing using &, it's not
subject to any limitations on the number of arguments, and it doesn't
incur a nested function call level.