Hi Gurus and Excel Geniuses,
This is probably a very easy for most, so I apologize in advance for the long question, but here goes:
Overview: I am trying to create an "expense summary" worksheet, that lists all cash and invoice payments for my office. This would ideally, return all values recorded from various worksheets into one single list.
More specifically: I am trying to create a multifaceted return column (or perhaps several - depending on where I'm going with this) where expense entries recorded on two other worksheets (one is an internal worksheet from the same workbook, and the other is an external worksheet from another workbook) are automatically listed on the "multifaceted return column" when an employee enters a value into the other two linked worksheets.
The names of the worksheets are as follows:
Expense Summary from workbook titled Invoices (/Users/gregorymichelson/Downloads/[Invoices.xlsx]ExpenseSummary)
Data Input from workbook titled titled Invoices
(/Users/gregorymichelson/Downloads/[Invoices.xlsx]Data Input)
Petty Cash Available from workbook titled PETTY CASH PROGRAM LEDGER-5
(/Users/gregorymichelson/Downloads/[PETTY CASH PROGRAM LEDGER-5.xlsx]Petty Cash Available)
The Expense Summary is for my personal vieweing while my staff only make entries in one or two of the other two worksheets (i.e., Data Input or Petty Cash Available).
The Formula Thus Far:
Currently, on the Expense Summary worksheet, I have succesfully linked values from the Data Input worksheet, as follows:
A4=IF('Data Input'!A4<>"",'Data Input'!A4,"")
So, every time one of my staff inputs information into cell A4 in the Data Input worksheet (this woud be the date of a particular expense), it is automatically returned to cell A4 in my Expense Summary worksheet.
Due to the fact that we often receive/record invoices out of order in terms of dates, every now and then I manually sort the returned date values in Expense Summary so that all dates become organized in ascending chronological order (side note - if someone knows a way to automatically return the values in correct order without using the manual sorting function, please teach me how!).
Where I'm Stuck:
We also frequently use petty cash, and respectively my staff record their transactions on a petty cash ledger, or the Petty Cash Available worksheet.
I want to simulatenously link the values from both the Petty Cash Available and Data Input worksheets so that they are all returned in list form in my Expense Summary worksheet to the next availabe (blank) cell accordingly. This should ideally accommodate for multiple transactions occurring on the same date.
For example, if someone makes a petty cash transaction and enters a date value in cell A4 of the Petty Cash Available worksheet, I want it to be automatically returned to the next available cell in column A in my Expense Summary worksheet (let's say cell A36). If someone then pays an invoice and records it on the Data Input worksheet, I want my Expense Summary worksheet to display that date value in the next available cell in column A (this would then be cell A37)... and vice versa.
Attached is a screenshots of the three worksheets for further reference. The top two (in L to R order) are Data Input and Petty Cash Available, and the bottom worksheet is the Expense Summary where I want values simulatenously linked to)
Thanks!
- Greg
