displaying ongoing total in a fixed cell

[office boy]

hi - if possible i would like to have a fixed cell somewhere at the top of the page that displays the data from the latest addition to the total in a column..

eg: say column U is a total column..each day a row is added to the sheet..this will give a a new total in column U on that row..at the moment to get the latest total to appear at the top of the page in the selected cell i manually alter the formula there to =U445 or =U446 etc etc..(or the latest addition to that column)

could someone plaese tell me if there is way for this fixed cell to pick up data from the latest addition to column U automatically?

i've had success with using MAX and AVERAGE formula in such ways but am unable to locate any way of doing the above

many thanks in advance

ob

[NBVC]

Try:

=Lookup(9.999999999e+307,U:U)

[VBA Noob]

Maybe

=LOOKUP(MAX(U:U)+1,U:U)

more options on link

http://www.xldynamic.com/source/xld.LastValue.html

VBA Noob

[office boy]

guys thanks for yoiur prompt replies...i've had a look and can't phathom it..

i'm using office 2000 - dont know if that makes any difference to syntax?

i fully understand the thinking behind the bignum now and the formula thanks..., but entering the references you gave does not fix...

the totals in the column U i mention derive from a formula in the cells..will this inhibit?

many thanks

ob

[NBVC]

Mine should work, as long as it is the last value in the column you are looking for and it is numeric.... not text string.

[office boy]

thanks NBVC ..its financial ie: numeric preceeded by £...

the cell just seems to spit the formula out...

many thanks

ob

[NBVC]

so is it working, then.... it should work even if formatted as "financial" (or "currency")....

don't know what you mean by

the cell just seems to spit the formula out...

[office boy]

sorry ..should have explained better...

no it isn't working unfortunately

i have made sure that the cell i want to display the last cell data in isnt in the same column just in case..though would prefer at top of column U..

when i input the the formula into the cell i wish to use... the formula doesn't appear to take i have tried adding spaces (just in case there is some syntax difference somewhere) to no avail..the syntax just appears in the cell or displays name?...

just to outline the sheet set up for this -
column U has 3 rows of text and numeric data headers and the remainder is currency that are formula based (using the total above in column U in conjuction with a figure from the row T /cell beside) and is incremental..


many thanks

ob

[VBA Noob]

Just attach the file.

Both formulas should work

VBA Noob

[office boy]

here you go guys...

just a small snippet of the sheet...

basically would just like U1 to display the last cell used data in column U automatically.

at the moment it is done manually

many thanks

ob

[VBA Noob]

Try

=LOOKUP(MAX(U4:U65536)+1,U4:U65536)

You should of mentioned the circular message

VBA Noob

[NBVC]

...here you go...

the formula I recommended works as described....

[office boy]

would like to thank you both very much...thats great!!..

its mad though becasue i copy pasted that syntax in from the links and replies on these threads...but hey..you solved it for me ..

once again top stuff..thanks!!

ob

[VBA Noob]

Glad it helped

Sometimes easier for us if you add the worksheet from the start of the thread.

VBA Noob