I have to keep a record of my clients library.  I do this by keeping a list of product books.  I currently have a excel sheet that has multiple sub sheets (for each account) with all the names of each product book, I mark each book that is in there library.  What I would like to do is have one master sheet that I can update product names and when this is done it would automatically populate each sub sheet with the info, rather then me having to input in to each sub sheet.

ANSWER: Hi Thomas,

I'm not sure what you need exactly. Let me see if I understand your setup.

- You have one worksheet that holds a list of all books
- For each client you have a separate worksheet in which that booklist is repeated and in which the books owned by that client are marked

If that is correct, why would you repeat all books for each client? Why not just add the books a client owns, using the booklist?

---------- FOLLOW-UP ----------

QUESTION: I'm out side sales.  The books are sample of our product styles.  I keep a list for each account with ever book because I need to see who has what.  We launch and drop styles through out the year, I use my iPad at accounts and do not want to have to fill in each account info,  I would like to be able to use my iMac at home to update the list and just clink on the box for each book at the accounts.

ANSWER: Can you perhaps break down your question into smaller steps? I still fail to grasp how I can help you excel-wise :-)

---------- FOLLOW-UP ----------

QUESTION: Hi,  I have over 200 accounts and over 100 style books.  I do not want to update each account sheet when a new style book gets lunched or delete drop style books for each account sheet.  Its way too much work and too much time to do it for ever account sheet.  I want one sheet when edited populates each account sheet.   


I would do it like so:

- Have ONE sheet with books. Add a column in which you can mark books as Dropped.
- Format that range of cells as a table (Home, Format as table)
- In each account sheet, have a table in which the first column holds the names (or numbers) of a book that account owns (one row per book)
- In column A insert a Data, Validation rule with a dropdown list of all books
- on the columns next to the first column, use the VLOOKUP function to pull in the other information of the book as selected on column A. One of the columns might be pulled from the column on the books sheet that tells you whether the book has been Dropped or not.

See: http://1drv.ms/1Btz5iK
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.