How to retrieve contents from a single cells within a range w/blanks

[wec43wec]

Using Excel.

In column a, I have a number on every 10th row(i.e. 10 in cell A10, 20 in cell A20, 30 in cell A30, etc).

I want to have a formula in cell B25 that says look in column A and give me or insert the first number I get to when I look at the cells above the current (formula) row number. In this example it would be starting from cell A25 thru A1.

In the above example, I am looking for a return value of "20".

If I had the same condition, but the formula was in cell B15 instead of B25, I would be looking for a return value of 10.

Note: Not using VBA if possible.

Any ideas?

Thanks

 

[combo]

[tt]=INDIRECT(ADDRESS(10*INT(ROW()/10),1))[/tt]
Not to apply for rows 1-9.

combo

 

[SkipVought]

hi,

You are not really clear as to what value you want to supply in order to get a value returned.

If you supply the OCCURRENCE, ie 1, 2, 3 etc in a cell or is your formula repeated in in rows and you want the largest value prior to the row of the formula. IT IS NOT CLEAR!

If you simply supply a number, like 1, 2 or 3 etc, then it could be a simple as...
[tt]
=INDEX(A:A,D1*10,1)
[/tt]
where D1 contains your supplied number.

If you are looking for something else, please be much clearer with your requirement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluedragon2]

=INT(ROW()/10)*10

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

We're all guessing at what you need!!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]