Account codes
 

Working with a cash worksheet that I list deposits and expenditures. Need to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words (in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting code.

Thanks

Account codes
 

"Gary" wrote:

Working with a cash worksheet that I list deposits and expenditures. Need to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words (in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting code.

Hello Gary

A multiple IF statement might do.
if(or(a2="Rent","Housekeeping","Utilities"),3,if(o r(a2="Salaries","Expenses"),2))

This is limited to seven IF statements so that if this is not enough create
a list of deposits and expenitures and put the ac# in the next column and use
a Lookup formula

Regards Peter

Account codes
 

Create a 2 column lookup table with all the key words in the left column and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures. Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words
(in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures. Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words
(in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

Gary

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 09:19:03 -0800, Gary wrote:

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

If you look at the workbook you will see two sheets, one named 'U$ Cash' and
another 'Sudanese SDG's'. They are the two sheets that are of concern. Each
has a column called 'Account Code' (or 'Code'). I have created a table on
another sheet called 'LookUp' that has a column with certain key
words/string, and another column that lists a number to which I want the key
words associated with. I am trying to create a formula/relationship that
whenever I enter text in the 'Description' column of the two main sheets, if
the text includes a key word/string that is found in the 'LookUp' table, the
associated account value will be put in the same row under 'Account Code/Code'

The particular spreadsheet being used for set up was not worded
particularily in the 'Description' column to match the account related words,
so there will be quite a few in this sample file that will not properly
assign account codes. Will be modifying March cash sheets when I get back to
Sudan in a few days if what I am looking to accomplish can be done.
Thanks for your help..
file download at:
http://www.freefilehosting.net/download/3d492

"Gord Dibben" wrote:

Gary

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 09:19:03 -0800, Gary wrote:

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

In U$Cash G3 enter this formula =VLOOKUP(B3,LookUp!$B$2:$C$26,2,FALSE)

Copy down to end of data.

In Sudanese SDG's G7 enter =VLOOKUP(B7,LookUp!$B$2:$C$26,2,FALSE)

Copy down to end of data.

You will get lots of N/A cells because your Lookup B column list is incomplete.

You could give the lookup list "LookUp!$B$2:$C$26" a defined name like CodeList
then use that in the formulas for less typing.

=VLOOKUP(B3,CodeList,2,FALSE)


Gord

On Tue, 4 Mar 2008 11:12:17 -0800, Gary wrote:

If you look at the workbook you will see two sheets, one named 'U$ Cash' and
another 'Sudanese SDG's'. They are the two sheets that are of concern. Each
has a column called 'Account Code' (or 'Code'). I have created a table on
another sheet called 'LookUp' that has a column with certain key
words/string, and another column that lists a number to which I want the key
words associated with. I am trying to create a formula/relationship that
whenever I enter text in the 'Description' column of the two main sheets, if
the text includes a key word/string that is found in the 'LookUp' table, the
associated account value will be put in the same row under 'Account Code/Code'

The particular spreadsheet being used for set up was not worded
particularily in the 'Description' column to match the account related words,
so there will be quite a few in this sample file that will not properly
assign account codes. Will be modifying March cash sheets when I get back to
Sudan in a few days if what I am looking to accomplish can be done.
Thanks for your help..
file download at:
http://www.freefilehosting.net/download/3d492

"Gord Dibben" wrote:

Gary

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 09:19:03 -0800, Gary wrote:

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Account codes
 

Gord, thanks for the reply. Playing around with it, it looks VLOOKUP doesn't
have the ability to recognize one key word out of a series of words like I
had hoped. Guess I will have to have two colums for description; 1st that
contains the relevant key word and the 2nd that is more descriptive.

Thanks,
Gary

"Gord Dibben" wrote:

In U$Cash G3 enter this formula =VLOOKUP(B3,LookUp!$B$2:$C$26,2,FALSE)

Copy down to end of data.

In Sudanese SDG's G7 enter =VLOOKUP(B7,LookUp!$B$2:$C$26,2,FALSE)

Copy down to end of data.

You will get lots of N/A cells because your Lookup B column list is incomplete.

You could give the lookup list "LookUp!$B$2:$C$26" a defined name like CodeList
then use that in the formulas for less typing.

=VLOOKUP(B3,CodeList,2,FALSE)


Gord

On Tue, 4 Mar 2008 11:12:17 -0800, Gary wrote:

If you look at the workbook you will see two sheets, one named 'U$ Cash' and
another 'Sudanese SDG's'. They are the two sheets that are of concern. Each
has a column called 'Account Code' (or 'Code'). I have created a table on
another sheet called 'LookUp' that has a column with certain key
words/string, and another column that lists a number to which I want the key
words associated with. I am trying to create a formula/relationship that
whenever I enter text in the 'Description' column of the two main sheets, if
the text includes a key word/string that is found in the 'LookUp' table, the
associated account value will be put in the same row under 'Account Code/Code'

The particular spreadsheet being used for set up was not worded
particularily in the 'Description' column to match the account related words,
so there will be quite a few in this sample file that will not properly
assign account codes. Will be modifying March cash sheets when I get back to
Sudan in a few days if what I am looking to accomplish can be done.
Thanks for your help..
file download at:
http://www.freefilehosting.net/download/3d492

"Gord Dibben" wrote:

Gary

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 09:19:03 -0800, Gary wrote:

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks