Creating Output Script using Checkboxes

Hi,

Not sure if this can be done in excel or if I need to create some sort of special form but....

I am looking to create an output script based on ticking chackboxes in excel.

The user would manually enter into a cell A3 an item code - lets call it 11111

They then use a button, probably a checkbox to select multiple areas that they want in the output - there would be about 8 to choose from, lets say in this case the user ticks three of them, those being 'North', 'South' and 'East'.

Finally they selct the weeknumbers they want to apply, again probably from checkboxes - in this case they select weeks '5' '6' and '7'.

This would then produce an output on the sheet in columns A, B and C that looks like this:

11111 North 5
11111 South 5
11111 East 5
11111 North 6
11111 South 6
11111 East 6
11111 North 7
11111 South 7
11111 East 7

Any help, guidance would be appreciated because I am a bit lost on this one, not sure if I need to use VBA to get it to work,

thanks for looking

https://onedrive.live.com/redir?resi...nt=file%2cxlsx


Hi, thanks for the feedback - attached is a link to the file - thanks for looking.

Hi,

There is only one piece of input data in the file - cell A10 - this is where a single item code is manually inputted. The user then checks the boxes for location (north, south etc) that they need and the week numbers. After doing this I was hoping it was possible to click a macro button and produce the output. I am sorry, I am unsure how I canuse the filters to produce this effect.

Hmmmm...well thanks for trying anyway.

There is no original data to show, it is simply a criteria selection sheet - enter an item code, select the areas and week numbers you want in the checkboxes and then the output is produced, thats the requirement anyway. I assume it cant be done, thanks for your time.

I have a working model which I would like to tidy up before posting. For my info, what is the likely maximum number of weeks selected?

I'll get back to you as soon as I can.

Hi,

That sounds great - the max number of weeks would probably be 15 weeks. I hope this helps,

Look forward to hearing from you!

Tony,
I f you recommend VBA than I am not one to argue. When it comes to Excel I am a mere apprentice!

I have managed to pull together an almost complete "solution" but am stuck on one formula. I posted a thread "Assigning data dynamically" and received an answer for one formula but not the second (probably because my data was misleading).

If posted my solution so far, would you be able to look at and see if its viable?

Many thanks.

Tony,


Attached is my attempted solution. my main problem is assigning data to column F: I can't get my mind round the row relationships between rows in F and C.

Obviously any other comments/advice you may have will be vey much appreciated.

It's late here in UK so I'll sign off for today.

Thanks again.

Hi,

Here is an example which I hope you can work with.

Tony,

No problem. I have posted a "working" version following my initial request and as yet have had no response from the originator of the thread so it probably isn't too urgent.

Being retired I have time on my hands!

Hi,

JohnTopley - I am both mortified and embarassed that I did not respond sooner to your help - my phone previously alerted me when this thread received an update but for some reason this has not happened.

What you have done is fantastic!!! Thank you so much. For some reason it seems to break down if I add more than 4 weeks into it but it is still hugely useful to me as it is. I am trying to get my head around what you have done with the formulas, I never thought to look at it this way and it seems to be the right direction. Again, thank you and my apologies.

Just drag down the formula in D- F : I tried 6 weeks and it was OK.

Hi, you are right but if a week that is higher than week 8 is selected then column F does not populate and im not sure why.

Hi - my mistake - I needed to drag the formula in C down further, seems to be working like a charm. John, I thank you for your time and help and Tony also. Many thanks.

This has highlighted by problem with formula in F and how it relates to regions and weeks. I'll have to have another think about this and/or hope Tony Valko ("my" guru) will resolve the issue.

Many thanks - good luck Tony!

Forget my last post!

Make sure you drag down all the formulae in D to F. You may have extend the ranges but I have just selected 4 regions and weeks 5 to 14 and it populates the tables.

Agreed John, this does indeed work , thank you.

Tony - I think we can consider this a completed piece now to avoid you spending time on it.

Apologies about the mis-communications: the old grey matter needs a tonic. Anyway, glad it is sorted.