0

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?

1
  • 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.
    – Tedinoz
    Commented Jul 7, 2022 at 4:18

2 Answers 2

-1

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

-1

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!

3
  • PS. The data in the table should be just in Column A and B 🙂... and not across!
    – Geoff F
    Commented Dec 9, 2022 at 12:18
  • If you have a payment date column in Column C, and in D1 the formula...
    – Geoff F
    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 😁
    – Geoff F
    Commented Dec 10, 2022 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.