Nesting Function & Possibly Macros Needed?

[Falcon459]

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

[jhren]

Hi, Lenny.

Welcome to the forum.

I realize you are a newb here, so I'll give you a little advice. Break your task down into more basic elements and initiate a new thread for each element as the one before is solved. The reason I say this is because no one here wants to take the time to solve all the elements required to make your workbook functional to the desired level. Advice given here offers little reward, so their time involved matches in most cases. I noticed your post had 28 views and zero replies when I pulled it up. IMO, that's a result of several tasks rolled up into one post.

[Falcon459]

Hi jhren;
Thanks for the welcome and the advice. Although it is not quite what I was hoping to hear, I understand what you are saying. I kind of thought this would happen when I posted but, I was klinging to the idea of sticking with Excel for my bookkeeping. This was my last-ditch effort for this dream. I will consider following your advice but I see this as a long drawn-out process. And unfortunately, I have already spent way to much time on this project and frankly, I can't justify yet another lengthy process. It may be time for me to finally consider additional options. Thanks again for the frank but I'm sure, accurate advice.
Lenny

[Tsjallie]

You don't need to drop Excel. It's just not a matter of simply changing some formulas. Formulas like vlookup need structure and that's what your workbook is lacking (and you're not alone on that). Just start with some design: what are the core datasets (probably customers, articles and alike) and turn these into Excel tables. Then figure out their relationships (orders, invoices and alike) and make tables for these too. This will give a structured base from which you can create any fancy looking sheet you like. And it's all in your workbook already.

[Falcon459]

Thanks for the response & advice Tsjallie but as I mentioned in my original post, "- 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". My attachment here does not reflect my actual workbook, which does have structure. I have thought about this extensively but I don't posess the skill & experince with Excel to hash out the complexities on my own.
Thanks again
Lenny

[Tsjallie]

Ok, clear.
Then I think it's best you try to upload a portion of the workbook with its original structure. Just truncate any rows beyond, say, row 100. And don't forget to anonomize any sensitive data. From that point we could resolve any specific issues one by one. Starting with the lookups I guess?
Would that be a way to go for you?