LF Help in Excel - NS4Sale

bossman · 02-06-2008, 09:17 PM

Hey,
I have tried to find the answer out for myself but getting very frustrated with a formula i am trying in excel.

What I am trying to get is the following

I have 2 Colums of Text

Col1 AA, BB, CC, DD, EE
Col2 PP, QQ, RR, SS, TT

And I want to enter a txt value in a cel (ex A3) and want Excel to match the text in either colum and report the Colum header (either COL1 or Col2) back into cell B3.

please email or reply here to help me out.

thansk
Bossman

bossman33 @ ns.sympatico.ca

Rednecktech · 02-06-2008, 11:05 PM

So you want B3 to display the text of A3 as well as the column it's come from? Wouldn't B3 be in the "B" column therefore making it only possible for the reported text to have come from column "A" since there's only 2 columns?

Can you upload a sample sheet?

Jackie · 02-07-2008, 02:57 AM

They have night classes at the community college. I believe it's a 6 week program, 2 nights per week.

bossman · 02-07-2008, 12:42 PM

Quote:

Originally Posted by Jackie

They have night classes at the community college. I believe it's a 6 week program, 2 nights per week.

WTF? I am asking for help on 1 formula ,not help in locating a course.. I am proficiate in Excel but no expert. Sometimes even top programmers get stuck in Excel on complicated formula's so asking for help in the tech section of a forum doesn't mean I don't know Excel. Thanks for the reply anyway.

bossman · 02-07-2008, 12:52 PM

Quote:

Originally Posted by Rednecktech

So you want B3 to display the text of A3 as well as the column it's come from? Wouldn't B3 be in the "B" column therefore making it only possible for the reported text to have come from column "A" since there's only 2 columns?

Can you upload a sample sheet?

Ok.. here is the sample sheet.

1)What I want is a formula that in cell A2 I enter a value (can be any 2 char code AA, A1, C2 etc)

2)The formula in Cell B2 then takes that value from A2 and checks it with the cells under Col1 for a match if there is a match it displays the header of that range of cells in our case Col1 would be the output in B2 if any of these values (AA,BB,CC,DD,EE) were entered in A2

3)If no match was found in Col1 then check for match in Col2. So if A2 matched (PP,QQ,RR,SS,TT) then Col2 would be displayed in cell B2.

Hope this clairfies the problem a little more.

thanks for the help.
Bossman

Agility4mercy · 02-07-2008, 03:10 PM

Ok, I'm some what educated with Excel, but you have me lost as to you want the program to do.

Are you trying to develop a search text formula? Or am I misunderstanding?

BTW What are you doing this for?? Sometimes there is an easier why to create formula it just depends on what you need it for!

bossman · 02-07-2008, 09:21 PM

it's for a spreadsheet at work. We can enter a bunch of different items (cell A2) but they only have 2 sizes (hense Col1 and Col2). So I want to list all the common sized item in Col1 and the other size in Col2 so when the value inputed into cell A2 the formula looks to see which column the matching text resides and outputs the size for that item in cell B2.

Does that help?

Willy_Weed · 02-07-2008, 09:47 PM

The Lookup formula should be able to do that, with some massaging. I'll look at it tomorrow.

bossman · 02-10-2008, 11:12 AM

Anyone?

Willy_Weed · 02-11-2008, 07:51 AM

I forgot about this

bossman · 02-11-2008, 12:18 PM

can ya help me Willy? Do you think the IF along with an OR will work?

Willy_Weed · 02-11-2008, 03:01 PM

Add the Analysis toolpack, see attached (I hope)

bossman · 02-11-2008, 05:35 PM

Willy my man.. I think we can work with that. Thanks .. next time we make a deal the coffee or beer is on me.