Create link to external file to determine if formula was overwritten

I have been racking my brain on this one, and I have a feeling this will be simpler than I anticipated. Okay I am including 2 sample files, which provide a visual of my issue. Each month we have to manual adjust PTO (Column M) and pro-rate it for people based on their seniority date (Column J). Now the PTO (Column M) had a formula... it is an if statement here, but I have already reworked it into a table with a VLOOKUP for the process. I am re-working an existing file designed by someone else. I need to be able to do a VLOOKUP in Column M for an external file, using the Employee ID (Column C) and look at the previous workbook... in this case, the August Workbook would look at July. If the formula was not altered, then the result would be the formula in August. However, if July's formula was altered (highlighted in orange), then I would like that altered formula AND (if possible) the conditional formatting of Orange to appear. This is a painstaking manually process that is required. We need to be. The issue I am facing is that there are new records added constantly, plus these records are sorted. So John Smith could be on row 3 in July, and somewhere else in August. Aside from that, I am not wanting to capture the value. If the "default formula" is not listed for that employee ID, I want that formula to be added. Am I living in a fantasy or is this possible?

Posssible with Excel 2013 or above
O2
=LEN(FORMULATEXT(INDEX([July.xlsx]Sheet1!$M$2:$M$15,MATCH(C2,[July.xlsx]Sheet1!$C$2:$C$15,))))<99

M2
=IF(O2,INDEX([July.xlsx]Sheet1!$M$2:$M$15,MATCH(C2,[July.xlsx]Sheet1!$C$2:$C$15,)),LOOKUP(L2,{0.0001,144;5,160;10,200;20,240}))

for Excel 2010 , UDF for formulatext is require,check below link

https://www.excelforum.com/excel-for...or-2007-a.html

Bo_Ry,

That is not what I meant. And FYI, I have Office 365. Okay the 1st record (Ian Harris) is highlight (Column M) on July file, and his name on August file (so you know how was manually altered easily for this exercise). The formula for anyone in Column M by default is:

=IF(AND(L2<>0,L2<5),"144",IF(AND(L2>=5,L2<10),"160",IF(AND(L2>=10,L2<20),"200",IF(AND(L2>=20),"240"))))

Now since this record was altered, his formula is:

=8*12

it is THAT formula I need displayed as a formula within cell M2 rather than the formula above since it is different.

Please update your forum profile to show your current version: as you can see, members rely on it being accurate. Thanks.

Ali,

Sorry about that... I forgot the version was part of the profile. It is updated now, and FYI, I use the same version at work and at home.

I just got an update from the user, and I checked my file, and the data/formulas I provided reflect it already in the original. The manual overrides are simple formulas. For instance the record on row 2 (Ian Harris), has a formula, which was overwritten of =8*12. These are the formulas that I need to capture. It is possible that the override is more complex than that. =1*12+11*13.33 is a perfect example, which is row 7 for Jessica Fraser. The point being none of the overrides have a lengthy IF statement, or now I converted it to a VLOOKUP to make it easier for regular updates. So basically if the formula on the previous worksheet does not contain the formula listed below, I want to pull in the formula that IS listed. Please tell me this is possible. Thanks so very much!!! The table that is the named range of PTO_Hours looks like this:

years hours
0 144
5 160
10 200
20 240

The new formula is: =IFERROR(VLOOKUP($L2,PTO_Hours,2,TRUE),"-")

Note that Post#6 must have been added while I was working on this proposal, so my proposed solution refers formulas in the original files. If you need help converting to the new set up please upload a current version of the August file.
I am still a bit unclear as to whether you want to see the "overwritten" formulas from July, or the values those formulas yield.
I have included both. The formulas are based on the first formula in Bo_Ry's post.
To show the formula use:

Formula:

Please Login or Register  to view this content.

To show the values use:

Formula:

Please Login or Register  to view this content.

Conditional formatting for formulas: =LEN(M2)>3
Conditional formatting for values: =ISNUMBER(M2)
Let us know if you have any questions.

JetMc---

I will check it out for the user. Thanks!

You're Welcome. If the proposal does what the user wants, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Thanks so much. Sorry I was out of town on business again. User is happy!