Creating Formula - Is it even possible?

[tnemom]

test.xls
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	CatID#			ELECTMTCHours	Inv.Material$	Mutiplier	09645	09655	09656	09751	09752	09757	09889	10236	10673	10675	10676	10842	10877
2	10842	9655	#N/A	1.5	81	3	3	10	3	1	7	20	6	3	6	5	5	7	3
3	9645	9655	#N/A	1	40	2	3	10	3	1	7	20	6	3	6	5	5	7	3
Buchanan Survey

This is a partial of the sheet that I am working on.

The current Formula in Column L is manually entered for each line. What I would like to know is if there is a way to have a macro that would create this formula?

What we would need it to do is check out the Cat ID #'s in cells H - J and based upon the numbers in there, look up the $ amount (right now they are in columns N-Z). The amount in column M would be manually input and then the formula in column L would be

the value in M multiplied by the sum of the values that correspond with the ID #'s in H-J plus 10.

Is this possible?
Thanks,
TNEMOM

 

[The Todal]

Is there any reason why the values in columns N - Z must be repeated in each row? If not, these could be placed on another sheet in your workbook and then it becomes a simple matter of using either HLOOKUP to capture the Cat ID values you need for your formula.

 

[tnemom]

No they don't need to be repeated - I plan on making a list on an alternate sheet. This is the way the sheet was presented to me and I was asked if I could simplify since the user manually enters everything.

If I could get assistance with the formula, it would be much appreciated.

Tnemom

 

[tnemom]

Is there anyone who could help me with the formula?

Thanks

 

[barry houdini]

Try this formula in L2 copied down

=(SUMPRODUCT(--ISNUMBER(MATCH(N$1:Z$1,H2:J2,0)),N2:Z2)+10)*M2

 

[Richard Schollar]

Hi

How do you come up with the value in L3? Shouldn't it be looking up 9645 and 9655, giving a result of 46?

 

[tnemom]

Yes, that should be the result in L3...the formula is wrong.

 

[tnemom]

Barry,

When I put that formula in L2 - I get the result of 51 not 81

 

[barry houdini]

OK, I suspect that I2 is not deemed to be a match with O1 because I2 is a numeric value 9655 and O1 is a text formatted 09655.

You probably need to change N1:Z1 so these are all numeric values

You can do that like this:

Copy any empty cell
Select N1:Z1
Edit > Paste Special > "add" > OK

If you wish you can display 9655 as 09655 by using a custom format of 00000 in N1:Z1

 

[tnemom]

Thanks Barry!!

Works perfectly now