# Can I use conditional formatting to highlight cells in a Google sheet tab that are being referenced in a formula of another tab?

I am reconciling my monthly expenses. I have a Google sheet tab that is summing monthly transaction totals for each vendor from a list of transactions that is located on another tab.

I'd like to easily identify all of the transactions that have been included in the vendor sums so that I know which remaining transactions need further investigation.

Is there a way to use conditional formatting to highlight all the cells in a Google sheet tab that are being referenced in formulas of another tab?

Or is there a better way of accomplishing this goal?

• Hi, welcome to WebApps. One of the challenges of your question is to “know” which transactions have been referenced. One method might be to include a checkbox on each transaction row. You could then write a single formula (perhaps based on ‘sumif’ or ‘sumifs’) so that as you reconcile a transaction, you check the relevant checkbox. Commented Jul 7, 2022 at 4:18

I think, with Google Sheets you are out of luck, however the feature of "Trace Precedents and Trace Dependents" exists in MS Excel.

T!C1 formula ... =ARRAYFORMULA( vlookup(B1:B50,S!A1:C50,3,false) )

Conditional formatting for column A,B and C ... =C1="Y"

S!A1 formula ... =query(T!A1:B50,"select B, sum(A) group by B order by B asc")

Sheet T 1 a 2 b 3 c 4 d 5 a 6 b 7 c 8 d ...etc (ie. amount / vendor names)

Sheet S Sum a 6 b 8 c 10 d 12

Place a Y in column C on sheet S 😁 ... and then switch to Sheet T

Worked for these example sheets!

• PS. The data in the table should be just in Column A and B 🙂... and not across! Commented Dec 9, 2022 at 12:18
• If you have a payment date column in Column C, and in D1 the formula... Commented Dec 10, 2022 at 18:59
• With a payment date in Column C, and in D1 ... =arrayformula( concat(right(C1:C1000,4),mid(C1:C1000,3,3)) ) ... and in E1 ... =ARRAYFORMULA( concat(D1:D1000,B1:B1000) ) ... and in F1 ... =arrayformula (vlookup(E1:E1000,S!A1:E1000,5,false)) ... then the sum query can be changed to ... =query(T!A1:E50,"select E, B, D, sum(A) where A>0 group by E,B,D order by E asc") ... and the conditions changed to ... =F1="Y" ... then each Y shows payments in T for that month 😁 Commented Dec 10, 2022 at 19:19