autoincrementing dde link

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large number of dde link address that I am inputting which need the
address to increment sequentially. I can't get Excel to increment the
address. And, I don't want to type them all in. I found if I remove the =
mark at the beginning of the dde link, Excel will increment the address when
I drag the cells. But, then I have to manually go back and replace the =
mark. I have about 3000 cells to enter on multiple pages! I need a faster
way!
 
Try this: In each dde link address cell except the first one, simply type in
the formula "=(ref) + 1", where (ref) is a cell reference to the previous
address. Now each cell with this formula will always be one greater than the
last one. You can just copy the formula into every cell you need.
 
CORRECTION: This actually returns the value of the preceding cell, +1. It
doesn't change the actual dde address referenced. For instance, my actual
data in two successive addresses is 2 and 5. Using the actual address in the
first cell I get a 2, but using the =(ref) +1 formula in the next cell
returns a 3, not the correct 5. Is there a way to have Excel evaluate the
previous formula, not the value of that formula?
 
I'm a little confused. So you have the addresses incrementing by different
amounts in places? Or is it just that one spot?
 
The formula is: "=kepdde|_ddedata!RS232.TunnelTron1.DW208". I want to
increment the "DW208" portion to "DW209" and on and on. Actually I am using
about 1200 various addresses. So, being about to increment them by dragging
is a really big deal. Right now, I have to remove the equals mark at the
begining, then the formula will increment. After that, I have to go down the
column and put the equal mark back at the begining.

Thanks for you help. I've been busy the past few days on another problem
and just got back to this one.

David Barbe
 
Oh, okay, now I understand what you're trying to do. I think you can use the
INDIRECT function along with some text functions to do what you want. The
only way I can think of, though, requires adding another parallel column.
So, in the new column (let's say it is column B) you would have just the
number part, which increments. Here would be the formula I described before,
so that the values you get are 208, 209, 210, etc... Then, in the column
with your references, you could have:
=INDIRECT(CONCATENATE("kepdde|_ddedata!RS232.TunnelTron1.DW",B##))
where B## is a reference to the number in that row in column B.
This should combine the first, constant part of that address with the
changing numerical part and then use the resulting text string as a
reference. Now, I've never seen a cell reference like the one you've got
there, but if it's been working then the INDIRECT function shouldn't have a
problem with it.
If you don't like the aesthetic tackiness of a whole column whose sole
purpose is for this, you can always hide it or make it's text color white.
I hope that works for you.
-Juskalux
 
Back
Top