Drop Down list from 1 to a variable value, dependant on MAX value in a different Column!?

Hi All!

Bit of an unusual problem here, or for me anyway, I'm sure all you gurus will find an easy fix!

First off, i am using a copy of Office for Mac 2011 on a work macbook air. So any suggestions need to work on mac & windows.

I have a spreadsheet, where in Column C, The name of a client is added i.e. Client 1, Client 2, Client 3 etc. Then, in column O i have a formula:

Please Login or Register  to view this content.

This formula counts the number of appearances of that client. This is used as a helper to the following formula in Column B which is:

Please Login or Register  to view this content.

This gives me just the number from the end of the formula in Column O and then configured to show it in the format "0001", "0002", "0003" and so on.

What i now need, is in Column G, to have a data validation drop down box, dependant on the name of the client, which starts at 1 and finishes at the highest job number of that particular client. For example if Client 1 has 3 jobs, their drop down would show as 1,2,3. If Client 3 has 10 jobs, their drop down would be 1,2,3,4,5,6,7,8,9,10.

I hope this is possible? I can upload an example spreadsheet if needed, but don't have to hand right now, so hopefully this makes sense to get started on the problem.

Thanks in advance for your help guys!

Chris

Hi there,

I assume that the email post with the forum rules, was a request for a spreadsheet example?

So, attached to this post is an example spreadsheet. i have removed some comments etc. for security reasons, but all the columns I have left populated are the ones which will be used in any formulas. The rest will be miscellaneous comments.

To explain what is happening so far:

COLUMN A is generating a unique reference sequentially, which will stay the same. This happens once COLUMN E is greater than 0 i.e. a date of some kind.

COLUMN O has a formula which monitors how many times the client in COLUMN C Has appeared.

COLUMN B is then using the answer in COLUMN O to generate a client specific number sequentially.

COLUMN J Has a drop down in with statuses of jobs.


Then we get to the bit that I need your guys' help with!

I want COLUMN G to have a drop down menu in each cell. I want the drop down menu to start at 1, and finish at the number of jobs that client has OPEN currently. To explain, if Iris P&H have 12 OPEN jobs, then their drop down would show as 1 to 12, if they had 20 open jobs, it would show as 1 to 20. Whether a job is open or not is defined by COLUMN J, the only status which signifies a closed job is "JOB COMPLETE" any other statuses are open and count as a job.

In an ideal world, I don't want to get into code or vba to achieve this, but, if that is how it must be done, I am open to it.

Thanks for your help in advance guys!

Chris