Forum Discussion

BlueCollarVending's avatar
BlueCollarVending
Copper Contributor
Oct 12, 2023

[SOLVED] Expense Report

So i have a expense report im working on and i need help with some formulas. The report is multiple sheets,  1 sheet is an overview 1 is the main receipt entry  2 are for more detailed entries (ba...
excel
Formulas and Functions
BlueCollarVending's avatar
BlueCollarVending
Copper Contributor
Oct 16, 2023
im also trying to set it up so that when it generates to the details tab, it will leave a row blank between each entry so i can come back and add all the details from that purchase in and then group the details under the purchase
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 16, 2023
    Which columns in the Purchase Report sheet correspond with the "Purchase Details" and "Total" fields?
    • BlueCollarVending's avatar
      BlueCollarVending
      Copper Contributor
      Oct 16, 2023
      I'm not sure i follow what you are asking. If you are referring to the "Inventory and Other" tabs, those details i want to come from the purchase report automatically. As far as the "Total" columns on the Purchase Report, That is generated automatically from the report page.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Oct 16, 2023

        BlueCollarVending 

        You can use this formula to pull from the main sheet:

        =LET(
    filtered, FILTER(TBLPURCHASEREPORT, TBLPURCHASEREPORT[TYPE] = "Inventory"),
    CHOOSECOLS(filtered, 2, 3, 6, 4)
)

        For the "other" sheet, it's the same formula with a different criteria.

         

        I don't recommend adding blank rows after each record. The issue with that is FILTER wants to spill results. If we leave blank rows for entry then we take spilling off the board and must use older methods (INDEX - SMALL - IF) array that are not efficient. 

Resources