Author Topic: Is this possible in excel  (Read 4051 times)

Offline inkman996

  • !!!
  • Gonzo Member
  • ******
  • Posts: 3760
Is this possible in excel
« on: February 04, 2014, 03:22:26 PM »
I am in the process of creating a master order sheet for a large city fire department. It is very complex with dozens of different items and sizes, and specific titles involved. My goal is to have a main sheet that the person in charge in can add the persons name and select each item and size, from there everything is tallied with formulas so on a separate page item sizes are tallied and that can be sent to us. In the past we were always given hundreds of individual hand written sheets for each person and it never ended well. We have requested they come up with a better system for us. The task has fallen on me naturally. This is all fine I am pretty good with excel and a lot of the formulas but i am self taught.

There is one thing holding me up right now, when a fire fighter, paramedic, battalion chief, LT, etc. etc. orders a T-shirt the back of that shirts has their title. I need to tally up each of these titles, so is there a way to add a selection type button or radio button in excel that the person doing all the inputting can simply select.

Simply put, person types in persons name in the first field, next field is union tees which has a list of sizes. Person puts a qty in the correct size. Next field (fields) is the titles which the person has to select the correct one. Not only do I need that added to the tally sheet but also correlated to the size the person chose. This way when the tally is submitted to us The list will be for example: Navy Union Firefighter xl 57 L 30 2X 32 etc.


Does any of this make sense? God I hope so.
"No man is an island"


Offline ericheartsu

  • Gonzo Member
  • ******
  • Posts: 3539
Re: Is this possible in excel
« Reply #1 on: February 04, 2014, 03:45:37 PM »
we went through something like this, recently, but on a much smaller scale. We had a friend actually code us a website that the manager just go in and type in what he wanted. if you want pm me and i'll send you the details!
Night Owls
Waterbased screen printing and promo products.
www.nightowlsprint.com 281.741.7285

Offline Sbrem

  • Ludicrous Speed Member
  • *******
  • Posts: 6055
Re: Is this possible in excel
« Reply #2 on: February 04, 2014, 04:10:00 PM »
I've done a similar thing before, in FileMaker. I roughed it all out first on paper, then started building... you just have to carefully think it through. And, to play the flute, just blow in this end, and move your fingers on the other...

Steve
I made a mistake once; I thought I was wrong about something; I wasn't

Offline inkman996

  • !!!
  • Gonzo Member
  • ******
  • Posts: 3760
Re: Is this possible in excel
« Reply #3 on: February 04, 2014, 05:13:06 PM »
I've done a similar thing before, in FileMaker. I roughed it all out first on paper, then started building... you just have to carefully think it through. And, to play the flute, just blow in this end, and move your fingers on the other...

Steve

But is there a way to add in a selection box that is added to a total on a different page? Obviously it would require a macro of some sort and,that is where I fail miserably with excel.

I have all the page layouts roughed in. All items are roughed in and the easier formulations are done. It's just these last couple of hurdles that are slowing me down.
"No man is an island"

Offline cbjamel

  • Gonzo Member
  • ******
  • Posts: 1093
Re: Is this possible in excel
« Reply #4 on: February 04, 2014, 05:16:10 PM »
I am in the process of creating a master order sheet for a large city fire department. It is very complex with dozens of different items and sizes, and specific titles involved. My goal is to have a main sheet that the person in charge in can add the persons name and select each item and size, from there everything is tallied with formulas so on a separate page item sizes are tallied and that can be sent to us. In the past we were always given hundreds of individual hand written sheets for each person and it never ended well. We have requested they come up with a better system for us. The task has fallen on me naturally. This is all fine I am pretty good with excel and a lot of the formulas but i am self taught.

There is one thing holding me up right now, when a fire fighter, paramedic, battalion chief, LT, etc. etc. orders a T-shirt the back of that shirts has their title. I need to tally up each of these titles, so is there a way to add a selection type button or radio button in excel that the person doing all the inputting can simply select.

Simply put, person types in persons name in the first field, next field is union tees which has a list of sizes. Person puts a qty in the correct size. Next field (fields) is the titles which the person has to select the correct one. Not only do I need that added to the tally sheet but also correlated to the size the person chose. This way when the tally is submitted to us The list will be for example: Navy Union Firefighter xl 57 L 30 2X 32 etc.


Does any of this make sense? God I hope so.

Not quite what you wanted but try this. Just change extention to .xlsx from zip.
Shane

Offline Sbrem

  • Ludicrous Speed Member
  • *******
  • Posts: 6055
Re: Is this possible in excel
« Reply #5 on: February 04, 2014, 05:42:12 PM »
I've done a similar thing before, in FileMaker. I roughed it all out first on paper, then started building... you just have to carefully think it through. And, to play the flute, just blow in this end, and move your fingers on the other...

Steve

But is there a way to add in a selection box that is added to a total on a different page? Obviously it would require a macro of some sort and,that is where I fail miserably with excel.

I have all the page layouts roughed in. All items are roughed in and the easier formulations are done. It's just these last couple of hurdles that are slowing me down.

Yes, you can. When I did it for a huge customer about 20 years ago, the main layout was the database that had all the info, then other layouts would take the info from the main one. I'm not sure about doing it in Excel, but I don't see why not.

Steve
I made a mistake once; I thought I was wrong about something; I wasn't

Offline sweetts

  • !!!
  • Gonzo Member
  • ******
  • Posts: 1768
  • Simplicity is the ultimate sophistication DUH
Re: Is this possible in excel
« Reply #6 on: February 05, 2014, 10:33:33 PM »
What about a form on google docs that would be crazy simple to build, you could save each form and have a separate tally sheet, best of all you just supply the customer with the drive link and when they are done you pull it up and your done


Sent from my iPad using Tapatalk
RT Screen Designs
Willowick Ohio
www.rtscreendesigns.com

Offline inkman996

  • !!!
  • Gonzo Member
  • ******
  • Posts: 3760
Re: Is this possible in excel
« Reply #7 on: February 06, 2014, 10:47:14 AM »
What about a form on google docs that would be crazy simple to build, you could save each form and have a separate tally sheet, best of all you just supply the customer with the drive link and when they are done you pull it up and your done


Sent from my iPad using Tapatalk

Google Docs is not possible, the city has a limited internet policy. They also have no quarter master for over 700 people which means we end up doing most of the organizing for them. It is worth it regardless for the orders they do submit once a month. Also did I mention each person pays individually, nothing is more fun than receiving several hundred checks at one time.
"No man is an island"

Offline Sparkie

  • Full Member
  • ***
  • Posts: 129
Re: Is this possible in excel
« Reply #8 on: February 06, 2014, 10:53:35 AM »
See if this gets you started. I use Open Office but it should work in Excel.
order form test

There are 2 sheets. The Order Form sheet is where the custy enters the info. The Items sheet is for you to add your item details and get a count. Let me know if you need assistance in setting it for your use.

- Mark

Offline inkman996

  • !!!
  • Gonzo Member
  • ******
  • Posts: 3760
Re: Is this possible in excel
« Reply #9 on: February 06, 2014, 12:01:15 PM »
See if this gets you started. I use Open Office but it should work in Excel.
order form test

There are 2 sheets. The Order Form sheet is where the custy enters the info. The Items sheet is for you to add your item details and get a count. Let me know if you need assistance in setting it for your use.


Sparkie pretty much what I have already done, tho your drop down list is a good idea I will use. See the pics below to understand where I am at so far.

Keep in mind there is 27 items in total that also have sizes from Small up to 3X. There is also 6 different hat styles most have sizes, bags, patches, stickers and drink ware. In total breaking everything down with sizes there is hundreds of items to organize. Below is screen shots of some of the pages so far, each age is specific to printing, embroidery or non apparel. I been using the =sum function on a tally page. This works great for getting the totals of a product and size quantities for when its time to order from the mills, what I am not able to figure out yet is how to break down the amount of lets say size medium IAFF logo that gets Fire Fighter, or Battalion Chief etc. etc. Also since we get paid directly from each person I cannot figure out how to sum up their complete purchase on a separate page so we do not have to add up each person manually. I need to figure out how to tie a quantity to a price amount and have totaled with all other products as well.

Still a work in progress, still have to add several more titles and items. BUt it is close to done for the most part minus the wish list.
"No man is an island"

Offline Sparkie

  • Full Member
  • ***
  • Posts: 129
Re: Is this possible in excel
« Reply #10 on: February 06, 2014, 09:17:05 PM »
I'm not extremely experienced in using them, but Pivot Tables are very useful.
The shaded area is the data entry
The section below is the Pivot Table created by Excel (this sample is Open Office) where you can filter and sort as well as choose other options and layouts.

- Mark

Online Evo

  • Hero Member
  • *****
  • Posts: 951
  • Anything is possible.
Re: Is this possible in excel
« Reply #11 on: February 06, 2014, 10:29:44 PM »
If you have a cell where you want to narrow down the choices of what can be entered into the cell, you use LISTS.

Basically somewhere down in the worksheet (or on another worksheet), like way down in the hundreds, you enter all the possible choices in descending order in the column. Then you specify that set of cells as the list range for the given cell. You can lock and hide the list cells so they can't be modified.

When I worked in imports for an  outdoor clothing company this is how we made forms for the design people to fill out for design specifications for customs classifications. Worked like a charm and kept them from entering information that should not have been a possible choice for a given cell.

There is scarcely anything in the world that some man cannot make a little worse, and sell a little more cheaply. The person who buys on price alone is this man's lawful prey.
John Ruskin (1819 - 1900)

Online Evo

  • Hero Member
  • *****
  • Posts: 951
  • Anything is possible.
There is scarcely anything in the world that some man cannot make a little worse, and sell a little more cheaply. The person who buys on price alone is this man's lawful prey.
John Ruskin (1819 - 1900)

Online Evo

  • Hero Member
  • *****
  • Posts: 951
  • Anything is possible.
Re: Is this possible in excel
« Reply #13 on: February 06, 2014, 10:34:42 PM »
So the above will work for the titles. You need to aggregate the info from all the cells by using CONCATENATE.


http://office.microsoft.com/en-us/excel-help/concatenate-function-HP010062562.aspx
There is scarcely anything in the world that some man cannot make a little worse, and sell a little more cheaply. The person who buys on price alone is this man's lawful prey.
John Ruskin (1819 - 1900)

Offline inkman996

  • !!!
  • Gonzo Member
  • ******
  • Posts: 3760
Re: Is this possible in excel
« Reply #14 on: February 07, 2014, 10:47:13 AM »
Sparkie you are amazing! I looked up Pivot Tool and watched some tuts and wow what an amazing tool. That has go to the the most useful tool I ever seen in excel yet. This is perfect for what I need and will use it for sure thanks bro!

"No man is an island"