So There are two different styles of general ledger programs being used. I need to quickly and easily Reference numbers in a column and depending on what it is put another number in the last column.



So for example the xlsx file has these columns

ACCT NO | Description | Ammount
02-206300 Savings 400.00

And i need to add a 3-0-0-2310 So it looks like this

ACCT NO | Description | Amount | New Acct NO
02-206300 Savings 400.00 3-0-0-2310

There are 800 Different account numbers with new account numbers and multiple sheets that need to be processed with these numbers. I cant think of a way to quickly do this.

I have a key setup with the original number and the new number all together

Have you looked into using the VLOOKUP function.

It will kinda do what i want to do but its a little messy. One thing i noticed when i copied down the table array changed as well as the lookupvalue is there a way to get just the lookupcalue to change?

Is there any other ideas out here?

a) Use absolute references for account reference table. P.e. you have on separate sheet Accounts a table with headers Account and NewAccount in A1 and B1, and data filled down to row 600. Now when on some another sheet with account numbers in column A you want to get new account into some other colum on row 2, the formula will be:
=VLOOKUP($A2,Accounts!$A$2:$B$600,2,0)

b) Define all tables as Tables (select any single cell in datarange; Insert>Table; check "My table has headers"; name the defined table). P.e. with table tAccounts defined on sheet Accounts, and calculating new account number in another defined Table with old account in column AccountNo, for any Table row the formula will be:
=VLOOKUP([@AccountNo],tAccounts,2,0).

Both formulas on coping down adjust the reference to old account, but leave the reference to accounts table unchanged.

I was able to define the tables and that works for me. This is a basic solution. Thanks for your help everyone.