Copying a row of data based on a category

[Finalfrontier1976]

Hi,

I am trying to develop a spreadsheet (attached to this post), what I would like to do in the Contents tab is have a drop down list in every cell from A2 downwards (only in column A though). When clicking on this drop down list it will display all the categories that are listed in the Category tab (this list may increase). When the user selects a category from this list it is displayed in column A on the Contents tab.

What I want to do then is for whatever data I put in each row is copied to the tab that relates to the category.

So for example, let's say my first entry is that on the Contents Tab in Cell A2 there's a drop down and I choose Photos (which is one of the categories list on that tab), I then type date in columns/cells: B2, C2, D2 etc then everything from A2 to D2 (or beyond that to even Y2) is copied to the photos tab from column A onwards. My further issue is that if I then enter another Photo category, I want it to again copy the data but put it under the line that's already been copied there from the previous entry. Bascially, everything entered into the Contents tab is all copied to the appropriate tabs. The contents tab shows all entries.

I hope that makes sense!

Thanks,
Lew

[FDibbins]

To start with, please update your profile to show the excl version you are using. Members use this to tailor suggestions for you (earlier versions of excel dont have all the finctions that later 1's do)

Assuming you are using 2010 or later....
make sure you are in A2
click the Data tab/data Validation.
Under Allow, select List
Under Source, highlight the range on categories (plus a few extra cells...=Categories!$A$1:$A$20) We could make this dynamic, but if it wont grow much/often, we can keep it manual
click OK

You now have a DD in A2, which you can copy down as needed

To do the copying from there to whichever sheet you choose, will require VBA, which is not my strong side, but Im sure 1 of the other members will pick this up and help you further

[xladept]

Hi Finalfrontier,

Here's event code to put the validation into any cell in the first column after row1 - but there seems to be no data to transfer?

Please Login or Register  to view this content.

[newdoverman]

Here is a start for you.
I put your CATEGORIES in a Table then used the header in the table to create the Data Validation list on the Contents worksheet. I filled in rows 2 to 20 with the Data Validation drop down lists.
The data Validation formula is:

Please Login or Register  to view this content.

The name is the entire column of the table so when additions or deletions are made, the drop down list will adjust accordingly.

The formula used on all worksheets is exactly the same as they take the value that is in B1 which is the category for the worksheet. This is an ARRAY FORMULA so, enter with Ctrl + Shift + Enter.

Please Login or Register  to view this content.

I entered this formula on the first few worksheets and filled across and down to N21. The area with the formulae is shaded with outlines on the cells so you can see where the formulae are. If you need more columns fill the formula across. If you need less, delete the excess columns.

I corrected some of the categories on the CATEGORY worksheet as some entries had trailing spaces.

Column A of each workbook is actually column B of the Contents Worksheet as there is no need to have the category repeated down the worksheets.

I think that you should be able to follow the example worksheets to complete your workbook.

NOTE: I unmerged the cells that you had as merged in the category worksheets and used B1 in each of the worksheets as the cell being referenced on all worksheets that I filled in.