Automatically Populate Cells Across Workbook Sheets

[Shirley8679]

Hi everyone, I am going to give this my best shot at explaining what I am trying to accomplish here! I dont have all the Excel terminology down so bear with me lol. My husband owns a Butcher shop and took on a growing Alaska account and I have been thrown into the paperwork side of this account! This is our second year and it gets crazy for a month or 2! Last year was my learning year as I am by no means an expert with Excel.

I created an order form to send out to customers. In the same workbook, I created an invoice and receipt! Is there any way to link these sheets or have one sheet communicate with another. For example, if the customer enters a value in cell E16 on product price list sheet (sheet 1), sheet 2 (invoice) will automatically populate with the item description 'PRIME RIB' , the # lbs they entered wanting and the price of 374.75 .
So if they enter any value in row E under quantity, it will automatically populate the invoice sheet rows B,C,D, and E with description, quantity and total?
What I did last year was alot of work! I know it's going to be a good amount of work either way but I know there has to be a way to make it a little easier! Even if I have to recreate my invoice and receipt, that's ok! I find it enjoyable. I just don't know formulas!
I hope my question made sense, I am horrible at explaining this type of thing!
Thanks for any and all feedback I get! I attached a couple photos to give an idea of what I have already done! Hopefully it helps make sense of my question :beaming_face_with_smiling_eyes::beaming_face_with_smiling_eyes:
One last quick question. I woukd also like to know how to make certain cells mandatory! I want the customer to be required to fill in the vessel name, contact name, phone # etc,.
THANK YOU

Shirley Davis

[#2]

It should be fairly easy to do most of what you want, especially bring things over from the order to the invoice. Would it be possible for you to attach the actual spreadsheet? Working from an image requires us to re-create what you've already got....and it doesn't look as if you have any confidential info in there (if you do, render it anonymous before posting).

[#3]

Yes, I can absolutely do that! I have to get ready for work in just a few minutes here so I will do that as soon as I get home this evening! Thank you so much for your help! I posted this question on my cell phone, I didn't even think to do it from my desktop and just attach the spreadsheet! Thank you :smiling_face_with_smiling_eyes:

[#4]

Attached is the Excel workbook. :)  Thank you for your help! @mathetes 

[#5 · Accepted solution]

@Shirley8679 

I have started you on the road to getting the invoice that lists only the items for which a quantity has been entered on your Product Price List sheet. I used the new FILTER function, which will only work for you if you have the most current version of Excel. It is so powerful and easy to use that I highly recommend updating your system if you don't have that.

This does NOT address everything you asked for, but I think it gets at the most important part. I'd be happy to do more if you'd like.....but first take a look at this and what I've written both here and on the workbook itself. I did not try to make it a finished product; my goal was to introduce you to the FILTER function, which I think will go a long way toward resolving what you need. There still is work to do, however. But you clearly have learned a lot, even if you don't feel all that confident yet. And the best way to keep learning, is to keep working at it.

The other observation I'd make is that you have (in my opinion) made a classical mistake: you've worked to make your workbook "pretty" before developing the needed functionality. As a result, I was never able to get the FILTER function to work in your original Invoice sheet. I created the start of another. My recommendation is to get a workbook like this functioning first, then make it attractive to the eye. I'm leaving that last part to you.

A few additional recommendations:

• You'll note that occasionally "0" appears under the heading "Description"--that's because you've left it blank. Yet that was the header you were using on your original form....I'd would just suggest that you should put content into that column on your "Price List" sheet, or modify the whole thing so that "Item" is a more complete description and eliminate the secondary.
• You'll see I removed the sub=headings from the Product Price List, and replaced them by an additional column with an abbreviation (e.g., Bf for Beef). In a database, it's generally far better NOT to use subheadings; if the categorization is important for some reason--which it could be--if, for example, the description "Lunch Meat" appeared under Beef and Pork and Chicken, then you'd want to make sure that that designation is also part of the array selected by FILTER.

Here is a link to a very useful video that Microsoft has created to introduce people to FILTER and some other new functions you might find useful. It's where I learned of these functions, in fact, just a few months ago. https://www.youtube.com/watch?v=9I9DtFOVPIg

[#6]

@mathetes 

 It has been a remarkably busy couple days, so I am just now getting to this. Thank you so much for your time and help.

I am going to look more closely at what you have done and try and wrap my brain around what you have explained. I am excited to learn more of this and I will be watching the YouTube video you attached. Thank you for that!! 

I pay monthly for Office 365 family, so I am not sure if it is the latest version. I do however see a fx (insert function) button on the far-left side of the ribbon under the formulas tab. Is this the button you are talking about?

Shirley Davis

[#7]

If you have the Office 365 subscription, then you do have the most current version, and FILTER will work. I don't recall (nor could I find) any reference to a "button" in what I wrote... Anyway, see if what I gave you is what you were looking for, watch the video...and come back with more questions if you need further help.