Making a shopping list

E

EARTHWALKER

This was posted by a friend in another forum.

Please can someone format a a table in excel for me. I need a tabl
that displays a list of items that i can enter a number in a colum nex
to an item, this would then generate another table that i could the
print showing only the items i have have selected and the qauntity. i
like a shopping list from a supermarket .

It's really anniying me that I can't work it out, as it seems very ver
simple to do
 
J

Jason Morin

Let's assume your list of all items are in column A and
qty to purchase in col. B. The sheet name is "allitems".
In A1 of a new sheet, insert this, press ctrl/shift/enter,
and fill down as far as needed (to the row # that equals
total possible items on sheet "allitems"):

=INDEX(allitems!$A$1:$A$100,SMALL(IF(allitems!
$B$1:$B$100<>"",ROW(allitems!$B$1:$B$100)),ROW()))

Now put this in B1 and fill down:

=VLOOKUP(A1,allitems!A:B,2,0)

To hide error values, you can select col A - B, turn the
font color to white, then press Ctrl+1, Number tab, click
custom, and insert:

[Black]General;[Black]@

HTH
Jason
Atlanta, GA
 
D

Debra Dalgleish

Instead of creating another table, you could use AutoFilter to hide the
rows with no quantity:

Select a cell in the list
Choose Data>Filter>AutoFilter
From the dropdown list in the Quantity column heading,
choose (NonBlanks)
Print the list
To view the full list, choose Data>Filter>Show All
 
A

AlfD

Hi!

You can do this using data validation and lookup.

The following produces a (literal) shopping list with defaul
quantities.




Make a 2 column table somewhere (eggs, tea, sugar etc) in first colum
and default quantities (e.g. 6,100 bags,1kg) in the second. Name thi
table as ShopList (Insert >Name>Define)

In col A (as many cells as you need) insert data validation (Data
Validation) using list and entering ShopList as that list.
In B1 put
=IF(ISNA(VLOOKUP(A4,ShopList,2,FALSE)),"",VLOOKUP(A4,ShopList,2,FALSE)
and copy this down as far as you need.

Now use the dropdown box in A1 to select Tea and B1 will show you 10
bags. If you don't like the default you can then change it (bu
remember that this will destroy the formula in that cell).

When done: select the filled cells and print.

Is this something like what you want?

Al
 
Top