Can I do this...and if so...HOW!?

[Guest]

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!

 

[Guest]

Hi.

if you put an example in your posting it would be more underestandable.

Thanks,

 

[Guest]

Hmmm...that's a tough one...let me see if I can show it as best as
possible...I tried to explain it but it is hard to explain without
showing...so I'll give it a try:

Sheet 1:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]

Okay...so the information in Column C on Sheet 2 is from drop down
lists...what I want is for the individual columns on Sheet 1 to look in
Column C on Sheet 2 and see if it sees itself....if it does...I want it to
pull the value from Column B on Sheet 2 and place it in the next available
cell in its respective column on Sheet 1!?

Does that make it any clearer? Sorry...I am having trouble explaining it
well enough...I may not be able to get this to work just for lack of ability
to explain it! HA!!

Thanks in advance for any help!

 

[Guest]

Hi again,

what if your data would be like below:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]
8/15/07 80.00 [Gas]
9/12/07 100.00 [Gas]

so for example for Gas you want to pull those 3 row data regarding Gas
separately or you want sum of them or you want sum of them monthly?

Thanks,
--
Farhad Hodjat

Hmmm...that's a tough one...let me see if I can show it as best as
possible...I tried to explain it but it is hard to explain without
showing...so I'll give it a try:

Sheet 1:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]

Okay...so the information in Column C on Sheet 2 is from drop down
lists...what I want is for the individual columns on Sheet 1 to look in
Column C on Sheet 2 and see if it sees itself....if it does...I want it to
pull the value from Column B on Sheet 2 and place it in the next available
cell in its respective column on Sheet 1!?

Does that make it any clearer? Sorry...I am having trouble explaining it
well enough...I may not be able to get this to work just for lack of ability
to explain it! HA!!

Thanks in advance for any help!

Hi.

if you put an example in your posting it would be more underestandable.

Thanks,

 

[Guest]

I want the 3 rows of gas to show seperately when they arrive on Sheet 1. So
each time sheet 1, column A searches sheet 2...it will pull the data relavant
to "Gas" and place new transaction amounts into the next available cell in
the column.

Hi again,

what if your data would be like below:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]
8/15/07 80.00 [Gas]
9/12/07 100.00 [Gas]

so for example for Gas you want to pull those 3 row data regarding Gas
separately or you want sum of them or you want sum of them monthly?

Thanks,
--
Farhad Hodjat

Hmmm...that's a tough one...let me see if I can show it as best as
possible...I tried to explain it but it is hard to explain without
showing...so I'll give it a try:

Sheet 1:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]

Okay...so the information in Column C on Sheet 2 is from drop down
lists...what I want is for the individual columns on Sheet 1 to look in
Column C on Sheet 2 and see if it sees itself....if it does...I want it to
pull the value from Column B on Sheet 2 and place it in the next available
cell in its respective column on Sheet 1!?

Does that make it any clearer? Sorry...I am having trouble explaining it
well enough...I may not be able to get this to work just for lack of ability
to explain it! HA!!

Thanks in advance for any help!

Hi.

if you put an example in your posting it would be more underestandable.

Thanks,
--
Farhad Hodjat


:

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!

 

[Guest]

Hello,

I don't know where are you but i am in Toronto and right now is 5:30pm and
should leave work place but i think about it at home and tomorrow wil let you
know if i could solve it or not.

Thanks,
--
Farhad Hodjat

I want the 3 rows of gas to show seperately when they arrive on Sheet 1. So
each time sheet 1, column A searches sheet 2...it will pull the data relavant
to "Gas" and place new transaction amounts into the next available cell in
the column.

Hi again,

what if your data would be like below:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]
8/15/07 80.00 [Gas]
9/12/07 100.00 [Gas]

so for example for Gas you want to pull those 3 row data regarding Gas
separately or you want sum of them or you want sum of them monthly?

Thanks,
--
Farhad Hodjat

Hmmm...that's a tough one...let me see if I can show it as best as
possible...I tried to explain it but it is hard to explain without
showing...so I'll give it a try:

Sheet 1:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]

Okay...so the information in Column C on Sheet 2 is from drop down
lists...what I want is for the individual columns on Sheet 1 to look in
Column C on Sheet 2 and see if it sees itself....if it does...I want it to
pull the value from Column B on Sheet 2 and place it in the next available
cell in its respective column on Sheet 1!?

Does that make it any clearer? Sorry...I am having trouble explaining it
well enough...I may not be able to get this to work just for lack of ability
to explain it! HA!!

Thanks in advance for any help!

:

Hi.

if you put an example in your posting it would be more underestandable.

Thanks,
--
Farhad Hodjat


:

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!

 

[Guest]

I'm a west coaster...so I still have a few hours. Thank you for you help
though...very much appreciated! I look forward to your insight tomorrow! Have
a great night!

Hello,

I don't know where are you but i am in Toronto and right now is 5:30pm and
should leave work place but i think about it at home and tomorrow wil let you
know if i could solve it or not.

Thanks,
--
Farhad Hodjat

I want the 3 rows of gas to show seperately when they arrive on Sheet 1. So
each time sheet 1, column A searches sheet 2...it will pull the data relavant
to "Gas" and place new transaction amounts into the next available cell in
the column.

Hi again,

what if your data would be like below:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]
8/15/07 80.00 [Gas]
9/12/07 100.00 [Gas]

so for example for Gas you want to pull those 3 row data regarding Gas
separately or you want sum of them or you want sum of them monthly?

Thanks,
--
Farhad Hodjat


:

Hmmm...that's a tough one...let me see if I can show it as best as
possible...I tried to explain it but it is hard to explain without
showing...so I'll give it a try:

Sheet 1:

Column: A B C D E
Gas Groceries Ent Medical Misc
50.00 250.00
35.00


Sheet 2:

Column: A B C
Date Amount For?
8/10/07 55.00 [Misc]
8/11/07 25.00 [Ent]
8/12/07 35.00 [Gas]

Okay...so the information in Column C on Sheet 2 is from drop down
lists...what I want is for the individual columns on Sheet 1 to look in
Column C on Sheet 2 and see if it sees itself....if it does...I want it to
pull the value from Column B on Sheet 2 and place it in the next available
cell in its respective column on Sheet 1!?

Does that make it any clearer? Sorry...I am having trouble explaining it
well enough...I may not be able to get this to work just for lack of ability
to explain it! HA!!

Thanks in advance for any help!

:

Hi.

if you put an example in your posting it would be more underestandable.

Thanks,
--
Farhad Hodjat


:

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!

 

[Guest]

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:
Tools>Macro>Visual Basic Editor
and in the Visual Basic Editor go to:
Insert>Module
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
(e-mail address removed)

Thanks,

 

[iliace]

Okay, this might be a little too complicated... basically, I would
create two dynamic ranges. Name them Categories and Amounts. Here's
how to do that:

In Excel 2003 or earlier, go to Insert->Name->Define. For Categories,
assuming your "For?" heading is in C1, do this:
Name: Categories
Refers To: =OFFSET('Sheet2'!$C$1,1,0,COUNTA($C:$C)-1,1)

Name: Amounts
Refers To: =OFFSET('Sheet2'!$B$1,1,0,COUNTA($B:$B)-1,1)

This assumes you do not have anything else in these columns besides
the table data.

Now, on your Sheet1, highlight the column right below "Gas" heading
(the following assumes it's in A1), going as far as you expect your
list to expand in the future (possibly 1000-2000 rows). With all
those cells hightlighted, go to the formula bar, enter the following
formula, and press Ctrl+Shift+Enter (instead of just Enter):

=IF(ISERROR(INDEX(Amounts,SMALL(IF(Categories=A
$1,ROW(INDIRECT("1:"&ROWS(Amounts)))),ROW(INDIRECT("1:"&ROWS(Amounts)))))),"",INDEX(Amounts,SMALL(IF(Categories=A
$1,ROW(INDIRECT("1:"&ROWS(Amounts)))),ROW(INDIRECT("1:"&ROWS(Amounts))))))

With the entire column still highlighted, use Ctrl+C, and paste in B2,
C2, D2, and any other category columns you expect to have. Keep in
mind that once your list on Sheet2 grows, this calculation will become
increasingly slow (I'm experimenting with 500 rows, and it takes about
2 seconds each time I enter data), so you may want to set
recalculations for this workbook to Manual, and press F9 each time you
want Sheet1 to update.