Data Validation character limitation issue

Can anybody help with my problem please?
My formula (ideally) is set out below, however the 255 character limit is preventing me from being able to enter this in Excel.

Would appreciate any suggestions.

=IF(R16="Chemicals",$AA$12:$AA$17,IF(R16="Petro-Chemicals",$AB$12:$AB$15,IF(R16="Food, Health & Personal Care",$AC$12:$AC$14,IF(R16="Agrochemicals",$AD$12:$AD$12,IF(R16="Adhesives & Sealants",$AE$12:$AE$14,IF(R16+"Colours",$AF$12:$AF$16,IF(R16+"Electronic Chemicals",$AG$12:$AG$14,IF(R16+"Distributors",$AH$12:$AH$14))))))))

I pasted same in 2007, but there is no any error.
There are two options
- As mentioned by Joseph use name range.
-Put your criteria( "Petro-Chemicals","Food, Health & Personal Care"....)in cell and link them.

Regards,
Suhas

Thanks, I've tried to link the criteria to cells, but now get the message 'The list source must be a delimited list, or a reference to a single row or column.'

The references to the Cells e.g. AA1 are where the headings are located, i.e. Chemicals etc.

My revised formula is below - any ideas?

=IF(R16="AA1",$AA$12:$AA$17,IF(R16="AB1",$AB$12:$AB$15,IF(R16="AC1",$AC$12:$AC$14,IF(R16="AD1",$AD$12:$AD$12,IF(R16="AE1",$AE$12:$AE$14,IF(R16="AF1",$AF$12:$AF$16,IF(R16="AG1",$AG$12:$AG$14,IF(R16="AH1",$AH$12:$AH$14))))))))

Now for the next question :-(

Any idea how I can extend the formula down the cells in the column so that the formula remains the same, but the cell reference moves down to the next, i.e. R16 then R17 then R18 etc...

=IF(R16=AA1,$AA$12:$AA$17,IF(R16=AB1,$AB$12:$AB$15,IF(R16=AC1,$AC$12:$AC$14,IF(R16=AD1,$AD$12:$AD$12,IF(R16=AE1,$AE$12:$AE$14,IF(R16=AF1,$AF$12:$AF$16,IF(R16=AG1,$AG$12:$AG$14,IF(R16=AH1,$AH$12:$AH$14))))))))

Thanks for this:

=IF(R16=$AA$1,$AA$12:$AA$17,IF(R16=$AB$1,$AB$12:$AB$15,IF(R16=$AC$1,$AC$12:$AC$14,IF(R16=$AD$1,$AD$12:$AD$12,IF(R16=$AE$1,$AE$12:$AE$14,IF(R16=$AF$1,$AF$12:$AF$16,IF(R16=$AG$1,$AG$12:$AG$14,IF(R16=$AH$1,$AH$12:$AH$14))))))))

However, I once again get the error message: 'The list source must be a delimited list, or a reference to a single row or column.'

Frustrating - I'm almost there...! Can you assist?

Please attache Excel sheet.

Regards,
Suhas

Ok - so attached is the spreadsheet where I'm looking to use a combination of Data Validation and IF formulas, to categorize a customer list. Everything I've tried so far, I'm struggling to get the solution I require, so any help would be greatly appreciated.

On tab 1 are the Category, branch and sub-branch options. On tab 2 will be the list of customers where I'd like to be able to use data validation which will then filter out for the next option, depending on the previous category selected.

Hopefully somebody can come up with a solution for me!

Many thanks!

Could anybody please help with this as I can't seem to make any progress :-(

Thanks!

Hi Sj80,

I have worked a solution based on your requirement, it has some limitation & need some ground to be done, i have done a sample for Chemicals, if this solves your problem i will let you know next step to taken.

Punnam

hi Sj80,

I am waiting for answer .

Punnam

Hi Punnam,

Thanks for your assistance - it does seem to be working for the Chemicals category.
If it could be extended for the rest of the document and categories etc. I'm sure I'd be able to work with it.

Could you advise if this is possible?

Thanks!

Hi Sj80,

yes sure it will work for the rest of the cases also, but u need to do some manual work before it can happen , kindly have a look to the back up sheet, you need to make some name ranges based on the various Categories, Branches & Sub-branches, if u are aware of name manager you will be able to do the work on you own, if not i will do it for you but i need some time to complete the rest .

Punnam

Hi Punnam,

Thanks for the reply. I'm not quite sure what you mean and I am really an Excel novice when it comes to these kind of formulas etc.
If there is any chance you would be able to do the name manager as you refer to, it would be greatly appreciated.

Sj80

Hi Sj80,

No issue i will do it for you, but i will do it by tomorrow after noon only.

Punnam

Hi Sj80 ,

I am working on your sheet but i have been referring you previous posts & found that u are using excel 2003 , in that case my solution will not help you.as we can go only 7 nested if conditions in excel 2003 & 64 in case of excel 2007 and above.

Let me know was it possible for you to access file from excel 2007.

Punnam

Hi Sj80,

I have updated details up to COLORS , provide me more details i will complete the rest.

Punnam

Hi Punnam,

For the final 2 Categories, there are only limited options as per the document. For Electronic chemicals, there are no sub-branches, and for Distributors, there are only 2 sub-branches within the Cemical distributors branch.

If you could complete these, the document will be complete and I will be most grateful!