Creating Formula - Is it even possible?

tnemom

Board Regular
Joined
May 14, 2007
Messages
67
test.xls
HIJKLMNOPQRSTUVWXYZ
1CatID#ELECTMTCHoursInv.Material$Mutiplier09645096550965609751097520975709889102361067310675106761084210877
2108429655#N/A1.5813310317206365573
396459655#N/A1402310317206365573
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

The Todal

Board Regular
Joined
Jan 2, 2007
Messages
129
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

Board Regular
Joined
May 14, 2007
Messages
67
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
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Try this formula in L2 copied down

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
May 14, 2007
Messages
67

ADVERTISEMENT

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

tnemom

Board Regular
Joined
May 14, 2007
Messages
67
Barry,

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top