Want to put a value into ONE cell and watch it populate an entire column on another sheet

[Robertluckett]

Hello,

I've tried posting my problem in various ways, but I haven't been able to find anyone who could help me. I want the value of a cell, let's say A1 to populate a column in descending order (ie. B1, B2, B3, etc.) everytime the value changes or is updated. I would like this to happen automatically meaning no need to press any buttons or press F9.

To further explain my situation; I have a PLC (programmable logic controller) that I have successfully populated certain data into excel. The tag values update immediately, as they are being called from the program. I just want the data to populate as I described above. PLEASE HELP. I have looked all over and can't find anything to help me with this issue.

So, let's start with something a little easier. Is it possible to populate a column with data entered into A1? So when I enter the number '1' into A1 it populates
B1 on another sheet. Then when I put 2 into A1 it populates B2, 3 into A1 it populates B3, etc.

[6StringJazzer]

The most straightforward way to do this is with VBA (macros). It could happen automatically but you have to enable macros to run.

Another possibility is the judicious use of circular references in formulas in column B of the second sheet. I have only done that a couple of times and so would have to experiment to make sure it fits your exact situation.

One thing missing from your description is: If you put a 2 into A1, what data do you want to go into B2?

[Robertluckett]

Hi Jazzer thanks for the reply.

I would want 2 to go into B2

For A1 I'd enter the number 1 that would put 1 into B1. I put 2 into A1 that would put 2 into B2 so on and so forth.

What I'm wondering is how would you write that Macro. I come from a C base background so I tried

Dim n as int
If ($A$1 = some value, $B$n = some value, "")
If ($B$n = some value, n++, "")
end If
end If

I have never attempted to write code for excel until just recently. Could use some help here.

[6StringJazzer]

Have a look at the attached. VBA is much different than C. Not only is the syntax different, but the driving concepts behind the two languages are also much different. Good discussion over a beer. We can also throw in C++, Ada, and Java into the comparison while we're at it.

VBA has events triggered by the application (remember the A in VBA stands for Applications). I have written an event handler for the Change event triggered when there is a change in the worksheet. You have to rethink your whole approach with VBA.

I sprinkled in some comments but let me know if you have questions.

[Robertluckett]

What an awesome forum! Thanks Jazzer for all your help. You've really shined a light on this whole situation!