PDA

View Full Version : Generate customised list from master



TrishB
2016-08-10, 10:07 PM
Hi! I'm not very familiar with Excel, so forgive my ignorance. What I want to do is have a master shopping list, and every week select from it the things I need for that week (with a tick or something), and generate a new list with only those entries on it. Is there any way I can do this? (Ideally I'd like to use Excel for the master, and Word for the weekly list, but if there's a better way, please do tell me!)

p45cal
2016-08-10, 11:49 PM
see attached.

TrishB
2016-08-11, 01:06 AM
FANTASTIC! Thank you so much, p45cal!

TrishB
2016-08-11, 06:05 PM
p45cal, Could I ask you one more question about this, please? How can I extend the master list beyond 19 items? If I click on the cells, I just get the tick appearing! Many thanks.

TrishB
2016-08-11, 06:29 PM
Sorry, scratch that, I've realised what I should do.

TrishB
2016-08-11, 06:41 PM
Oh dear, so sorry, I do still have a problem. I've extended columns A, I and J by copying and pasting (I and J together), but when I put ticks in the J column, the items are no longer being transferred to column A. Can you help?

p45cal
2016-08-11, 08:00 PM
See attached.

TrishB
2016-08-11, 08:08 PM
Thank you again, for your quick and very helpful reply. Is there any way I can extend this column myself? I think I may possibly need more than 95 rows! (I'm already up to 87, and I'm still compiling the list.)

p45cal
2016-08-11, 11:19 PM
It's laborious to add the check boxes manually; each one is linked to the cell it's on top of. However, run this little macro after selecting any cells you want checkboxes in. Select say 200 cells in a single column at once and run the macro - it will add the 200 checkboxes and link each to the cell it's sitting on. You should first delete any pre-existing checkboxes associated with thos cells otherwise things will get VERY confusing.
As to the formulae in column A, adjust the ranges in the formula in cell A2 to accommodate the new checkbox cells, and enter the formula with Ctrl + Shift + Enter, NOT just Enter. Then you can copy down.
Sub AddCheckBoxes()
'First manually remove any pre-existing chaeckboxes in these cells!!
For Each c In Selection.Cells
Set cb = ActiveSheet.CheckBoxes.Add(c.Left + 1, c.Top + 1, c.Width - 2, c.Height)
With cb
.Height = c.Height - 2
.LinkedCell = c.Address(0, 0)
.Text = ""
.Interior.ColorIndex = 2 'white fill to mask text in cell behind.
End With
Next
End Sub

TrishB
2016-08-12, 01:00 AM
Gosh, I'm really sorry, but this is all over my head. I have no idea how to run a macro. Could I ask you to please add to your kindness and take pity on me and send me another copy? I'm not sure how many entries I will eventually have, but if you make it 500, that will certainly be far more than I need, and hopefully I won't need to bother you again! I really appreciate you being willing to help me. Many thanks.

AliGW
2016-08-12, 08:29 AM
@Trish - the formula in A2 is this:

=IFERROR(INDEX($I$1:$I$96,SMALL(IF($J$1:$J$96=TRUE,ROW($J$1:$J$96)),ROW()-1)),"")

You need to extend the ranges in red to cover the length of your list like this:

=IFERROR(INDEX($I$1:$I$500,SMALL(IF($J$1:$J$500=TRUE,ROW($J$1:$J$500)),ROW()-1)),"")

Do not just click ENTER to exit the cell - you must click CTRL+SHIFT+ENTER to confirm this formula, which is an array formula, then you can drag copy it down as far as you need.

Hope this helps!

p45cal
2016-08-12, 01:26 PM
Attached.

TrishB
2016-08-13, 12:23 AM
Thanks, Ali!

TrishB
2016-08-13, 12:25 AM
p45cal, thank you so much. You have been an absolute star!