Data validation of a Formatted cell.

**ridlej1** · 09-24-2013, 04:35 AM

Hi,

I've been stressing over this for a while, so I gave up and turned to the internet.

The problem I'm faced with is I have a cell, containing a drop down box which looks up a named range. Eg A1, A2, A3, etc... And the cell next to it will contain B1, B2, B3, etc...
Is it possible to format the cell to have "A"# and "B"# where '#' would be the data from the named range? This would mean I could simplify my document because i would only need one named range as all the other information would be in the format. (There are currently over 50 named ranges)

Which brings me to my next point, can i populate a format automatically? So if my cell was "Dog" it would populate all the formats to be "Collie", "Spaniel" etc?

P.S I'm currently running excel 2003

**L-Drr** · 09-24-2013, 04:50 AM

Maybe it's just my English, but I can't figure out what you're trying to say.
You have a dropdown list with the values A1, A2, A3 etc (or with the values in those cells?), and the cell next to it had the OFFSET of 1 column to the right of the dropdown value... right?
Could you give an example of what you want changed? I don't get the "A"# part

**ridlej1** · 09-24-2013, 05:24 AM

Originally Posted by L-Drr

Maybe it's just my English, but I can't figure out what you're trying to say.
You have a dropdown list with the values A1, A2, A3 etc (or with the values in those cells?), and the cell next to it had the OFFSET of 1 column to the right of the dropdown value... right?
Could you give an example of what you want changed? I don't get the "A"# part

Thanks for the reply, it's probably just my description. The A's and B's were just examples, they're actually different tests, (I work in a lab) to add some more context i'll call them "Dis" and "Fria" so the dropdown list contains Dis 1, Dis 2, Dis 3, etc.. and Fria 1, Fria 2, Fria 3, etc... Where 'Dis 1-5' and'Fria 1-5' are different named ranges.
I'm hoping to simplify this so that i only need one named range of '1-5' and i could automatically format the cells so that they are formatted 'Dis' and 'Fria' and the dropdown list will change it between 1-5.

Hopefully that helps, i'm no excel guru.

**L-Drr** · 09-24-2013, 05:47 AM

Still having a little trouble, but maybe I get it

So you want the dropdown to be just 1, 2, 3, 4 or 5, and use that in a referral in combination with the words "Dis " or "Fria "?
Wherever you are using the referral, you could use "Dis "&A1 (A1 is dropdown cell)

If I'm still not getting it right, could you give a specific example? (e.g. "I want the dropdown to say 1 or 2 or 3, and in this cell put "Dis " of "Fria " in front of it, based on this condition, and that should result in this range")

**ridlej1** · 09-24-2013, 05:56 AM

Okay, lets try this..

I want cell A1 to be a drop box and containing "Dis" followed by a named range (1, 2 or 3). I want B1 to be a drop box saying "Fria" followed by the same named range of 1, 2 or 3. So i think i need to format the cell to say "Dis ##"
Second part
But i want A1 and B1 to populate the prefix (Dis and Fria) based on C1. So if C1 says Paracetamol, A1 should have "Dis" followed by the named range, and B1 should have "Fria" followed by the named range. If C1 says Calpol, i'd like A1 to say "Fria" Followed by the named range, and B1 to say "Moisture" followed by the named range.
I have a table with all of the tests and permutations that i'll need

**hemesh** · 09-24-2013, 06:26 AM

Hello Ridlej1 can you upload a sample book with all sensitive data removed and results manually indicated i.e. what you want and where you want??

**ridlej1** · 09-24-2013, 07:53 AM

I should have really done this earlier :P
Here is a smaller version of the spreadsheet in question. I need to make it simpilar, because it's taking up too much space on the company hard drive. There is usually over 2000 rows.

Thanks in advance