Making a row change by contents of a column

**timmtamm** · 10-13-2009, 04:28 PM

I'm using excel to make a list. In column B I put a job catagory. I want columns C-J to then respond by putting up the jobs that need to be done in that category.

So, if I input category X, I want the cells in the row where "X" is (for example, row 5) to list all the jobs that need to be completed in that category. So 5C will come up with job 1, 5D will come up with job 3, 5E will come up with job 5, etc.

Then on the next row, if I input category Y, then 6C will come up with job 1, 6D will come up with job 2, and 6E will come up with job 3, etc.

There are over 100 different job categories and jobs, so it is too much for "IF" statements. Any ideas here? And how to implement the ideas? (I'm not very experienced at the programming thing so you might need to spell it out for me).

**JBeaucaire** · 10-13-2009, 04:45 PM

Hi TimmTamm, mockup a sample workbook showing a reaonable sample of the data and a sample of your desired results, point out where the results came from if not obvious in your sample data.
Click GO ADVANCED and use the paperclip icon to post up your workbook.

**rwgrietveld** · 10-13-2009, 05:02 PM

I got the following as a starter:

Please Login or Register  to view this content.

To get all the error codes out I extended the formula by using this:

Please Login or Register  to view this content.

Job_category and All_Category are defined names

**timmtamm** · 10-13-2009, 05:40 PM

Here are 2 different lists that I currently maintain by just manually inputing everything. There is actually a lot more information than what shows, I just simplified some things do make it easier to understand. When a job is done, I just color the cell in. (so like tablet 4 is done on the 1st spreadsheet and raw material 1 is done on the other spreadsheet.)

My goal is to put in a # (like a catalog #) and then the rest of the things will just fill in.

Again, I'm dealing with more than 100 items here. Possibly more than 300. (never actually counted).

**timmtamm** · 10-13-2009, 05:42 PM

sorry. here's the second one.

**timmtamm** · 10-13-2009, 06:03 PM

rwgrietveld

Hmm, looks promising, but I need some time to see if it will work. I'll have to get back to you on this one.

I've tried "if statements" before and they always go for about 10 to 15 conditions before they max out and give me an error. Here's one I've tried before, but its only takes in some of the most common ones as it will max out if I put in everything.

Please Login or Register  to view this content.

Oh, and I had to put that thing into every cell after column B in the row.

Anyway, I'll look into this one a little more. Thanks.

**timmtamm** · 10-14-2009, 05:13 PM

Ok, rwgrietveld's idea will definitely work, but I'm not sure how the machanics of it work. For one thing, how do you turn the cell into a dropdown list. The only way I've been able to duplicate it is to actually copy and paste the cell.

How does the program recognize which tab is the information list that it should be looking up. I plan on having separate tabs for each month. I don't know if, when I create a new tab, excel will try to pull information from that tab instead of the list tab. Also, if I wan't to extend the lists to column K through M, how do I do that? I tried to just copy and paste and it only gave me a reference error.
Edit: Ok, from what I can tell, there appears to be some sort of table that Vlookup is refrencing to determine where it gets its information from. At least when I use the formula auditing toolbar to trace errors, it points to some table thing. I cant figue out how to access that table. It's not under "Edit, Go To, Special, Current Array" or any other means that I know of.

I also need a column for a tracking #. When I inserted a column (column D) it just erased the contents of column D and made an empty column.

Here's the spreadsheet so far.