My IT department is currently working on a way to convert my business from
Excel to a MS SQL database, but until then I need figure out a way to
maintain my daily functionality and meet the demands of my clients. I'm
trying to create a formula that will automatically insert a job costing code
which will be referenced on another worksheet to fill in Costs and Accruals.

Here is the relevane layout of my spreadsheet:

Worksheet A:
A- Origin/Shipper
B- Destination
W- Job Cost Code

Worksheet C:
B- Destination Name
C- Store Number/Job Cost Code

What I would like to do, is have Excel lookup the name of the desination and
display the appropriate job cost code as outlined on the table in Worksheet
C. I came up with the following formula:
=IF(B=WorksheetC!B,LOOKUP(B,WorksheeC!B,WorksheetC !C))

The problem is, that not all of the destinations appear on the data table in
Worksheet C. If that happens, I want Excel to lookup the value in column A,
the origin. But it returns the value for the closest match instead of
returning a FALSE value. I thought that this would work:
=IF(B=WorksheetC!B,LOOKUP(B,WorksheetC!B,Worksheet C!C))*AND(IF(W=FALSE(),(LOOKUP(A,WorksheetC!B,Work sheetC!C))

That just returns a circular argument, and no matter what setting I put in
'iteration' it comes back as a 0.

I thought that by taking the circular argument out of the formula it might
work, so added an outside reference in Column Y:
=IF(B=WorksheetC!B,TRUE(),FALSE())

Then changed W to:
=IF(Y=TRUE(),LOOKUP(B,WorksheetC!B,WorksheetC!C))* AND(IF(Y=FALSE(),LOOKUP(A,WorksheetC!B,WorksheetC! C))

All I get now is a #VALUE! result in W.

Any suggestions?
--
Stephen Nichols
Freight Management Services
Cole International

Some thoughts ..

In sheet: A,
(data is assumed from row2 down)

Try in W2:
=IF(B2="","",IF(ISNA(VLOOKUP(B2,'C'!B:C,2,0)),IF(I SNA(VLOOKUP(A2,'C'!B:C,2,0)),"--",VLOOKUP(A2,'C'!B:C,2,0)),VLOOKUP(B2,'C'!B:C,2,0) ))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Canadian Chai Guy" wrote:
My IT department is currently working on a way to convert my business from
Excel to a MS SQL database, but until then I need figure out a way to
maintain my daily functionality and meet the demands of my clients. I'm
trying to create a formula that will automatically insert a job costing code
which will be referenced on another worksheet to fill in Costs and Accruals.

Here is the relevane layout of my spreadsheet:

Worksheet A:
A- Origin/Shipper
B- Destination
W- Job Cost Code

Worksheet C:
B- Destination Name
C- Store Number/Job Cost Code

What I would like to do, is have Excel lookup the name of the desination and
display the appropriate job cost code as outlined on the table in Worksheet
C. I came up with the following formula:
=IF(B=WorksheetC!B,LOOKUP(B,WorksheeC!B,WorksheetC !C))

The problem is, that not all of the destinations appear on the data table in
Worksheet C. If that happens, I want Excel to lookup the value in column A,
the origin. But it returns the value for the closest match instead of
returning a FALSE value. I thought that this would work:
=IF(B=WorksheetC!B,LOOKUP(B,WorksheetC!B,Worksheet C!C))*AND(IF(W=FALSE(),(LOOKUP(A,WorksheetC!B,Work sheetC!C))

That just returns a circular argument, and no matter what setting I put in
'iteration' it comes back as a 0.

I thought that by taking the circular argument out of the formula it might
work, so added an outside reference in Column Y:
=IF(B=WorksheetC!B,TRUE(),FALSE())

Then changed W to:
=IF(Y=TRUE(),LOOKUP(B,WorksheetC!B,WorksheetC!C))* AND(IF(Y=FALSE(),LOOKUP(A,WorksheetC!B,WorksheetC! C))

All I get now is a #VALUE! result in W.

Any suggestions?
--
Stephen Nichols
Freight Management Services
Cole International

It worked.

You're amazing!
--
Stephen Nichols
Freight Management Services
Cole International


"Max" wrote:

Some thoughts ..

In sheet: A,
(data is assumed from row2 down)

Try in W2:
=IF(B2="","",IF(ISNA(VLOOKUP(B2,'C'!B:C,2,0)),IF(I SNA(VLOOKUP(A2,'C'!B:C,2,0)),"--",VLOOKUP(A2,'C'!B:C,2,0)),VLOOKUP(B2,'C'!B:C,2,0) ))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Canadian Chai Guy" wrote:
My IT department is currently working on a way to convert my business from
Excel to a MS SQL database, but until then I need figure out a way to
maintain my daily functionality and meet the demands of my clients. I'm
trying to create a formula that will automatically insert a job costing code
which will be referenced on another worksheet to fill in Costs and Accruals.

Here is the relevane layout of my spreadsheet:

Worksheet A:
A- Origin/Shipper
B- Destination
W- Job Cost Code

Worksheet C:
B- Destination Name
C- Store Number/Job Cost Code

What I would like to do, is have Excel lookup the name of the desination and
display the appropriate job cost code as outlined on the table in Worksheet
C. I came up with the following formula:
=IF(B=WorksheetC!B,LOOKUP(B,WorksheeC!B,WorksheetC !C))

The problem is, that not all of the destinations appear on the data table in
Worksheet C. If that happens, I want Excel to lookup the value in column A,
the origin. But it returns the value for the closest match instead of
returning a FALSE value. I thought that this would work:
=IF(B=WorksheetC!B,LOOKUP(B,WorksheetC!B,Worksheet C!C))*AND(IF(W=FALSE(),(LOOKUP(A,WorksheetC!B,Work sheetC!C))

That just returns a circular argument, and no matter what setting I put in
'iteration' it comes back as a 0.

I thought that by taking the circular argument out of the formula it might
work, so added an outside reference in Column Y:
=IF(B=WorksheetC!B,TRUE(),FALSE())

Then changed W to:
=IF(Y=TRUE(),LOOKUP(B,WorksheetC!B,WorksheetC!C))* AND(IF(Y=FALSE(),LOOKUP(A,WorksheetC!B,WorksheetC! C))

All I get now is a #VALUE! result in W.

Any suggestions?
--
Stephen Nichols
Freight Management Services
Cole International

Great to hear that, Stephen.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Canadian Chai Guy" wrote in message
...
It worked.

You're amazing!
--
Stephen Nichols