Excel 03 - Stocktaking theory questions

titaniumbean

New Member
Joined
Mar 19, 2009
Messages
7
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

:eek:
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top