Dynamic Range of Values from Formula

**excel328** · 07-16-2010, 06:41 PM

Dynamic Range of Values from Formula

ex.
a b c
1 2 2
2 4 4
3 6 6

b is a column that uses a formula to determine a*2
c is a column that presents only the values (from paste special values)

Is there a way for column c to update automatically every time column b produces new entries in its column?
-------------------------------------------------------------
The reason I want to do this is because I wish to copy the values from a formula in a table and organize the new plain values alphabetically in another table.

**JBeaucaire** · 07-16-2010, 07:23 PM

No, a formula cannot create a flat value in a cell like Paste Special > Values can. A macro can do that.

But if you're going to go with a macro, then there's no longer any need to have a column B and a column C. You can have column C create itself every time there's a change in a value in column A. Is that something you could work with? Not everyone can work with macros.

**excel328** · 07-17-2010, 02:35 PM

I tried to record a simple macro to do the automated pasting but I was wondering if there might be more automated way of doing things such as detecting the values from the middle table and sort alphabetically the pasted values on the right side without user key presses.

**foxguy** · 07-18-2010, 02:07 PM

Hi excel328;

As JB indicated, you don't need column B.
If Column A is manually changed or is changed by a macro then put this in Column A sheet's module. (Right Mouse Click the tab and select "View Code")
Note: If Column A is a formula, it will not trigger this. You will need to put something in the sheet's module that actually is changed manually or by a macro.

Please Login or Register  to view this content.

If you really need it to automatically update Column C whenever anything changes then put this in Column A Sheet's module
Note: this will execute every time the sheet calculates, even if it has nothing to do with Column A

Please Login or Register  to view this content.

**excel328** · 07-18-2010, 04:22 PM

foxguy, I use your second code but was unable to see changes. Your saying that I should press alt+f11 and insert the code into the Column A Sheet's module? Also it seems like I was getting an error with the first code.

**foxguy** · 07-18-2010, 06:15 PM

Hi excel328;

There are several modules in Visual Basic Editor (VBE). Different modules are used for different things. Alt+F11 opens the VBE but doesn't put you directly into a sheet's module (which is the only place a sheet looks for Event subs
This opens the sheet's module directly.

Right Mouse Click the tab and select "View Code"

I just noticed that I gave you the wrong code. Put this code in the sheet's module. It gets triggered whenever any cell on the sheet is changed manually or by a macro. (The code I accidentally gave you last time gets triggered anytime the cursor changes cells, but if you weren't moving the cursor into Column A, it did nothing). This one will recalculate Column C whenever Column A is changed.
To repeat; if Column A is a formula then this does not trigger.

Please Login or Register  to view this content.

**excel328** · 07-18-2010, 08:25 PM

Thanks for the help. I only use the second code to do the updates along with my recorded macro. I didn't use

Please Login or Register  to view this content.

because the line If Not Intersect(Target, .Range("A:A") Is Nothing Then was highlighted in red .

**foxguy** · 07-18-2010, 08:35 PM

Hi excel328;

The Worksheet_Calculate sub does execute a lot more than is necessary, but if it doesn't slow down your working, it's OK.

I don't know why the line triggered an error. I'm sure it's something simple, but I'm missing it.