Adding information to templates

Nicole Finch

I am trying to work out the best way to do this. I have an evolving list of products (hundreds and hundreds) that can be ordered, and 29 people (only - this is set) who can order them for a Bulk Buy once a year. 

My current plan, was to create a Template, then have a sheet for each person. This way I can request them to 'update' only the lines that are relevant to the Bulk Buy (these are staggered throughout the year). I can then use a report to pull in all the updates.

However, I don't believe that if I add new products to my Template that these will automatically be added to the sheets that have been created from the template. Which would mean I would have to manually copy the new lines across 29 sheets. I was wondering if anyone has any other ideas how I could potentially do this to save myself some time?

*Please note, my plan for the template is to to hierarchy the products in a list - with the columns being ID/Description/Price/Qty/Rebate/Current Supplier. I would be adding new Hierarchies as the product list grew. 

Richard Rymill SBP

Hi Nicole

You may need to avoid too large a single sheet with eg over 200,000 cells in it, which would cause sheet load issues, but if you can restrict your sheet size this should work fine. as follows... 

One sheet to capture demand from your 29 users. In that sheet you create dropdown lists for Products & list your 29 users.  

Then columns to record such as Volume req, reason why they need it? For when? Approval or or rejection/ query? ( via Update requests). Data triggers like product type/ approved, to help you filter later? 

Create a Smartform from this sheet, which will list all the fields you need your users to complete when they order. 

When requests land, you get an Alert, Approve/ query etc then you can either drag them into an hierachy  OR (if the sheet size worries you?) Move agreed orders to another sheet, OR just use Filters or Reports to show what is required in what volume etc. 

I could go on and add more functionality, but I think this will save you endless amounts of time and Every time you add a product into the dropdown list it will appear when the next form is filled. Use alphabetical listing so type L takes you to the L's saves hunting down a long list of product. So the form is easily completed, starting with finding your own name in your list of 29 users.

This is a long way better than creating 29 sheets, which would not work well! 

 Hope that Helps?

Richardr

 

Michelle Watkins

Hi Nicole,

Richard's details are spot on.

In summary - my recommendation was going to be to flip what your approach is ..

Instead of individual sheets for each person, have one MASTER sheet and then individual REPORTS for the people.

This picks up any new ROWS and hierarchy of data, but does not automatically pick up any new columns you might add to the underlying master sheet.

You can still send and schedule 'send update requests' from the reports or the underlying sheet -- this one request is visible on both the sheet and the report.

Just more thoughts on how I've approached a similar situation.