Custom functions...

[Tomas]

Is it possible to create a function that takes a value
from cell "A1" and returns the result to cell "A3" while
residing in cell "A2"?

or in other words...

is it possible for a function to send results to a
different cell (or cells) than the one the formula is in?

thanks,
Tomas

 

[Guest]

Hi

A formula cannot push values into other cells. You'll need VBA to do what
you want.

 

[Guest]

Ok... I'm creating my own custom formula in vba.... how
do you reference the cell "next to the formula cell" from
vba?

 

[JulieD]

to reference a cell one column to the right of the active cell you use
activecell.offset(0,1)
to reference a cell one column to the left of the active cell you use
activecell.offset(0,-1)
to reference a cell one row below the active cell you use
activecell.offset(1,0)
to reference a cell one row above active cell you use
activecell.offset(-1,0)
etc

Hope this helps

Cheers
JulieD

 

[Frank Kabel]

Hi
even a user defined function in VBA which you want to use
in a cell can't do this!

The only way would be to use a worksheet event like
worksheet_change. See:
http://www.cpearson.com/excel/differen.htm

 

[Don Guillett]

Have a look at OFFSET

 

[Tomas]

thanks... that's good to know... i was starting to doubt
myself

This is my problem then:

This is the result from a webquery on sheet1...
Sheet1:

A1: Joe, B1: Male
A2: Bill, B2: Male
A3: Sue, B3: Female
A4: Cindy, B4: Female
A5: Bob, B5: Male


Then let's say i want to see (in sheet2) all the rows
from sheet1 where gender = male.

Sheet2:
A1: Joe, B1: Male
A2: Bill, B2: Male
A3: Bob, B3: Male

This information needs to change whenever the results
from the webquery on sheet1 changes...

How do i do this?

regards,
Tomas

 

[Don Guillett]

Also look at the answers to your OTHER posts on the SAME thing with
different subject lines.