This is my first ever post on this forum do let me know if I have posted incorrectly ?
Context: I am working in 3 sheets titled Expenses, Sales, Master. I would like the Master list to be automatically filled out whenever I enter either an Expense or a Sale row.
Issue/Problem: Currently it is semi working, I can go into the Sales sheet, put in a Date and Amount and it will automatically appear in the Master sheet. I can do the same with Expenses as well but each row in Master corresponds with a row in Sales and Expenses. This means that now that I have recorded my expense on July 11th 2021, if I enter a sale on row 15, it will overide the info in the Expense sheet due to IF priority (see formula below).
The formula for the cell on the Master sheet A16 is: =IF(ISBLANK(SALES!A15), IF(ISBLANK(EXPENSES!A2),"",EXPENSES!A2), SALES!A15)
This formula references a potential Date on the corresponding row in Sales (row 15), if it is empty, then it will go to the Expenses sheet and look for a Date on row 2, if it's empty then it will record nothing. If I have both a sale and expense on the same referenced rows, the expense values will just be ignored due to the nature of my formula. I am wondering if there is a function in Excel that will allow me to have the Master list just record transactions when I put them in either Sales or Expenses without having to make the Master sheet reference specific rows and running into issues when there are sales and expenses on the same referenced rows.
The Expense and Sales sheets look like this:
View attachment 42835View attachment 42837
The Master sheet looks like this:
Thanks in advance!
Context: I am working in 3 sheets titled Expenses, Sales, Master. I would like the Master list to be automatically filled out whenever I enter either an Expense or a Sale row.
Issue/Problem: Currently it is semi working, I can go into the Sales sheet, put in a Date and Amount and it will automatically appear in the Master sheet. I can do the same with Expenses as well but each row in Master corresponds with a row in Sales and Expenses. This means that now that I have recorded my expense on July 11th 2021, if I enter a sale on row 15, it will overide the info in the Expense sheet due to IF priority (see formula below).
The formula for the cell on the Master sheet A16 is: =IF(ISBLANK(SALES!A15), IF(ISBLANK(EXPENSES!A2),"",EXPENSES!A2), SALES!A15)
This formula references a potential Date on the corresponding row in Sales (row 15), if it is empty, then it will go to the Expenses sheet and look for a Date on row 2, if it's empty then it will record nothing. If I have both a sale and expense on the same referenced rows, the expense values will just be ignored due to the nature of my formula. I am wondering if there is a function in Excel that will allow me to have the Master list just record transactions when I put them in either Sales or Expenses without having to make the Master sheet reference specific rows and running into issues when there are sales and expenses on the same referenced rows.
The Expense and Sales sheets look like this:
View attachment 42835View attachment 42837
The Master sheet looks like this:
Thanks in advance!
