[SOLVED] Expense Report

[BlueCollarVending]

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 (based on the receipt type)

1 Milage Log

1 settings

So what i am trying to do is, on my main receipt entry sheet, i enter the receipt basic info. Date, Receipt number, supplier, type, pretax amount, tax and then totals are automatic.

on the 2 detailed sheets i would like to reference the main sheet and have it pull info from that sheet (based on "type") and have it auto populate the info there. then i would go in and add the details based on the receipt ( ie. all the items on the purchase). Then i would "group" the purchase details under the auto populated info.

I guess my first question would be, If it were to set this up, would it mess up my formulas if i add rows under the auto populated info and group them?

I understand this is probably a large request and a lot of formulas. Any advice or direction would be awesome.

[#2]

@BlueCollarVending 

It is possible to set up your expense report in a way that allows you to auto-populate information on the detailed sheets based on the type you have entered in the main receipt entry. Additionally, you can group rows without affecting your formulas. Here is a general approach to achieve this:

Main Receipt Entry Sheet:

1. Create a table for entering your receipt information. Include columns like Date, Receipt Number, Supplier, Type, Pre-tax Amount, Tax, and Totals.
2. Use Excel's data validation or drop-down lists to ensure you select predefined receipt types consistently.
3. Use formulas to calculate totals, like =Pre-tax Amount + Tax.

Detailed Sheets:

1. For each detailed sheet (based on receipt type), create a table similar to the main receipt entry, including a column for Type.
2. Use formulas like VLOOKUP or INDEX/MATCH to auto-populate information from the Main Receipt Entry Sheet based on the Type.

For example, if your detailed sheet is for "Type A" receipts, you could use a formula like this to retrieve the supplier's name:

=VLOOKUP("Type A", 'Main Receipt Entry'!A2:G100, 3, FALSE)

1. Group rows as needed to add details under the auto-populated info. Grouping does not affect formulas, so you can expand and collapse as necessary.

The key to making this work is using a consistent structure and cell references in your formulas. If you add rows under the auto-populated info and ensure the references in your formulas are dynamic (e.g., do not use absolute cell references like A2), then your formulas should continue to work as expected.

Remember that creating a complex report with multiple sheets and data linking can get intricate, and it might be helpful to plan out the structure and design before implementing it fully. The exact structure and formulas will depend on your specific requirements and the complexity of your expense report.The text, steps and functions were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

I tried the formula you posted and it worked but only for a specific cell. And it doesnt allow me to filter the Main PURCHASE REPORT by category and input all the essential data with the choose function. Also what would a formula be to make it autoskip a row?

I asked ChatGPT and this is what it sent. Took a bit to get there but....

=IFERROR(CHOOSE({1,2},INDEX('Purchase Report'!A:A,SMALL(IF('Purchase Report'!D:D="Your_Category",ROW('Purchase Report'!D:D)-ROW('Purchase Report'!D1)+1),ROW(INDIRECT("1:"&ROWS('Purchase Report'!D:D))))),INDEX('Purchase Report'!C:C,SMALL(IF('Purchase Report'!D:D="Your_Category",ROW('Purchase Report'!D:D)-ROW('Purchase Report'!D1)+1),ROW(INDIRECT("1:"&ROWS('Purchase Report'!D:D))))))), "")

The problem is, is it keeps causing my excel to crash. Plus i dont really understand any of this. I would like to understand some of the code so i could figure out how to make it work properly. 

[#4]

=IFERROR(INDEX('Purchase Report'!A:A, MATCH(1, ('Purchase Report'!D:D="Your_Category")*('Purchase Report'!A:A<>""), 0)), "")

[#5]

@BlueCollarVending 

This formula is crashing the application because it's arranged as an old school ctrl+shift+enter array that's concerned with entire columns and using INDIRECT (a volatile function).  If you're using Excel 365, an elegant solution is available.

Do you happen to have an anonymized sample of the workbook you can share?

[#6]

@Patrick2788 

This is the only thing i can think to do to get a copy over

[#7]

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

[#8]

Which columns in the Purchase Report sheet correspond with the "Purchase Details" and "Total" fields?

[#9]

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.

[#10]

@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.