Good morning LeahT,

I wrote a function for your call last night please follow instruction below:

Code:

Function FindNth(Table As Range, Val1 As Variant, Val1Occrnce As Integer,
SearchCol As Integer, ResultCol As Integer)

'''''''''''''''''''''''''''''''''''''''
'Written by Farhad Hodjat
'
'Finds the N'th value in the search Column of a table that has a stated _
value on the same row in another Column.
'''''''''''''''''''''''''''''''''''''''

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

For i = 1 To Table.Rows.Count
If Table.Cells(i, SearchCol) = Val1 Then
iCount = iCount + 1
End If

If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function

to add the function to your workbook when your workbook is opened go to:
ToolsMacroVisual Basic Editor
and in the Visual Basic Editor go to:
InsertModule
and copy paste the code above in the new opened window

go back to your workbook and sheet 2 and enter in the cell A2 ( in your
example ) the following formula:

=IF(FindNth(Sheet2!$A$2:$C$100,Sheet1!A$1,ROW()-1,3,2)=0,"",FindNth(Sheet2!$A$2:$C$100,Sheet1!A$1, ROW()-1,3,2))

copy drag down the formula to where ever you need (in this example A100)
copy drag right to column E ( your example)

the above formula has written based on your example and the syntax for the
function is:

=FindNth(Range, Variant, Val1Occrnce , SearchCol , ResultCol )

Range= the table that you want to make a search in it
Variant= the string that you want to find it in the table
Val1Occrnce=the number that your string has repited in the table for example
if your string has repited 3 times in the table and you want to return the
second one you should put 2
SearchCol= the number of column in your table that you want to find the
string in that column (in your example it is column 3)
ResultCol= the number of column that you want to pull out the result ( in
your example this column is amount and the column numbet is 2)

hope you can make it if not send to me your file i can do it for you


Thanks,
--
Farhad Hodjat


"LeahT" wrote:

I posted this somewhere else (can't find it now) and didn't get a response
and it might be due to the fact that I didn't explain it well...so I will try
again (my apologies if you have seen this once before somewhere else).

I have two worksheets (1 & 2)...I want worksheet 1, column A to go and look
at worksheet 2, column B to find a text value (ie: groceries)...if it finds
the word groceries in column B, I want it to grab the $$ amount from column A
and place it in the next available cell on worksheet 1, column A.

In other words, Worksheet 2 is a general journal...I am inputting daily
transactions into that worksheet...I would like for that information to
translate over to worksheet 1 which is the monthly spreadsheet that shows all
of the general ledger information seperated out by transaction. That
information eventually translates into my general ledger report (which I
already am able to do). Unfortunately, currently I am manually taking
information from worksheet 2 and placing it into its respective columns on
worksheet 1...I was hoping to take out that step and just be able to input
info into worksheet 2 and have it automatically translate over. I can get it
to work for one cell...but not all of them...and when I tried to create a
drop-down list on worksheet 2 that would be a list of the GL accounts...then
the value on the other worksheet disappeared completely...even though in the
function box it shows the correct value...it doesn't show up in the cell!

I am really frustrated...any thoughts would be helpful...and I will be happy
to try to explain better if this isn't making sense!