Getting data from a formula

[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

 

[Dave Peterson]

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.

 

[m1918]

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

 

[ebloch]

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

 

[Dave Peterson]

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