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