Create two corresponding worksheets for daily inventory in Numbers for iPad...

[rlcox]

I was hoping someone could help at least point me in the right direction with this idea. Let me first say that this is not an attempt to get a free job out of the day...part of the reason for doing this is to learn something new. I am fairly computer literate but to too much beyond the basic spreadsheet functions between a few cells on the same worksheet.

So, my idea is this...I am making a spreadsheet to resemble the liquor shelf at the store. You touch the desired product label that needs a quantity sold input. Some will get input daily, other slower products won't see action for who knows how long, meaning it would stay a blank cell for the quantity sold that day. The products that do get input for the day, I want that value and the corresponding product to be pasted to the next available cell into a second worksheet/tab. The purpose of this is to print worksheet two and it be only the products that have a quantity sold for that day for taking to the stock room and calculating the total sales of the day.

Worksheet one has all the product listed and when touching the cell of a product you would get a popup with the values of 1-10 (there's no more than 10 bottles on the shelf of anything) and so if the cell for Jack Daniels received the first input of 3 bottles sold, it would copy and paste the price of JD ($14.50) into A1 of worksheet/tab 2, along with the quantity sold input into B1, "Jack Daniels" into C1, and last being the size (50ml, 200ml, 375ml, 750ml, & Liter) into D1. The next product that receives input on sheet 1 would do the same on row 2, the next into row three and they just follow down through the next empty and available row. If a product doesn't get input then it stays empty and therefore nothing gets copied over to sheet 2 for printing. This way the "stock needed" list isn't the full length of the 60-70 different products and half of them being empty anyway. Finally, once completed, an easy way to erase/clear all quantity inputs for that day on sheet 1 and completely clear sheet two except for the formula to calculate total sales at the end of the column, therefore having a clean start the next day.

I hope this is somewhat clear. I have a tendency to ramble in an effort to thoroughly explain. If there is anything I missed please let me know. I don't know if this is even possible with Numbers, or even DocToGo...or perhaps the complexity of the equations need to be done in Excel and then import the worksheet into Numbers and put the finishing touches on it that way. If I a, shooting for the stars then so be it, but I don't know without asking.

Again...for whoever may choose this challenge, this is intended to be a learning experience in addition to benefiting the family business. I'm not looking for someone to devise and post a half page of VB code for me to just C&P and me not learn a thing about how or why. Whether you want to take me step by step, or just point me to a good tutorial...whatever works. I just know my research on the Internet isn't getting me anywhere, because all they do is post a bunch of code that I can't decipher anyway. If I can just get started with a basic template or something then hopefully I could scale it as needed.

Sorry for the lengthy post but I just wanted to make it as clear as possible.

--Roger

 

[twerppoet]

Well, this is just opinion, since I haven't tried to tackle the problems you proposed (other than in my head):

You might be able to kludge something together that would resemble your requirements; but it would be ugly and very finicky about proper input methods. That is if you can do it at all, and I'm far from sure that it can be done.

To do this elegantly (and make it easy to use) you would need the ability to write macros. Numbers does not have the ability to create macros; nor can it import and run a macro. As far as I know, none of the Office/spreadsheet apps on the iPad can do Macros. If I'm wrong, someone please correct me.

Short of finding an app on the App Store (it probably exists), or writing your own app (and that would be another learning experience), I think the only way to do this would be with one of the new cloud desktop services. Both OnLive and CloudOn provide full versions of Excel, macros and all. Pretty anything you can do in Excel could be run on the iPad, as long as you have a solid, reasonably fast internet connection.

The two services are very new and a bit wobbly, but the basic accounts are free.

 

[hpulmer]

Interested in a simple solution for the liquor stuff without learning how to program it through - though I would like to help with the excel/Numbers and so on I am actually a NOOB on those ;o) but I find my way around the problems

Question is if you have the iPad all the time at the store or just in the evenings for inventory making... there are apps for inventory making/ keeping - might even be with barcode scanning etc.
Or how about a shopping list? GroceryGadget for example (with barcode scanner)
If you have it always there you could enter the stuff sold into a google docs form...

 

[twerppoet]

The idea intrigues me though. I may have to do some experiments. If I do, and find out anything worthwhile I'll come back here and let you know.

It all depends on whether it keeps me awake all night, as problems sometimes do.

The direction I'm thinking is that you need some kind of variable to track cells with values in them. There is no direct way of doing this, but you might be able to use a combination of statistic, indirect addressing, and string functions to create a text string composed those cells addresses. Something like "A1:A5:A7". Then you could write a formula for each cell in the second worksheet that would parse/extract the address they need for each non-empty cell and pull the needed value.

I've tried similar (if less ambitious) spreadsheets before with no success: The sole recommendation of this approach is that I haven't proven it won't work, yet.

 

[rlcox]

I appreciate the enthusiasm versus other places that are anxious to berate you for even thinking of some thing so ludicrous. The reason for doing it this way is for the sake of the old fart (I say that light heartedly) that does it on a daily basis on pen and paper...I just do the weekend shift. This is a joint venture between me and the other business partners to get him to move from pen and paper. He is 70-something and has never even turned on a computer, so it's all about baby steps for him. The purpose for doing it this way is to emulate the way he does it on paper and resemble him looking at the shelf and the end results being just like when he writes it down. Me being a glutton for punishment, in addition to a computer tech and overall tech geek, I'm always up for a new challenge.

On the contrary though, these baby steps for him and I don't need to be more labor some than just doing it on paper, which tends to happen a lot when trying to implement technology in place of pen and paper. An example being a calendar on a smartphone...I still do it on my phone but in reality one could whip out their pocket calendar, open to the desired date, and write the appointment in faster than what it takes to put an event in the phone. But anyway...

That being said...not really knowing jack about complex formulas or VB, beyond calculating a few cells...what little I do know (just enough to be dangerous) from a simple as possible and logistical viewpoint...I thought that was kind of one of the purposes of the IF/THEN function. So in simple English "if A1contains a value >0, then copy and paste that value into the first available cell of the appropriate column in sheet 2" then have it just bump down to the next product and so on until.

So while we could meander from the beaten path a little, if only for the sake of learning, that fine...but I just wanted you to understand the reason for doing this versus just acquiring an app. Now...if someone is aware of an app that is super customizable and not $30-40, then please do share, if it's simple enough in terms of how to operate it then maybe we could get the old fart to give it a chance. However, the last time I looked (it has been a while though) there were only a couple apps and they were complete management systems and quite pricey.

Also...we don't have Internet at the bar, but I could do a wifi intranet and operate Excel over a remote connection, but again...that is something I would have to teach him how to initiate in the morning and if a computer is involved his eyes glaze over pretty quickly. Plus...at that point I might as well break out the netbook and take it out front and did it on a full blown computer.

So anyway...I'll keep watching here and if a come up with anything in the meantime then I will follow up here myself.

Thanks for the input and catch you later.

--Roger

 

[twerppoet]

. . .That being said...not really knowing jack about complex formulas or VB, beyond calculating a few cells...what little I do know (just enough to be dangerous) from a simple as possible and logistical viewpoint...I thought that was kind of one of the purposes of the IF/THEN function. So in simple English "if A1contains a value >0, then copy and paste that value into the first available cell of the appropriate column in sheet 2" then have it just bump down to the next product and so on until.
--Roger

The problem is that there is no put or paste function. All results of a formula end up in the same cell as the formula. What I wouldn't give for an addressable paste function. You could do so much with it.

I'm afraid I'm not much more advanced that you are. I've played a bit, but I haven't even looked a VB for years (don't have Excel anymore), and never got past the simplest sort of scripts.

 

[rlcox]

Update on my end...

I found the "T" function under the "Text" category. This will copy the text of the desired cell referenced to, as is into whichever cell you reference it to. In addition, I figured out that if you do an "If" function and declare the desired cell as "If 'cell'>0" and then make it add that desired cell to "+0" then it will paste that cell into wherever you are laying the function. Obviously you can jump back and forth between sheets when declaring cells in a function and easily make it "T" (copy and paste) a cell where wanted. Now it's a matter of figuring out how to get it to drop rows that return no input and go to "next".

I'm going to bed...more tomorrow I hope!

Later,
Roger