What VBA function returns cell 2 from which cell 1 gets its value?
 

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto( Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

What VBA function returns cell 2 from which cell 1 gets its value?
 

On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke
wrote:

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto (Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron

What VBA function returns cell 2 from which cell 1 gets its value?
 

On Wed, 24 Mar 2010 08:13:22 -0400, Ron Rosenfeld
wrote:


***

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron

Never Mind. That doesn't work

--ron

What VBA function returns cell 2 from which cell 1 gets its value?
 

If your formula is as simple as you show (only one range reference), then
the answer is probably as simple as this...

.ShowPrecedents
Set Cel2= Cel1.NavigateArrow(False, 1, 1)
.ShowPrecedents True

If you have other range references in your formula, and especially if those
references are for multiple sheets, then the code gets more complicated as
the 2nd and 3rd arguments have to account for them.

--
Rick (MVP - Excel)



"Jim Luedke" wrote in message
...
This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto( Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

What VBA function returns cell 2 from which cell 1 gets its va
 

Cel1.Precedents.Select

will activate a range which is the union of cells appearing in the formula
(Excel 2007).

If you have trace arrows showing on your sheet then NavigateArrow will allow
you to select a particular one to follow.



"Ron Rosenfeld" wrote:

On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke
wrote:

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto (Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron
.

What VBA function returns cell 2 from which cell 1 gets its va
 

As far as I know, that will only work for Precedent cells on the same
worksheet as the cell (Cel1 in this case)... Precedents will not work on
cells referencing other sheets. The only way I know to do that is how I
showed in my post... show the precedent arrows, then use NavigateArrow to
find the cell on the foreign sheet that you want and the turn the arrows
off. The code for this can get complicated if there are several cells on
other sheets, especially if there are multiple other sheets involved.

--
Rick (MVP - Excel)


"pbart" wrote in message
...
Cel1.Precedents.Select

will activate a range which is the union of cells appearing in the formula
(Excel 2007).

If you have trace arrows showing on your sheet then NavigateArrow will
allow
you to select a particular one to follow.



"Ron Rosenfeld" wrote:

On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke
wrote:

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto (Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron
.

What VBA function returns cell 2 from which cell 1 gets its value?
 

On Wed, 24 Mar 2010 12:56:19 -0400, "Rick Rothstein"
wrote:

If your formula is as simple as you show (only one range reference), then
the answer is probably as simple as this...

.ShowPrecedents
Set Cel2= Cel1.NavigateArrow(False, 1, 1)
.ShowPrecedents True

If you have other range references in your formula, and especially if those
references are for multiple sheets, then the code gets more complicated as
the 2nd and 3rd arguments have to account for them.

--
Rick (MVP - Excel)

Rick,

1. I think the argument in the second line should be True.
2. Will this work in a function?
--ron

What VBA function returns cell 2 from which cell 1 gets its va
 

Rick

You are right; my apologies!

A different strand of thinking could be to cheat and record the address of
predecessor cell (SalesSheet!$A$1) as a string on ProfitSheet at the time the
data is set up; the string could be used in the formula
"=MassageData(INDIRECT(Cel3)) much as one would a database 'foreign key'. I
would only suggest this as a potential solution if it fits well into the
spirit of the workbook in question.



"Rick Rothstein" wrote:

As far as I know, that will only work for Precedent cells on the same
worksheet as the cell (Cel1 in this case)... Precedents will not work on
cells referencing other sheets. The only way I know to do that is how I
showed in my post... show the precedent arrows, then use NavigateArrow to
find the cell on the foreign sheet that you want and the turn the arrows
off. The code for this can get complicated if there are several cells on
other sheets, especially if there are multiple other sheets involved.

--
Rick (MVP - Excel)


"pbart" wrote in message
...
Cel1.Precedents.Select

will activate a range which is the union of cells appearing in the formula
(Excel 2007).

If you have trace arrows showing on your sheet then NavigateArrow will
allow
you to select a particular one to follow.



"Ron Rosenfeld" wrote:

On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke
wrote:

This is a simple and possibly embarrassing question.

In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
a different sheet.

Cell 1 has a UDF, so it looks like:

"=MassageData(SalesSheet!$A$1)"

Given that I know cell 1 at runtime, what VBA function returns cell 2?

I have tried:

Set Cel2 = Cel1.Precedents(1)
Set Cel2 = Cel1.Precedents.Cells(1, 1)
etc.

but that only seems to return Cel1 itself (at least that's what the
Debug Window shows).

1) Is my syntax wrong?

2) Is Excel's lack of external dependent/precedent functionality in my
old version, the reason?

3) If so, has that un-feature ever been fixed?

I guess I could manually remove the UDF and do:

Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))

or maybe this sickness (if I have the syntax right):

Set Cel2 =
Application.WorksheetFunction.INDIRECT(StripDitto (Cel1.Formula))

But what's the simple way that's staring me in the face?

Thanks much.

***

For the address, perhaps:

rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address

--ron
.

.