Getting data from a formula

  • Thread starter Thread starter m1918
  • Start date Start date
M

m1918

Is it possible to extract a value from within a formula?

For example:

Let's assume I have a formula in B3 which is "=a3*2". Is it possible to
get the value "3" off the formula in B3 and placed in C3? TIA.


A B C
5 =a3*2
 
It would depend on how complex the formula is and how much effort you wanted to
put into the code that would parse that formula.
 
Hi Dave,

The formula is actually not very complex. This (master) worksheet has
about 700 rows. From it, I would delete certain rows to create different
new sheets. What I need is the original row number before deletion. What
I have been doing so far is add a number corresponding row number into a
extra column. The problem arise whenever I have to add or delete rows
from the master sheet, then I'd have to manually correct the row
numbers. Since the relative row number in a formula will automatically
be adjusted when I make any changes, I was hoping it will pick up the
row number and adjust for me by itself. Are you more confused than
before? If you like, I could show you a sample of what I'm trying to do.
In any case, thank you.

George


Dave Peterson wrote:
n> It would depend on how complex the formula is and how much effort you
wanted to
 
Use =Row() instead of manually entering the row.

Hi Dave,

The formula is actually not very complex. This (master) worksheet has
about 700 rows. From it, I would delete certain rows to create different
new sheets. What I need is the original row number before deletion. What I
have been doing so far is add a number corresponding row number into a
extra column. The problem arise whenever I have to add or delete rows from
the master sheet, then I'd have to manually correct the row numbers. Since
the relative row number in a formula will automatically be adjusted when I
make any changes, I was hoping it will pick up the row number and adjust
for me by itself. Are you more confused than before? If you like, I could
show you a sample of what I'm trying to do.
In any case, thank you.

George


Dave Peterson wrote:
n> It would depend on how complex the formula is and how much effort you
wanted to
Click to expand...
 
I wouldn't do this to create new sheets.

In fact, I try my best to keep the data in one location. Then I can use
Autofilter or sort to show what I want.

But if I have to separate the data into different worksheets, I'll use a macro
to separate the data. Each time I need a fresh version of those individual
sheets, I'll just rerun the macro. My updates only go into the original sheets.

If you want to try this:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb
 
Back
Top