calc result to cell location

[gavster]

Hi All,

This will probably be very easy for the experts, but I cant find it previously on here, so here goes.....

I have a column of data (eg a10 to a200) which is gradually being filled, and I'd like to have the last entry in a cell at the top of the spreadsheet.

Currently I'm using the Count function to determine how many cells in the range have data in them (eg 10), and adding this to the starting point(eg =(count(a10:a200))+10) to give me a calculated figure of 20.

So I know A20 is the last entry, but how can I get C3 to display the contents of A20?

Probably a much easier way than I am trying at present.

Thanks in advance.

Gavin

[Carim]

Hi,

You can use following formula :

Please Login or Register  to view this content.

[gavster]

Thanks Carim.

I've inserted the code and it works for some of my columns and not others, I'll explain.

Column A is manually entered data, code works fine.
Col B also manualdata, code works fine.
Col C manual data, code works fine.
Col D is calculated as (col C/col B), code works fine.
Col E is calculated as average of last 10 figures in col D. Code gives no result.

I've tried 2 ways of calculating col E, just in case it makes a difference:

Origiinal is =average($d290:$299)
Alternative is =IF(D299=0," ",(AVERAGE(D290:D299)))

Cant understand why these calculations throw this out, but col D is OK.

Cheers

[gavster]

Please ignore the previous post, I've solved the final problem.

Converted col E to =IF(D300=0,"",(AVERAGE(D290:D299)))

Forgot that this code was replicated down to row 700. With no data in D700, E700 contained a " " (eg a space). So the code was working, and returning a space as the result.

Thanks very much Carim.

[Carim]

Glad it fixed your problem ...

Thanks for the feedback