Attempting to use INDEX function for the first time

[Von Pookie]

I've got 2 worksheets with company names/addresses and the codes associated with them.

They are set up as follows:

Book1
	A	B	C	D
1	Code	CompanyName/Address	Billto
2	AMT2	Dummyco. 100ABCSt. Here,OH12345	Same
3	MOI2	TestProducts 5026thSt. There,IL12345	Same
4	BWP6	Made-upInc. 9003KingSt. Everywhere,NY12345	Dummyco. 100ABCSt. Here,OH12345
Sheet1

One worksheet is domestic companies, the other international.

I'm attempting to make a separate index page from the other 2 sheets, but instead of typing everything out, I figured I'd try this.

What I want to get is just the code, company name (no address), company location (no address or zip), and whatever is under bill to.

I've got the initial index function to work, however, I'm stuck on how to get just the company name or city/state-city/country from the one cell. Or even if I can, for that matter.

Any ideas? Or should I just start entering it all "by hand?"

Thanks,

 

[Yogi Anand]

Hi Kristy:

You may also want to try the following -- this one uses the simple LEFT, MID, CHAR, and FIND functions ...

Book1
	A	B	C	D	E	F	G
1	Code	CompanyNameAddress	BillTo		CompanyName	CompanyLocation	BillTo
2	AMT2	DummyCo. 100ABCSt. Here,OH12345	Same		DummyCo.	Here,OH	Same
3	MOI2	TestProducts 5026thSt. There,IL12345	Same		TestProducts	There,IL	Same
4	BWP6	Made-upInc. 9003KingSt. Everwhere,NY12345	DummyCo. 100ABCSt. Here,OH12345		Made-upInc.	Everwhere,NY	DummyCo. 100ABCSt. Here,OH12345
Sheet3a

Does it help?

 

[Von Pookie]

Thanks for the help, guys.

Unfortunately, I'll have to wait and try them tomorrow.

Either way, I'll let you know how it turns out

 

[Von Pookie]

Ok. Here we go again.

Doing better this time, though.

Aladin: I couldn't seem to get yours to work. Not to sound like we're "breaking up" or anything but, it's not you, it's me.

Yogi: While I don't quite understand how the formula is working, it seems to work great except for the one to pull out the location. I keep getting a #Value error after I change the B3 to the correct starting cell which is Domestic!$B4. It worked great on pulling out just the name, however.

Any ideas?

 

[Yogi Anand]

Hi Kristy:

I have used your worksheet name Domestic and started my CompanyNameAddress in cell B4 in the following simulation ...

y030326h1.xls
	A	B	C	D	E	F	G
1
2
3	Code	CompanyNameAddress	BillTo		CompanyName	CompanyLocation	BillTo
4	AMT2	DummyCo. 100ABCSt. Here,OH12345	Same		DummyCo.	Here,OH	Same
5	MOI2	TestProducts 5026thSt. There,IL12345	Same		TestProducts	There,IL	Same
6	BWP6	Made-upInc. 9003KingSt. Everywhere,NY12345	Made-upInc. 9003KingSt. Everywhere,NY12345		Made-upInc.	Everywhere,NY	Made-upInc. 9003KingSt. Everywhere,NY12345
Domestic

The formula for the CompanyLocation in this simulation is essentially the same as in my last post except for inclusion of sheet name Domestic -- I have however also used the CLEAN function to clean up any non-printing characters (such as the ALT+RETURN used in our CompanyNameAddress field).

So, the CompanyLocation bit should also have worked with the formulas in my earlier post. However, please try the modified formula in this post. You are welcome to email me your file -- I have sent you my email address in a PM to you.

 

[Aladin Akyurek]

Ok. Here we go again.

Doing better this time, though.

Aladin: I couldn't seem to get yours to work. Not to sound like we're "breaking up" or anything but, it's not you, it's me.
[...]
Any ideas?

Want me to send you a WB housing WMID formulas? If yes, you need to install themorefunc.xlladd-in.

 

[Von Pookie]

Yogi: For some reason, I'm getting a Javascript error on the page and can't see all of the formulas. I can see the one for E4, but it's not working for the rest of them

Aladin: I did download the morefunc thing you mentioned. Actually, I used the link from your signature. It's installed and everything, I'm just too stupid to figure out how you got the formulas to work

 

[Yogi Anand]

Edit: After receiving your workbook, I corrected the formula for CompanyLocation (as shown below)

Hi Kristy:

The formulas I have in my post are as follows ...

CompanyName:
'=LEFT(Domestic!$B4,FIND(CHAR(10),Domestic!$B4)-1)

CompanyLocation:
'=LEFT(MID(Domestic!$B4,FIND("~",SUBSTITUTE(Domestic!$B4,CHAR(10),"~",LEN(Domestic!$B4)-LEN(SUBSTITUTE(Domestic!$B4,CHAR(10),""))))+1,255),FIND(",",MID(Domestic!$B4,FIND("~",SUBSTITUTE(Domestic!$B4,CHAR(10),"~",LEN(Domestic!$B4)-LEN(SUBSTITUTE(Domestic!$B4,CHAR(10),""))))+1,255))+3)

I hope this helps!

 

[Yogi Anand]

Hi Kristy:

Here is the simulation with the updated formula for the CompanyLocation ...

y030326h1.xls
	A	B	C	D	E	F	G
1
2
3	Code	CompanyNameAddress	BillTo		CompanyName	CompanyLocation	BillTo
4	AMT2	AMTEX 1500KingswayDr. Lebanon,OH12345	Same		AMTEX	Lebanon,OH	Same
5	MOI2	TestProducts 5026thSt. There,IL12345	Same		TestProducts	There,IL	Same
6	BWP6	Made-upInc. 9003KingSt. Everywhere,NY12345	Made-upInc. 9003KingSt. Everywhere,NY12345		Made-upInc.	Everywhere,NY	Made-upInc. 9003KingSt. Everywhere,NY12345
7	MA10	Bridgestone/Firestone,Inc TechnologyCompany 1600FirestonePkwy. Akron,OH77317-0001	MM35		Bridgestone/Firestone,Inc	Akron,OH	MM35
Domestic

I hope all is well now!