Hello everyone. My name is Lenny and I’m a newb here. Looks like you have a great forum so; I thought this would be a good place to possibly get some answers to a problem I have encountered. I’ve seen a few posts here that are close to my needs but don’t encompass the entire scope of my dilemma.

I have been using Excel for years to maintain the books for my small business. As my business has grown, data entry to maintain these records has become a very lengthy and time consuming process. So, I decided that it is time to give my workbook an overhaul. To, sum up my objective, I have been attempting to streamline the data entry process by having numerous tables in other tabs automatically re-calculate & update based on the addition of data in only two worksheets; specifically, Purchase Orders and Sales Invoices.

I have been trying to figure this out on my own but I have encountered two major obstacles which I am having difficulties with. The first is the fact that the Purchase Order (PO’s) & Sales Invoices are not in a logical table or list structure as they are generated on an as needed basis. This makes cell reference for lookup comparisons difficult. Second, due to the limitation or functions like “Lookup”, “Search”, “Find”, etc., I can’t figure out how to make Excel find the required data on one sheet & pull partial columns for that line to populate corresponding lines on several sheets.
I think that this may require, at the very least, nesting of multiple functions (e.g. “IF”, “VLOOKUP”, etc) and, may even require macros be written. I have done very basic nesting of the “IF” Function with “AND” and “OR” Functions but, I just can’t seem to wrap my head around this one. Also, I have inserted basic predefined macros, like check-boxes, etc, into worksheets but I am not familiar with Visual Basic.

Rather than confuse things here with details, I have detailed the scenarios in the attached workbook. Please refer to this workbook for all the gory details. A few things to keep in mind while looking at my workbook:
- My current version of Excel is 2007
- In an attempt to provide a clean-slate, so-to-speak, for this post, I quickly drew up most of the sheets with very little data. Most of the sheets are in a simple list format with minimal functions
and references. The first two sheets, however, have been copied from my actual business workbook. These two sheets do contain tables with a few basic nested functions. I also removed any
sensitive data from the tables to make them flow better with the examples on my other sheets.
- All of my tables and sheets in my actual workbook serve to ultimately auto-calculate and populate an Income Statement sheet which has not been included in this workbook.
- The sheets in this workbook are arranged with Expense Tabs to the left and Income Tabs to the right. I have provided more details for the Expense side than I have for the Income side because
the Income side is basically a mirror of the Expense side.
- The “Receivable PN Key” Tab (close to the middle tabs) should appear when you first open the workbook but, if it doesn’t, I recommend you start there. This tab has most of the details which
are a bit complicated and confusing. If you get confused with my descriptions, simply refer to the associated tab(s) for a graphical example to clear things up.
Forum Post Attachment.xlsx
Thanks guys and let me know if you have any questions.
Lenny