A B C
1 10 0 =B4
2 10 10
3 10 20
4 10 30
5 inputting new values -- > C1 changes it's value from =B4 to B5 as new value gets created.
How do I create an excel formula that changes itself (C1) to =B4 -- > B5 -- > B6 etc, whenever a new cell is created at B5 and 6 and so on.
Last edited by fadu4u; 07-31-2014 at 09:05 PM.
Untested, but try:Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Almost worked but this is strange, It gave me value on cell F15 however, the cells are filled until F18.
I used this: =INDEX(F:F , COUNT(F:F))
EDITED: I just used =COUNT(F:F) it gave me result as there are 15 rows same F15, despite till F18 cells are completely full.
it fails to recognize values over F15 for some reason.
Last edited by fadu4u; 07-31-2014 at 07:59 PM. Reason: New information added.
Suggest you post a sample workbook so we can see what you see.
It is working on sample workbook, except my actual workbook.
I copied my actual data to a sample workbook for you.
Same problem occuring.
https://docs.google.com/spreadsheets...it?usp=sharing
[FIXED] I had to define First data in the range, simply F:F won't work F5:F will work where F5 is the head of the data.
If you want the last (bottom-most) numeric value in the column:
=LOOKUP(1E100,F:F)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
this may also work, but what is 1E100 in this formula?
can I also use multiplication in my lookup formula? =LOOKUP(1E100,F:F) * B10 ?
1E100 is scientific notation (a shorthand method for expressing very long numbers) for the very large number 1 followed by 100 zeros.Originally Posted by fadu4u
How it works...
If the lookup value 1E100 is larger than any number in the range the formula will return the last (bottom-most) number in the range. There is a 100% chance that no numbers you're working with are anywhere close to being 1E100.
Yescan I also use multiplication in my lookup formula? =LOOKUP(1E100,F:F) * B10 ?
it doesn't work.
check here, I used your formula on this sample sheet.
https://docs.google.com/spreadsheets...it?usp=sharing
I don't know anything about Google docs.
It works in Excel.
it doesn't even work on excel.
I used this =LOOKUP(1E+100,D:D )
and the data is in row D. Nevermind I got my job done with the other formula, thanks for help.
Sure it does.
Do you mean column D?I used this =LOOKUP(1E+100,D:D )
and the data is in row D.
Here's a sample file.
The formula will return the last (bottom-most) numeric value from column D.
Start deleting the cells from the bottom of column D and notice the formula results.
Last edited by Tony Valko; 07-31-2014 at 09:40 PM.
lol it didn't work when the first time, now it does.
Thanks, I'll add reputation.
further more it also works on google docs now.
Reason, your mistake :P its =Lookup(1E+100, D:D) instead you wrote =lookup(1E100, D:D)
Excel automatically adds the plus sign.further more it also works on google docs now.
Reason, your mistake :P its =Lookup(1E+100, D:D) instead you wrote =lookup(1E100, D:D)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks