If formula too long

**intron** · 12-09-2007, 06:17 AM

Hi,

Using Excel 2002, SP3 on a Windows XP Machine.

I am trying to do a fairly simple thing that required a huge formula.

I have "labels" in column C3, on another worksheet (called 'codes') I have a list of new 'codes' for the things in C3. Each value in C3 can be one of 50. All 50 are represented in the 'codes'. There are approximately 1000 cells in C3. I would rather not do this by hand since the codes are things like "000000000000000000000000000100000000000"

This is my current formula:

=IF((C3="CC-A01"),codes!B1,IF((C3="CC-A02"),codes!B2,IF((C3="CC-A03"),codes!B3,IF((C3="CC-A04"),codes!B4,IF((C3="CC-A05"),codes!B5,IF((C3="CC-A06"),codes!B6,IF((C3="CC-A07"),codes!B7,IF((C3="CC-A08"),codes!B8,IF((C3="CC-A09"),codes!B9,IF((C3="CC-A10"),codes!B10,IF((C3="CC-A11"),codes!B11,IF((C3="CC-A12"),codes!B12,IF((C3="CC-A13"),codes!B13,IF((C3="CC-A14"),codes!B14,IF((C3="CC-A15"),codes!B15,IF((C3="CC-A16"),codes!B16,IF((C3="CC-A17"),codes!B17,IF((C3="CC-A18"),codes!B18,IF((C3="CC-A19"),codes!B19,IF((C3="CC-A20"),codes!B20,IF((C3="CC-A21"),codes!B21,IF((C3="CC-A22"),codes!B22,IF((C3="CC-A23"),codes!B23,IF((C3="CC-A24"),codes!B24,IF((C3="CC-A25"),codes!B25,IF((C3="CC-A26"),codes!B26,IF((C3="CC-A27"),codes!B27,IF((C3="CC-A28"),codes!B28,IF((C3="CC-A29"),codes!B29,IF((C3="CC-A30"),codes!B30,IF((C3="CC-A31"),codes!B31,IF((C3="CC-A32"),codes!B32,IF((C3="CC-A33"),codes!B33,IF((C3="CC-A34"),codes!B34,IF((C3="CC-A35"),codes!B35,IF((C3="CC-A36"),codes!B36,IF((C3="CC-A37"),codes!B37,IF((C3="CC-A38"),codes!B38,IF((C3="CC-A39"),codes!B39,IF((C3="CC-A40"),codes!B40,IF((C3="CC-A41"),codes!B41,IF((C3="CC-A42"),codes!B42,IF((C3="CC-A43"),codes!B43,IF((C3="CC-A44"),codes!B44,IF((C3="CC-A45"),codes!B45,IF((C3="CC-A46"),codes!B46,IF((C3="CC-A47"),codes!B46,IF((C3="CC-A48"),codes!B48,IF((C3="CC-A49"),codes!B49,IF((C3="CC-A50"),codes!B50,""))))))))))))))))))))))))))))))))))))))))))))))))))

Any ideas would be appreciated!

**mikerickson** · 12-09-2007, 06:42 AM

Welcome to the forum.

That formula can be replaced with
=INDIRECT("codes!B"&RIGHT(C3,2))

I would think about putting a column of "CC-A03" type labels next to the column of codes and using VLOOKUP.

**VBA Noob** · 12-09-2007, 06:49 AM

I would suggest setting up a table and using vlookup

or you could try

=INDIRECT(LOOKUP(RIGHT(C3,2),{"01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40"},
{"codes!b1","codes!b2","codes!b3","codes!b4","codes!b5","codes!b6","codes!b7","codes!b8","codes!b9","codes!b10","codes!b11","codes!b12","codes!b13","codes!b14","codes!b15","codes!b16","codes!b17","codes!b18","codes!b19","codes!b20","codes!b21","codes!b22","codes!b23","codes!b24","codes!b25","codes!b26","codes!b27","codes!b28","codes!b29","codes!b30","codes!b31","codes!b32","codes!b33","codes!b34","codes!b35","codes!b36","codes!b37","codes!b38","codes!b39","codes!b40"}))

amended

I would go with mikerickson

VBA Noob

**intron** · 12-09-2007, 07:01 AM

1. I can't believe how fast you guys were.

2. I can't believe that can be replaced with "=INDIRECT("codes!B"&RIGHT(C3,2))"

I use Excel everyday but its for pretty straightforward stuff - I am always impressed by people that know how to really squeeze everything out of Excel. Amazing.

Thanks so much and now I am off to investigate two new commands.

**Aladin Akyurek** · 12-09-2007, 07:06 AM

Originally Posted by intron

Hi,

Using Excel 2002, SP3 on a Windows XP Machine.

I am trying to do a fairly simple thing that required a huge formula.

I have "labels" in column C3, on another worksheet (called 'codes') I have a list of new 'codes' for the things in C3. Each value in C3 can be one of 50. All 50 are represented in the 'codes'. There are approximately 1000 cells in C3. I would rather not do this by hand since the codes are things like "000000000000000000000000000100000000000"

This is my current formula:

=IF((C3="CC-A01"),codes!B1,IF((C3="CC-A02"),codes!B2,IF((C3="CC-A03"),codes!B3,IF((C3="CC-A04"),codes!B4,IF((C3="CC-A05"),codes!B5,IF((C3="CC-A06"),codes!B6,IF((C3="CC-A07"),codes!B7,IF((C3="CC-A08"),codes!B8,IF((C3="CC-A09"),codes!B9,IF((C3="CC-A10"),codes!B10,IF((C3="CC-A11"),codes!B11,IF((C3="CC-A12"),codes!B12,IF((C3="CC-A13"),codes!B13,IF((C3="CC-A14"),codes!B14,IF((C3="CC-A15"),codes!B15,IF((C3="CC-A16"),codes!B16,IF((C3="CC-A17"),codes!B17,IF((C3="CC-A18"),codes!B18,IF((C3="CC-A19"),codes!B19,IF((C3="CC-A20"),codes!B20,IF((C3="CC-A21"),codes!B21,IF((C3="CC-A22"),codes!B22,IF((C3="CC-A23"),codes!B23,IF((C3="CC-A24"),codes!B24,IF((C3="CC-A25"),codes!B25,IF((C3="CC-A26"),codes!B26,IF((C3="CC-A27"),codes!B27,IF((C3="CC-A28"),codes!B28,IF((C3="CC-A29"),codes!B29,IF((C3="CC-A30"),codes!B30,IF((C3="CC-A31"),codes!B31,IF((C3="CC-A32"),codes!B32,IF((C3="CC-A33"),codes!B33,IF((C3="CC-A34"),codes!B34,IF((C3="CC-A35"),codes!B35,IF((C3="CC-A36"),codes!B36,IF((C3="CC-A37"),codes!B37,IF((C3="CC-A38"),codes!B38,IF((C3="CC-A39"),codes!B39,IF((C3="CC-A40"),codes!B40,IF((C3="CC-A41"),codes!B41,IF((C3="CC-A42"),codes!B42,IF((C3="CC-A43"),codes!B43,IF((C3="CC-A44"),codes!B44,IF((C3="CC-A45"),codes!B45,IF((C3="CC-A46"),codes!B46,IF((C3="CC-A47"),codes!B46,IF((C3="CC-A48"),codes!B48,IF((C3="CC-A49"),codes!B49,IF((C3="CC-A50"),codes!B50,""))))))))))))))))))))))))))))))))))))))))))))))))))

Any ideas would be appreciated!

=INDEX(codes!$B$1:$B$50,SUBSTITUTE(UPPER(C3),"CC-A","")+0)

**intron** · 12-09-2007, 11:13 PM

Hi again,

As with all things, it appears that there is more than one way to skin a cat.

I have decided to go with mikerickson's very simple version:

=INDIRECT("codes!B"&RIGHT(C3,2))

I am trying to understand exactly what it is doing rather than just assuming it works and gleefully copying it over. Its quite obvious it works but if anyone has the time, it would be great if I could get a quick explanation of the code.

What I have gathered so far is that:

RIGHT(Cx,2) is used to return the last two characters of the text in column C
(so, it will just see "01" rather than "CC-A01".

INDIRECT *somehow* looks at column B in worksheet "codes" and returns the the correct column B code that is associated with the text that RIGHT provides.

So... my question is how does it know, based on the information we provide, that the text that RIGHT returns will be found in column A of worksheet codes and that the value needed is in column B on the same row that the RIGHT value is found?

Jezz... I hope that makes sense.

In other words if my worksheet looks like this (with 4 columns, A-D):

1 infox infoy CC-A01 (formula)
2 infox infoy CC-A10 (formula)
3 infox infoy CC-A14 (formula)
4 infox infoy CC-A01 (formula)

and my worksheet "codes" has this (with 2 columns, A and B):

1 CC-A01 100000000000000
2 CC-A02 010000000000000
3 CC-A03 001000000000000
4 CC-A04 000100000000000
5 CC-A05 000010000000000
6 CC-A06 000001000000000

Then how does this formula tell it to do what it does?

I will likely need to recreate this in the future and I am trying to harass you just once

Thanks for all of the kind suggestions.

etc