Formula update needed by referring to another table

Welcome to the forum! Please click Go Advanced, click the paperclip icon and upload a sample workbook with the tables you mentioned. Sanitize the data first if needed to protect company information, and I'll take a look at it.

Thanks a lot for looking into it. The formula that needs to be updated is in Expense sheet (Coulmn K) K14.
If I select Operations in C14 and then type of expense then project either Loraine or PG it should refer to the table in data sheet and refer to list of departments and not list of offices which it is currently doing. Also if I select Pg project in E14 it is not linked to formula at all. I need to add the project codes for it which are listed in U 11 and U12 in Data sheet. I did not create the sheet so I am very confused. I will really appreciate your help. Thanks again. Do let me know if you have additional questions.

Regards,
Deenah

Deenah;
1st - your profile says you're working in Excel 2003, but the file you uploaded is an .xlsx file, so you might update your profile.

The computer I'm working on right now only has Excel 2003. If you can save your file in .xls format and upload it I'll look at it. Or when I get on another computer I'll look at it if no one else has solved your problem.

In the mean time:
Your formula is very complex. Try using "Formula Auditing" > "Evaluate Formula". It will step through your formula one calculation at a time and show you the order that each part of the formula is calculated and the results. I don't know where "Formula Auditing" if in 2007's ribbon, but it shouldn't be too hard to find.

I got lost trying to follow all the formulas.
Tell me what cell has the wrong result. And tell me what the result should be.

I personally would have hidden columns with partial formulas in them and have the final formula (which would be easier to follow) in the results cell.

I made that last entry while in pain. I have a problem with my shoulder.

I don't use that other computer because it's a desktop and the angle I have to hold my arm to use it hurts my shoulder. So I hadn't thought my response out yet.

Now I'm back on my laptop and can think straight. I think I might have gotten enough to give you an idea of how to fix your formula.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Then alter the part you want changed.

If you want to then put it back into 1 formula.
1) Replace "$AI14" with the contents of $AI14 (without the "=")
2) Replace "$AH14" with the contents of $AH14 (without the "=")
etc...