Excelformula for multiple spreadsheets

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:29 PM GMT

Question

I am building a spreadsheet in Excel 2007 for a friend in order to help him keep track of a small, but rapidly growing online business. The important data will go on sheet 1 - a master list with all customer data in rows: Customer Name, address, shipping address, phone #, product ordered, quantity ordered, price, sales tax, last 4 digits of credit card and, most importantly, day of month to ship. I would like to create in several additional worksheets a shipping list that populates all of the customer data from the master list, based on the day of month a product would ship. So if I have 200 rows of customer data, and in column B I have a number between 1 and 31 (this indicates day of month only) can you help me with a formula that would transfer everything on the row to a new sheet, based on the number in column B? I would like each new sheet to populate based on the number equaling a value between 1-5 (sheet2), 6-11 (sheet3), 12-16 (sheet4), 17-21 (sheet5), 22-26 (sheet6), 27-31(sheet7). So if B2 = 1, 2, 3, 4 or 5, then B1 through B20 should automatically copy itself to sheet2. And if the value of B is changed (from a 2 to an 11, for example) then the data would leave sheet2 and go to sheet3. The data in the Mastersheet would stay, but the information would copy itself to sheets 2 - 7 based on the value in column B. Does this make sense or do you need more specifics? This might be a simple formula, but it is beyond my smallknowledge. I apologize if it is unclear, I didn't realize how hard it would be to write out what I needed

Answer

I'd be INCLINED to do this in Access rather thanas that allows for the system to be multi-user, and also avoids the need to duplicate data - however, if doing it in excel, it SOUNDS as though you basically want an easy way to get an automatic "Filter" appearing on different sheets - for which you can use an array filter - see http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls for an example of this

My email if it helps with any follow ups is aidan.heritage@virgin.net

Advertisement

©2020 eLuminary LLC. All rights reserved.