Referencing Cells From Two Other Sheets to a Master Sheet And Taking the First Available Value

sjbfan

New Member
Joined
Jul 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
1626315636344.png


Thanks in advance!
 

Attachments

  • 1626315667194.png
    1626315667194.png
    71.5 KB · Views: 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

sjbfan

New Member
Joined
Jul 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Seems there was an issue uploading the Expense and Sales images so here they are:

1626316765565.png
1626316786288.png
 

Forum statistics

Threads
1,148,159
Messages
5,745,111
Members
423,924
Latest member
Gazzat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top