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

johnalexander

Board Regular
Joined
Feb 26, 2003
Messages
50
:help:

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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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...
 
Upvote 0
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.)
 
Upvote 0
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
 
Upvote 0
Re: How to reference a formula in another cell (not the resu

johnalexander said:
:help:

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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,344
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top