How do I create a formula for a Chart of Account list?

  • Thread starter Thread starter sweetb
  • Start date Start date
S

sweetb

I would like to be able to create a formula, so that when I type in a Chart
of Account number in one cell, the description of the account appears in the
next cell.

I've been searching the Microsoft Help site, but no luck...

Anyone??
 
Hi sweetb

Have a look at creating a VLOOKUP table.
A search on here or the help page will give you thousands of examples.

HTH
Michael M
 
Try this:

You could create a 2-column list on another sheet that has the AcctNum in
the left column and the corresponding AcctName in the right column.

Once that's done, you could use a VLOOKUP function to find the input AcctNum
in that list and return the AcctName.

Example:
Sheet2, A1:B3 contains this list:
100 Cash
200 A/R
300 A/P

Then....on Sheet1
A1: 200
B1: =VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0)

That basic formula will return "A/R".

But if you use Data Validation in cell A1,
your users would see a drop-down list of available
AcctNums to choose from. Selecting one would return its
AcctName in B1.

You could also use this slightly fancier version to avoid the error
message when no AcctNum is selected:
B1: =IF(B1="","",VLOOKUP(A1,Sheet2!$A$1:$B$3,2,0))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Use the VLOOKUP Function.

You have a table of Account Numbers and Descriptions in two columns, say on a
separate Sheet2 in Columns A and B from row1 to row 50

In A1 of Sheet1 enter an account number.

In B1 of Sheet1 enter this formula =VLOOKUP(A1,Sheet2!$A$1:$B$50,2,FALSE)

The description for account number will show in B1.

For more on VLOOKUP and perhaps Data Validation dropdown lists see Debra
Dalgleish's site.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

The DV lists save the typing part.


Gord Dibben MS Excel MVP
 
Back
Top