Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-20-2010, 03:41 AM
rkeles rkeles is offline Calculate formula base of list menu Windows XP Calculate formula base of list menu Office 2007
Novice
Calculate formula base of list menu
 
Join Date: Sep 2010
Posts: 7
rkeles is on a distinguished road
Default 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…
Reply With Quote
  #2  
Old 09-20-2010, 10:07 AM
Kimberly Kimberly is offline Calculate formula base of list menu Windows 7 Calculate formula base of list menu Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

=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?
Reply With Quote
  #3  
Old 09-21-2010, 05:19 AM
rkeles rkeles is offline Calculate formula base of list menu Windows XP Calculate formula base of list menu Office 2007
Novice
Calculate formula base of list menu
 
Join Date: Sep 2010
Posts: 7
rkeles is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
=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..
Reply With Quote
  #4  
Old 09-21-2010, 02:09 PM
Kimberly Kimberly is offline Calculate formula base of list menu Windows 7 Calculate formula base of list menu Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

=iferror(hlookup(e6,$a$2:$c$3,2,0)*d6,0)
Reply With Quote
  #5  
Old 09-22-2010, 12:38 AM
rkeles rkeles is offline Calculate formula base of list menu Windows XP Calculate formula base of list menu Office 2007
Novice
Calculate formula base of list menu
 
Join Date: Sep 2010
Posts: 7
rkeles is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't calculate formulas Cpat Excel 2 06-09-2010 02:07 PM
How do I set up the fields in mail merge word 07 from my data base mbcrabber Mail Merge 4 06-06-2010 01:25 PM
Need help creating a data base for mail merge. acedking90 Mail Merge 0 07-27-2009 11:04 AM
Calendar Base prayojana Project 0 11-12-2007 01:13 AM
Calculate formula base of list menu How to get cells to calculate time? 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.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft