How to get a specific information from a table - Please help me

If your data is in Columns A:B, try this to get the date:

=INDIRECT("A"&(9.99999E+307,$B:$B))
and this for amount
=INDIRECT("B"&(9.99999E+307,$B:$B))

If you have Excel in a different language, you can easily insert this formulas without translating them doing this. Select the destination cell for the Date formula, Press Alt - F11, Ctrl G and write this there.

ActiveCell.Formula = "=INDIRECT(""A""&(9.99999E+307,$B:$B))"

and press Enter.

Juan Pablo G.

this worked

table retval
A B D E
Date Amount Date Amount
1/1/2001 5 1/5/2001 6
1/2/2001 6 =INDEX(A:A,E2) =COUNTA(B:B)
1/3/2001 88
1/4/2001 95
1/5/2001 5
1/6/2001
1/7/2001
1/8/2001
1/9/2001
1/10/2001

I always forget about INDEX !!!

Juan Pablo G.

Ready for some really nasty VBA Script, does what you ask and is fast enough, but rough and ready, maybe some one will clean it up a bit, its reallynasty. Assign a button to this code.
I called F1 returned Date
I called G1 returned Amount

>>>>
Sub Button1_Click()
'Written bu Jack in UK
Columns("A:A").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Select
ActiveCell.Copy

Range("F2").PasteSpecial
Columns("B:B").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Select
ActiveCell.Copy
Range("G2").PasteSpecial

Columns("A:A").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Select

Application.CutCopyMode = False

End Sub
>>>>>

Try that until a proper programmer offers a sensible script,

It works oddly enough
HTH

Another formulation

Fabricio --

If you already entered 365 dates of a year in A and you are entering an amount every day in B,

in D2 enter: =INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

This will give you the last amount entered. And

in E2 enter: =INDEX(A:A,MATCH(9.99999999999999E+307,B:B))

Format E2 as date. This formula will provide the date on which the last amount was entered.

Note. In order these formulas to work, columns A and B should not contain anything else but the relevant data.

Aladin

============

Re: Another formulation

Thank you all very much. I stuck with Aladin's response, it worked fine. I just had a bit of problem in translating the formulas into Portuguese (this is Brazil speaking!), but Juan Pablo helped me with this issue.

Can I make it a little more complicate? Here it is: sometimes, I will have to input in the Amount column a specific word ("Out") instead of a number. In such case, I also want that the table you helped me to build shows that the last amount entered was "Out". I tried that, I messed up with your formula, but it didn't work. Any ideas? --

Re: Another formulation

If your data is "alone" in columns A and B, that is, only they are inputted there, you can have this one working.

For date (D2)
=INDEX(A:A,COUNTA($B:$B))

and for Amount(E2)
=INDEX(B:B,COUNTA($B:$B))

Which were suggested by Faster.

If the data is not "alone" then i think you should need a UDF that isn't very complicated to create.

Juan Pablo G.

Re: Another formulation

Fabricio --

The set of formulas in my first reply apply indeed only to dates and numbers (I should have pointed out that).

Now you want to mix up numbers and text such as "Out". If it's guaranteed that you don't leave any gaps (blanks) between the entries in B, you can use the following set of formulas:

In D2 enter: =INDEX(B:B,COUNTA(B:B))
In E2 enter: =INDEX(A:A,COUNTA(B:B))

With one or more blank cells in B, this formulas will not return the right answer.

If you want to allow the blanks in B, then use the following set which is impervious to text and blank cells:

In D2 enter: =INDEX(B1:B366,SUMPRODUCT(MAX((B1:B366<>0)*ROW(B1:B366))))
In E2 enter: =INDEX(A1:A366,SUMPRODUCT(MAX((B1:B366<>0)*ROW(B1:B366))))

Note that the last uses a range instead a whole column as argument. Whole columns simply cannot be fed to SUMPRODUCT. I believe you don't need to use the whole column, so that's OK.

Aladin

=======

Re: Another formulation

Thanks again, Aladin. This last set of formulas was just perfect! --

Re: Another formulation

I just thanked Aladin, now I thank you. You both came with the same solution and it was just perfect!