Excel 03 - Stocktaking theory questions

[titaniumbean]

Hi guys i'm an excel newbie, I have experience coding and with the basics of excel. I am looking to update a very basic spreadsheet stocktake system. The current version is very limited.

What we have-

1 workbook, multiple worksheets each of which is a stocktake for a different month (1 sheet being a blank template) and in each sheet is a list of possible stocks/types with a quantity field.



What I want to get to-

* I preferably want a userform entry system to put the data into the sheets with the user not being able to see all the data (as they are doing it);

* a summary page on which you can select the month you are interested in and it looks up all the fields in the list which have figures entered in the quantity field and displays them on the first page you view ie a summary page.

*i'd also like to be able to create charts comparing the different months stocks for each item.

-----------------------------------------------------

My main questions are how should I go about doing this?
What sort of functions will I be needing to use at each stage?
What important things should I remember that will stop me from making annoying and costly mistakes?
How can I make sure it will be future proof?
How hard would it be to have a form as the first page with buttons that direct you around the spreadhseet eg enter data, review a month, chart stock x's progress etc


I know this is a very vague and far reaching post, so i'll be grateful for all views/responses. Thanks in advance.

Bean

 

[matthew230]

Want you want is an application.

I suggest you modulize it, work out step by step what you want to do and then learn each part independently. Use the forums, but ask a specific, the last thing people feel like helping with is a complete project.

You can't avoid annoying and costly (time) mistakes.

You can't future proof it, all you can do it finish it and then several months later realise that it needs a complete re-write. However, the next app you write afterwards will be that much better for it.

Button's to direct you around a spreadsheet are a piece of cake. Why not try recording a few macro's and then studying the code, as long as you do not rely on this method, it can serve a useful purpose.

Good luck

 

[titaniumbean]

Want you want is an application.

I suggest you modulize it, work out step by step what you want to do and then learn each part independently. Use the forums, but ask a specific, the last thing people feel like helping with is a complete project.

You can't avoid annoying and costly (time) mistakes.

You can't future proof it, all you can do it finish it and then several months later realise that it needs a complete re-write. However, the next app you write afterwards will be that much better for it.

Button's to direct you around a spreadsheet are a piece of cake. Why not try recording a few macro's and then studying the code, as long as you do not rely on this method, it can serve a useful purpose.

Good luck

Hi I only ask in general terms because I feel like I can use the functions etc esp with auto complete I just need to know which ones to use.

I have read FAQ's etc and read alot of the dos and donts, for example by future proofing I meant things such as never referencing a constant rather reference a cell with the constant in so it's easier to update etc etc.

I've written in VBA before so i'm not too worried about the code itself, i'm just kinda confused as to where to actually start.

Thanks for the quick response.