Microsoft Office Forums Calculate formula base of list menu
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-20-2010, 03:41 AM
 rkeles Windows XP Office 2007 Novice Join Date: Sep 2010 Posts: 7
Calculate formula base of list menu

Hi,
I would like to make a quotation form in excel with separate rates for different items. I will explain how it will work and I wish you help me to do it.

Quotations for;
· Lambiri
· Doors
· Partitions
As you see on picture rates are different. For lambiri 20,000.00, for doors 40,000.00 for partition 70,000.00
In the Column G rate has been calculated manually. In G6 this formula works =D6*A3
In G7, =D7*B3
But for easy way I would like to formula different way to make it quick and be more professional.
In the Column E (ITEM) ; there is list menu “Lambiri – Door – Partition
And as you see on top left corner what the RATES are.
Now my question is:
If I select an item from list menu, rate will be what ever in RATES shows.
For example;
If I select item from list menu “Partition” it will calculate result in to G8, C3*D8
Or if select item “Door” in same cell, it will calculate result in to cell, B3*D8
I wish you can help me to resolve this…

Thank you…
#2
09-20-2010, 10:07 AM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

=HLOOKUP(E6,\$A\$2:\$C\$3,2,0)*D6
in cell G6 and copy down

By the way, you are multiplying in column G, but doesn't the label in cell G5 imply division?
#3
09-21-2010, 05:19 AM
 rkeles Windows XP Office 2007 Novice Join Date: Sep 2010 Posts: 7

Quote:
 Originally Posted by Kimberly =HLOOKUP(E6,\$A\$2:\$C\$3,2,0)*D6 in cell G6 and copy down By the way, you are multiplying in column G, but doesn't the label in cell G5 imply division?
Thanks Kimberly,
It works exactly what I want..

But I used it in Table, its give me #N/A error when i dont enter a value other cell.. (because of empy cell)

is there any solision for this error. on bottom of table I can not get the sum of amount because of this error. I want it to calculate empty cell as "0"

is it possiable...

thanks..
#4
09-21-2010, 02:09 PM
 Kimberly Windows 7 Office 2010 (Version 14.0) Expert Join Date: May 2010 Posts: 517

=iferror(hlookup(e6,\$a\$2:\$c\$3,2,0)*d6,0)
#5
09-22-2010, 12:38 AM
 rkeles Windows XP Office 2007 Novice Join Date: Sep 2010 Posts: 7

Quote:
 Originally Posted by Kimberly =iferror(hlookup(e6,\$a\$2:\$c\$3,2,0)*d6,0)
it works well now..
Thank you...

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Cpat Excel 2 06-09-2010 02:07 PM mbcrabber Mail Merge 4 06-06-2010 01:25 PM acedking90 Mail Merge 0 07-27-2009 11:04 AM prayojana Project 0 11-12-2007 01:13 AM jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:44 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top