Copy data from master sheet to other sheets if criteria is met.

Hi there
I am taking over spreadsheets from other folks who had their own way of doing things and I'd like to revise the process somewhat. I hate entering the same data multiple times so like to enter it once and have it to be transferred automatically. The previous way data was entered was month by month with a total/summary sheet, and then re-entered on other pages. What I would prefer is to have one master sheet (probably 100-120 lines) where I can see everything together and then disperse that data to the relevant monthly sheet if certain criteria are met. I've never tried programming macros so formula solutions are the easiest for me. in the attached example:
1) The Jan tab would be: If master! column 'issue'=Jan, then copy columns b thru n onto this sheet. I would need to repeat this for the other 12 sheets, so can this be done at once or does it need to be done one by one?
2) I've left 15 rows for each month before the totals row. Is this the best way or is there a way to set it that how ever many rows are on the master get auto transferred to the individual month. eg 9 in jan, 12 in feb etc.
3) For the 'by advertiser' tab, I'd like to count how many issues an advertiser appears in. There are only 13 issues but as you can see advertiser A appears 16 times since they appeared twice in some issues. I'd only want to count this once.
4) Again, for the 'by advertiser' tab, I got the list of advertisers by unique filtering the master list and manually copying it over. Is there a way to set it so that if there is now a new advertiser 'P' the advertiser column will automatically update without my having to manually add/copy it?
5) For the 'position chart' tab, Cell be would be if Master column 'issue'=A2 AND master column 'POS'=B1, input advertiser here (and then repeated for all other cells)
Thanks.

Hi teachaman,

I changed your spreadsheet into using Pivot Tables... they are pretty unflexible, but I think some of the results are exactly what you want...

Also, in the Master table, notice that I added an extra column to count the pages for the Advertiser.

Let me know what you think.

Dennis

Hi Dennis
Thanks so much for taking a look at this and my apologies for the delayed response. I found it a little hard to follow the pivot tables. I also did a test and changed something in column B on the master sheet but the 'pivot issue' sheet did not change. I want to be able to input everything on the 'master' and have it auto transferred and updated on the other sheets. I would then eventually 'lock' the other sheets and just work on the master. Also I'm not too fussed on having to manually count the advertiser occurrences on the master sheet. I know there is a way to say if month = jan AND advertiser = M, only count single occurrences. In the position chart, I was looking to put the name of the advertiser into the different positions so eg for January TOC 1 (cell D2) would show advertiser F on the position chart tab. Thoughts?

Hi teachaman,

How's this for item 2... only trouble is duplicate entries (see Nov and Best Available)... I suggest you have 2 entries, for example Best Available1 and Best Available2

Let me know what you think...

Dennis

Hi Dennis
Thanks so much for your input.
1) The Position chart formula worked a charm and I have used that one my final doc. Positions such as 'best available' I don't have to worry about since they are not contracted and if one of the other positions doesn't get sold, an advertiser gets upgraded from best available, so that is all good.
2) I'm still a little cautious with the pivot tables for a couple of reasons: a) If you still need to filter in the pivot table, why not just filter in the master sheet. b) I'm worried that an update will be missed if the pivot is not refreshed for some reason. c) I send the info to production folks and I could ask them to filter on the master sheet but I think they need/want to see things broken out by month and not just filtered. (I knnow!) I suppose I could create 13 pivot tabs for each issue, filter it by month and lock/hide the filter, so they cannot filter the wrong one. Also if I wanted to have a total by month as well as YTD running total I would have to create 13 pivot tabs, correct?
The workaround I have for the time being is to input everything on master, and when the issue is complete, filter and copy that into the relevant month. Then I always have to remember to input on the master and NOT the month tab, and of course copy and paste changes every time, which becomes a pain. As I say, what I'd love to do is have the master 'open' for input, but 'lock' the other tabs so that they only replicate info from the master and cannot be altered by mistake.
3) I'd prefer not to have to add a helper column but if that was the only way to get unduplicated counts, I would probably move it to the end or hide it, if I am still copying and pasting things over. Ps. is there a way to omit hidden rows/columns from a copy & paste?
Thanks again.
E

Hi E,

Good point about the pivot table and filter... I guess the way I designed it was not very efficient way of using pivot tables.

I fixed the 'by month' worksheet to get the pages and net values, so I don't think yu need 13 pivot tabs...

As far as copy & paste with hidden rows/columns could be tricky. Depending on how you do it, you can lose some key data.

Sorry, I might have led you to a wrong path in regards to your project.

Dennis

Hi Dennis
No, you haven't steered me wrong at all. I'm much further along than I would have been without your help. For some reason the position chart is not working properly now when I copy over the formula and it is giving me different data. Is there a way to connect by email on here. I'd like to send you the exact file but it contains confidential info.
Thanks
E

Check your private messages...

Hi E,

Your formula is ok, what's wrong are the range names 'issues' and 'pos'.

Rename them again, it's linking to someplace else. Look at the Formulas tab, Name Manager... I suggest you clean up all the names that are not being used or are errors...

Let me know if this still doesn't solve your problems.

Dennis