Excel Formula Help

Desmo · 26-02-2008, 17:40

Hey hey

Need some help with this formula that my dumb excel brain can't get to work.

This is a very simplified chart, but what I need to do is cross reference some of the information when I fill in the fields.
What I want to do is fill in Amount (B9 Yellow box) and Size (B10 Orange box) which then cross references the information and gives me the total.

So, Amount = 2000, Size = A4 Double, Total should = 3

If I can get this working, I can add in other charts using the same formula and hopefully make our quoting system a lot easier and quicker

Matblack · 26-02-2008, 17:45

Like this?

http://www.bettersolutions.com/excel...N420111612.htm

MB

Dymetrie · 26-02-2008, 17:50

Alternatively a nice long 'IF'/'AND'/'OR' formula would do the job...

Desmo · 26-02-2008, 17:58

Seen that link MB but couldn't get it working :/

kaiowas · 26-02-2008, 18:10

=OFFSET(A1,MATCH(B9,A2:A5,1),MATCH(B10,B1:E1,1))

Dymetrie · 26-02-2008, 18:23

Quote:

Originally Posted by kaiowas

=OFFSET(A1,MATCH(B9,A2:A5,1),MATCH(B10,B1:E1,1))

Keep getting either errors or incorrect results on that one...

Could just be Excel 2007 though....

Desmo · 26-02-2008, 18:28

Excellent, looks like Phil's is working great

Not sure what I was doing wrong earlier. Just need to expand it now....cheers Phil

Dymetrie · 26-02-2008, 18:42

Yup, after chatting to young Desmo. He changed the '1's to '0's and doing the same on mine made it work (under 2007 and 2000).

Fun Fun Fun

kaiowas · 27-02-2008, 09:18

Oops, I knew it should have been '0'. No idea why I typed '1'

Dymetrie · 27-02-2008, 09:19

Quote:

Originally Posted by kaiowas

Oops, I knew it should have been '0'. No idea why I typed '1'

YOU *******! YOU MADE ME DOUBT THE POWER OF OFFICE 2007!!!!

26-02-2008, 17:40	#1
Desmo The Last Airbender Join Date: Jun 2006 Location: Pigmopad Posts: 11,915	Excel Formula Help Hey hey Need some help with this formula that my dumb excel brain can't get to work. This is a very simplified chart, but what I need to do is cross reference some of the information when I fill in the fields. What I want to do is fill in Amount (B9 Yellow box) and Size (B10 Orange box) which then cross references the information and gives me the total. So, Amount = 2000, Size = A4 Double, Total should = 3 If I can get this working, I can add in other charts using the same formula and hopefully make our quoting system a lot easier and quicker __________________

26-02-2008, 17:45	#2
Matblack Baby Bore Join Date: Jun 2006 Location: Svalbard Posts: 9,770	Like this? http://www.bettersolutions.com/excel...N420111612.htm MB __________________ "we had roots that grew towards each other underground, and when all the pretty blossom had fallen from our branches we found that we were one tree and not two"

26-02-2008, 17:50	#3
Dymetrie A large glass of Merlot Join Date: Jun 2006 Location: Letchworth with a Lightsaber Posts: 5,819	Alternatively a nice long 'IF'/'AND'/'OR' formula would do the job... __________________ Khef, Ka and Ka-Tet....

26-02-2008, 17:58	#4
Desmo The Last Airbender Join Date: Jun 2006 Location: Pigmopad Posts: 11,915	Seen that link MB but couldn't get it working :/ __________________

26-02-2008, 18:10	#5
kaiowas The Stig Join Date: Jul 2006 Location: Fightertown USA Posts: 1,458	=OFFSET(A1,MATCH(B9,A2:A5,1),MATCH(B10,B1:E1,1)) __________________ Anal Fish Porn

26-02-2008, 18:28	#7
Desmo The Last Airbender Join Date: Jun 2006 Location: Pigmopad Posts: 11,915	Excellent, looks like Phil's is working great Not sure what I was doing wrong earlier. Just need to expand it now....cheers Phil __________________

26-02-2008, 18:42	#8
Dymetrie A large glass of Merlot Join Date: Jun 2006 Location: Letchworth with a Lightsaber Posts: 5,819	Yup, after chatting to young Desmo. He changed the '1's to '0's and doing the same on mine made it work (under 2007 and 2000). Fun Fun Fun __________________ Khef, Ka and Ka-Tet....

27-02-2008, 09:18	#9
kaiowas The Stig Join Date: Jul 2006 Location: Fightertown USA Posts: 1,458	Oops, I knew it should have been '0'. No idea why I typed '1' __________________ Anal Fish Porn