Excel and Stock Sheets :(

Pheebs · 12-09-2008, 12:18

I *hate* excel.

Currently trying to fudge together something a little more useful than what I've already got for stock check of my greeting cards. (Noobs - I design and sell greeting cards and will soon be trialling in Clintons).

I think I've looked and thought about it so much that I'm now just over complicating it so am hoping one of you genius people will say "stop being a div, just do this this and this!"

What I have and need to be able to do with these sheets:

I have long (and forever growing) lists of greeting card designs and their codes. They're all categorised (so under "seasonal" or "love" or "chocolate" etc).

I will have lots of clients (and hopefully will get some more soon) who will be ordering in cards whenever they require (could be any day of the week). Some will be doing sale or return - so if they do order a batch, I may well get a load back which I will need to record to go back onto my stock.

Obviously need to account for damaged items too.

I will need daily updates on figures and where I can hopefully figure out easily what's selling best etc.

It sounds simple but with all the customers - I'm going to need a day by day, month by month update for all of them with sale and returns. I just can't think how to practically put it on a spreadsheet without it becoming massive and confusing.

Damn me for having far too many designs

I have some ideas and have half done one but it's very confusing and just over crowded and soooo I would be intrigued to know how you bright sparks would lay it out.

(and yes I know I should get someone to design me a proggy but I have zilcho dosh to spare for things like that

)

Daz · 12-09-2008, 12:27

Sounds more like you want to do something in Access rather than Excel - it will be better at maintaining the relationships between all the pieces of the puzzle. It'll be more complicated though under the hood, you'll need a basic DB design, design some import forms, and make some reports to spit out the data you want, which might need decent knowledge of SQL. The end result would give you a simpler look into your data though, I can imagine it's a bit of a mess trying to fit it all into workbooks.

If you wanted to do it in Excel it could be done I'm sure with the same sort of principles. Have your data workbooks hidden away and have a couple of forms at the front for data input and some sort of reporting function. Excel can do brilliant things with pivot tables but it needs some sort of relational back end for that.

AboveTheSalt · 12-09-2008, 13:04

Quote:

Originally Posted by Daz

Sounds more like you want to do something in Access rather than Excel ...

Using Excel is only ever going to make your task harder if not impossible.

At a very quick glance, you have:

Cards - Unique key (UID), Desc'n, Type(s), Code, Supplier, Buying Price, Selling Price, Qty. avail., Qty allocated, Qty. sold, Qty. returned, Qty. faulty, etc...

Customers - UID (A/c No.), Name, Address, Tel. No., Spend to date, Returns value, etc...

Orders - UID (Order No.), Link to Customer, Customer Order Ref., Status, Date ordered, Date completed, Value, Paid (Y/N), etc...

Order Line - UID, Link to Orders & Link to Cards

Doing this in Excel is NOT going to work.

ps - Do you actually have a copy of Access?

Pheebs · 12-09-2008, 12:32

Ahh boo see I blooming well studied Access donkeys ago and have promptly forgotten everything I learned

(in fact, I think I bonfired all my work and roasted marshmallows on it

)

I can fudge my way around excel quite well but I think I would ultimately fail with Access.

Boo.

Currently it sounds like I was right in the respect it's just going to be complex and big

Pheebs · 12-09-2008, 14:02

I dooo... sooomewhere....

I think I've tried bullying my excel sheets into working and have just successfully wasted all morning and afternoon when I could have been drawing! Doh!

Have spoken to a couple of people who are dab hands at sage (fankoo for your input vix!) and am contacting another lady friend I know down here who's into this kind of gumph to see whether they're able to mock something up for me

Excel... IMO... after this morning... CAN DIE IN THE FACE!

12-09-2008, 12:18	#1
Pheebs Deep Throat Join Date: Jul 2006 Posts: 6,512	Excel and Stock Sheets :( I hate excel. Currently trying to fudge together something a little more useful than what I've already got for stock check of my greeting cards. (Noobs - I design and sell greeting cards and will soon be trialling in Clintons). I think I've looked and thought about it so much that I'm now just over complicating it so am hoping one of you genius people will say "stop being a div, just do this this and this!" What I have and need to be able to do with these sheets: I have long (and forever growing) lists of greeting card designs and their codes. They're all categorised (so under "seasonal" or "love" or "chocolate" etc). I will have lots of clients (and hopefully will get some more soon) who will be ordering in cards whenever they require (could be any day of the week). Some will be doing sale or return - so if they do order a batch, I may well get a load back which I will need to record to go back onto my stock. Obviously need to account for damaged items too. I will need daily updates on figures and where I can hopefully figure out easily what's selling best etc. It sounds simple but with all the customers - I'm going to need a day by day, month by month update for all of them with sale and returns. I just can't think how to practically put it on a spreadsheet without it becoming massive and confusing. Damn me for having far too many designs I have some ideas and have half done one but it's very confusing and just over crowded and soooo I would be intrigued to know how you bright sparks would lay it out. (and yes I know I should get someone to design me a proggy but I have zilcho dosh to spare for things like that ) __________________ http://www.spiffingdesigns.co.uk

12-09-2008, 12:27	#2
Daz The Stig Join Date: Jun 2006 Location: Swad! Posts: 10,713	Sounds more like you want to do something in Access rather than Excel - it will be better at maintaining the relationships between all the pieces of the puzzle. It'll be more complicated though under the hood, you'll need a basic DB design, design some import forms, and make some reports to spit out the data you want, which might need decent knowledge of SQL. The end result would give you a simpler look into your data though, I can imagine it's a bit of a mess trying to fit it all into workbooks. If you wanted to do it in Excel it could be done I'm sure with the same sort of principles. Have your data workbooks hidden away and have a couple of forms at the front for data input and some sort of reporting function. Excel can do brilliant things with pivot tables but it needs some sort of relational back end for that. __________________ apt-get moo

12-09-2008, 12:32	#4
Pheebs Deep Throat Join Date: Jul 2006 Posts: 6,512	Ahh boo see I blooming well studied Access donkeys ago and have promptly forgotten everything I learned (in fact, I think I bonfired all my work and roasted marshmallows on it ) I can fudge my way around excel quite well but I think I would ultimately fail with Access. Boo. Currently it sounds like I was right in the respect it's just going to be complex and big __________________ http://www.spiffingdesigns.co.uk

12-09-2008, 14:02	#5
Pheebs Deep Throat Join Date: Jul 2006 Posts: 6,512	I dooo... sooomewhere.... I think I've tried bullying my excel sheets into working and have just successfully wasted all morning and afternoon when I could have been drawing! Doh! Have spoken to a couple of people who are dab hands at sage (fankoo for your input vix!) and am contacting another lady friend I know down here who's into this kind of gumph to see whether they're able to mock something up for me Excel... IMO... after this morning... CAN DIE IN THE FACE! __________________ http://www.spiffingdesigns.co.uk