Creating a New Sheet / Populating Existing Sheet from Worksheet Data with Cell Ref "Yes"

Hi All,

Firstly thanks for any help on this query, its much appreciated.

I am trying to create a "Shopping List".

Basically I have Worksheet1 Named "All Items" within the worksheet I have the following Columns:

A1 = Catergory
B1 = Item
C1 = Needed
D1 = Quantity

The rows are populated with the information accordingly.

What I would like to do is create a worksheet with anything I put a "Yes" to in the C1 "Needed" column and also include "D1 Quanitity"

Thanks again,

Gurnaam Singh

Hi
Try to use a pivot table
see the file

You could just Filter on "Yes" in column C and copy/paste to "Shopping List".

Or

in A2 of "Shopping List"

=IFERROR(INDEX('All Items'!A$2:A$1000,SMALL(IF('All Items'!$C$2:$C$1000="Yes",ROW('All Items'!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")


copy across and down

Hi,

Many thanks for your prompt replies.

I have tried the following in Shopping List however nothing appears.

=IFERROR(INDEX('All Items'!A$2:A$1000,SMALL(IF('All Items'!$C$2:$C$1000="Yes",ROW('All Items'!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Would you mind uploading the sheet?

Thanks.

Or, instead of having the "Needed" column, could it create a list from the Quantity and use row information Item and Quantity

Sorry .. you need to enter the formula with Ctrl+Shift+Enter. You will see curly brackets { ...} appear round the formula if this is done correctly.

Hi,

It doesnt seem to be working. Sorry im not great on excel.

I have re-uploaded the file, I have removed the column for "needed" I guess now what I am trying to achieve is if there is a value in the column Quantity then for that row to be copied/mirror over to the new sheet.

If this is possible to be populated as I enter the quantities if that makes sense.

Regards,

Gurnaam Singh

It worked! you are an absolute legend!... thank you so much.

I am in the process of trying to create an app and I would be happy to share a % of profit with you (if it gets going) feel free to share you email if you'd like.

Thanks

Gurnaam Singh

Thank you AliGW, really appreciate it. Made my day.. (sounds sad I know) but atleast it allows me to move on to the next step of my project.

IF you wouldn't mind, could you explain the formulae so I can understand it for myself if I need to add/amend/change? I wont be offended if you dont. Thanks