up vote 0 down vote favorite
2
share [g+] share [fb]

how i can put last column contain value in 1 cell in excel?

i have figures from F4 to F83 ( each week the last figures will be in different cell between 4 and 83 - for exp this week it's was 82 rows and next week 50 rows)

and i want put the last figures in F84

please can you help me with that

i found vb formala but i need simple thing i don't have experience with this things

thanks

======

thanx andy i have petty cash table in column D i have debit side and in column E i have credit side and in column F i have the balance so for each transaction i write the debit or credit side then it's will be the balance i need the last row balance to be shown for example in F84 which it will be Total Ending Cash Balance so for example in this week if the wrote last transaction in F70 i want to show this number in F84 i hope u understant what i mean now

link|improve this question
94% accept rate
I'm finding it difficult to fully understand the question. Let me check I understand: each week you fill out a new column, from rows 4 to 83. So one week it's F4 - F83, the next G4 - G83. You want to have a calculated value in row 84. You say you want last figures but I don't know what you mean by that: could you describe it a bit more and/or give an example? – Andy Jul 14 '10 at 9:38
feedback

3 Answers

up vote 1 down vote accepted

As I understand you just want the value of the last row in the last column copied to row 84.

This function should do it (put it in cell F84):

=CELL("contents",INDIRECT((ADDRESS(COUNT(F4:F83)+3,6))))

This function only works if the first value is on row 4 and the rest are on consecutive rows.

You could also do it using VBA:

Sub foo

With ActiveWorkbook.ActiveSheet.UsedRange

    Cells(20, .SpecialCells(xlCellTypeLastCell, xlFormula).Column).Value = _
    .SpecialCells(xlCellTypeLastCell, xlFormula).Value
End With

End Sub
link|improve this answer
what +3, 6 mean? thanx alot for ur help, it's work – khalid Jul 15 '10 at 6:25
This part: "COUNT(F4:F83)+3" is the row of the cell you copy. And this part: ",6" (could be: ";6") is the column. Because you start at row 4 you have to add the 3 missing rows to your total count. The 6 means the f-column. – jasioBasio Jul 15 '10 at 10:32
feedback
up vote 1 down vote

Try 

=OFFSET(F3,COUNTA(F4:F83),0,1,1).

link|improve this answer
thanks alot it work but for some sheet i've to changed F3 to F4, i don't know why and all the sheet start from F4..... plz can u let me know 0,1,1 refer to what? – khalid Jul 15 '10 at 7:11
A basic offset looks like this: OFFSET(reference cell, number of rows to move down, number of columns to move across, returning this number of rows high, and this number of columns wide). So, 0 is no columns over (because you want to use the column of your reference cell) and you only want 1 cell, so 1 row high, 1 column wide. – variant Jul 15 '10 at 11:29
feedback
up vote 0 down vote

I don't have Excel to try it on, but maybe this will help. It works in OpenOffice calc.

Assuming that there are no blank cells in the range between F4 and the last filled cell, then Count(F4:F83) returns the number of filled cells and Count(F4:F83)+3 returns the row number of the last filled cell.

The function INDIRECT returns the contents of the referenced cell, for example, INDIRECT("F7",1) returns the contents of cell F7, so INDIRECT("F"&INT(count(F4:F83)+3,1) returns the contents of the last filled cell in the range F4:F83.

Perhaps someone could translate this to Excel code for you. Or you could consult this page for how to use the Excel INDIRECT and ADDRESS functions.

I found this trick on a LibreOffice forum. Use the formula =lookup(1e+99,range_to_search) to return the last numeric value in the range. A logical extension is =lookup("ZZZZZZZZZZZ",range_to_search) which returns the last filled cell (provided "ZZZZZZZZZZZ" would sort higher than any entry in the range). Cells do not have to be filled contiguously.

link|improve this answer
thanks alot for ur help – khalid Jul 15 '10 at 6:29
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.