Hi all,
I have a spreadsheet in Excel that serves as a pricelist, but it needs continuous updating (hiding and unhiding rows) as stock becomes available or unavailable.
I would like to have the ability to mark and unmark each item as available or not available. This can be done with Y/N or 1/0 in a column on the sheet that holds a list of stock items.
If the item is in stock then it should appear on the second sheet and if it is not in stock then it should not appear at all.
Items may need to be added or removed from the master sheet from time to time as new products are added or products are no longer supplied.
I've attached a sample so you can see how it is laid out.
Any suggestions that would make this tedious task easier would be welcome.
Thanks so much!
I don't have Excel 2007 on this computer, but I can tell you how I would do it.
On Sheet 1
Say Column "Y" is where you put the 1/0 starting at Row 2 (1 and 0 are equalavent to True and False and can't be missed).
In Cell Z2: =$Z1+If($Y2,1,0) copied down to the bottom of data.
On Sheet 2
Cell Z2: =Match(Row()-1,'Sheet1'!$Z:$Z,0) copied down as far as necessary.
Cell A2: =Index('Sheet1'!A:A,$Z2)
Make sure there is no "$" in A:A and there is a "$" in front of the "Z".
Copy A2 into range A2:x??? (??? is the last column of data and x is a row far enough down the sheet to cover all your data).
This will automatically put or remove all your data on Sheet2 as you change Sheet1!Column Y from 1 to 0 and back.
Obviously change the columns to your columns.
I'm including a file demonstrating this idea. There is some added error checking to show you how to have the formulas for empty rows not show "N/A".
Last edited by foxguy; 11-18-2011 at 12:53 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks