Inventory by location list question?

[BobT]

I have an excel spreadsheet inventory list.

Sheet-1 of the spreadsheet contains everything we would want to know
about the inventory (what it is, where the item is located, what it was
purchased for, sold for, etc).

Example of spreadsheet sheet#1 column (field) name/layout across top.

Column A = (Item#), Column B = (Category), Column C = (Description),
Column D = (DatePurchased), Column E = (Price Purchased),
Column F = (Date Sold), Column G = (Sold For),
Column H = (Location of item), Column E = (Price Purchased),
and Column F = (Date Sold).

Rows-3 thru X following (down and across), contain the data for the
actual inventory records.

Question/Problem:

I would like to create another page (sheet-2) that would show only the
items or details for a specific location from sheet-1.

Example: IF 'store1' is listed in any of the cells under Column H:2
and following from sheet1, THEN list all record details for store1 from
the first sheet-1 to sheet-2 (e.g Item, category, description, etc)
for that store. A list for what's just at that store

The details being across the rows in the columns for that row.

Does any one have any ideas on what the best way to go about this is?

Thanks-in-advance !

 

[JMay]

The simplest way??
On your sheet1 set up Data - Auto-Filter and click on the down arrow of H2,
Select "1"
This will filter out all records NOT 1.
Highlight and copy entire filtered range, and Ctl-C (Copy)
Go to Sheet2 - click on A2 and do a Paste-Special Values
Done..

 

[Tom Ogilvy]

Or just use the autofilter and not have separate pages.


A shortcut to highlight the filtered range plus header row Ctrl+Shift+8
--
Regards,
Tom Ogilvy

The simplest way??
On your sheet1 set up Data - Auto-Filter and click on the down arrow of H2,
Select "1"
This will filter out all records NOT 1.
Highlight and copy entire filtered range, and Ctl-C (Copy)
Go to Sheet2 - click on A2 and do a Paste-Special Values
Done..

 

[BobT]

Thanks JMAY and TOM for your suggestions on my ealrier question below. I
was a little confused on a couple of steps because of thier sequence or
I was just not getting it (with some assumed steps etc). But here is
what I ended up doing:

I did a COPY from sheet-1 then a PASTE SPECIAL onto sheet-2, under
paster special I was able to select an option to link from Sheet-1, the
sheet from which I was copying from.

Next, I then went to the column which my shops were listed column G and
performed the AUTOFILTER function which you both described.

It worked !

Only two things:

1. It seems if I want the list to get updated on the new sheet
(sheet-2) I must go to the Autofilter tab on column-g and reselect the
filter desired (example shop#1) everytime, it then refreshes with any
updates or changes from sheet-1 but it's not automatic.

Any way to automate this? anyone!


2. Blank fields on sheet-2 show with zero's instead of being blank like
on sheet-1. The references on sheet-2 look something like this:
=Inventory!B1 etc

I looked in FORMAT CELLS but didn't see anything under formating so as
to not show zero's but instead leave blank. P.S - Sheet-1 the master
table is fine.

Anyone know any answer to that one?


Once Again - Thank in advance to All !

 

[JMay]

It was Tom's suggestion to Have only the regular Sheet1 and create the
Auto-Filter on it. That way you can use it for data entry and updating plus
employ the Auto-Filter on it whenever you wish.. That seems like it would
work for you.
HTH