How to reference a formula in another cell (not the result)

[johnalexander]

let's say cell A1 contains a long formula which ends as follows:

....'C:\DATA\qfe\[D&P Deductible Adjustment.xls]D&P Deductible Adjustment'!T$4:T$17)

I want cell b1 to return the bottom row used in the reference (in this case, 17). This is a check for me to make sure that my ranges referenced are far down enough.
Anyone?

 

[PaddyD]

Re: How to reference a formula in another cell (not the resu

this could be done, but I doubt its the best solution to your problem. for example, rather tan manually check if your references are 'far down enough', it would be better to construct them so you can be sure they always refer tothe range(s) of interest.

post back with more about what you are trying to achieve...

 

[johnalexander]

Re: How to reference a formula in another cell (not the resu

Thanks. I know I could do as you suggest, using counts and the indirect function, but in this case, the source file is not open (indirect doesn't work very well linking to closed files). Unless you have a clever other way that doesn't sacrifice any other efficience (calc speed, etc.)

 

[PaddyD]

Re: How to reference a formula in another cell (not the resu

...using counts and the indirect function...

There is no need to 'check' if your ranges are down far enough if you have constructed them 'properly' - ie as dynamic named ranges that automatically expand / collapse as the data grows / shrinks. search the board for 'dynamic named ranges' for a few techniques.

post back with more details about what you are trying to achieve if needed

 

[Mark W.]

Re: How to reference a formula in another cell (not the resu

let's say cell A1 contains a long formula which ends as follows:

....'C:\DATA\qfe\[D&P Deductible Adjustment.xls]D&P Deductible Adjustment'!T$4:T$17)

I want cell b1 to return the bottom row used in the reference (in this case, 17). This is a check for me to make sure that my ranges referenced are far down enough.
Anyone?

Why wouldn't the ranges be "far down enough"? Why not just click on cell A1 and see what cells are included?

 

[johnalexander]

Re: How to reference a formula in another cell (not the resu

Paddy D: I chose not to use dynamic ranges, as I feel they do not work very well on externally linked files (indirect fn doesn't, but if you know one that does...)

Mark W: clicking on the cell and "looking" at the formula is what I do now. The whole intent is to extract the bottom row reference, so that I can compare it to a "count" of the rows of the linked file (count fn DOES work well on externally linked files-unlike the indirect fn). This way I could have a flag when these are not in sync (i.e.-someone adds more data to the linked file and forgets to tell me)

 

[PaddyD]

Re: How to reference a formula in another cell (not the resu

clearly you have not, as suggested, searched the board for direction re dynamic named ranges. indirect() is not required.

 

[Aladin Akyurek]

Re: How to reference a formula in another cell (not the resu

Not sure but, just out of curiosity, how about...

In Cell enter:

=FORMULATEXT(formulaCell)

=ROW(INDIRECT(SUBSTITUTE(MID(Cell,SEARCH(":",Cell)+1,LEN(Cell)),")","")))

FORMULATEXT requires the morefunc.xll add-in.