Need to detect a value from within a formula in another cell

[mibrahim]

using =BDS(..."cols=6;rows=69") as my formula, I would like to pull just the number 69 and enter it into another cell. This number, 69, is automatically populated within the formula. To clarify, the number, 69, is not the resulting output in the cell... it's part of the formula.

Also, this formula obviously returns 69 rows, I wouldn't be averse to some sort of count formula to count the output (which would result in the value of 69)... I'm just trying to avoid highlighting everything below in the column, since I'm not certain of the output length.

I realize this can sound a bit confusing, but feel free to ask clarifying/qualifying questions.

Thanks!

 

[BigC]

It does sound a bit confusing - at least to me!

1. What function is =BDS(..."cols=6;rows=69")?
2. Is the purpose of "cols=6;rows=69" to return a range argument to the function, and does this range vary in both and width (columns) and depth (rows)?

If so, the =OFFSET function nested within your primary function may do this (aka a 'dynamic range'), viz;
=OFFSET([anchor cell],[No. of rows +/- offset from anchor],[No. of columns +/- offset from anchor],depth,width)

The trick/clever bit is to then use =COUNT (for numbers) or =COUNTA (for numbers/text) within OFFSET to return the variable number of rows and/or columns that define the range required.
With a bit of experimentation with the OFFSET function (e.g. nested inside a =COUNTA function to count the number of values in a range) you can figure out how it works.

HTH

 

[mibrahim]

Thanks HTH,

I found a work around by just COUNTing the output (which was, yes, a variable length - but with a maximum length of 360, so I was able to just enter a range of 360 rows). Nonetheless, thank you.

M