haj284

New Member
Joined
Jul 10, 2012
Messages
14
Hi, I have an excel project that I need a lot of help with :( I can't seem to grasp nested IF's or VLOOKUPS. Here is the information

Product pricing is A1 subtotal and shipping cost are D2 and E2 then the list of product supplies are A3 thru 17 and under worksheet named product prcing and shipping


The other worksheet is named invoice and Item is F15 and total is in h15 the question is

in the per unit column (invoice worksheet) enter a formula that uses table lookup in the product pricing Table (product pricing and shipping worksheet) based on the value selected in the item column. Use the IFEEROR function to display a blank cell instead of the error value. here are the two different worksheet. first i i will post the invoice worksheet then the product pricing and shipping. please help me with the formula

ItemColumn1Column3QtyPer UnitTotal
Economy Patient Gowns4
Doorknob Gripper5
Giant Tv Remote3

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>




SubtotalShipping Cost
Adjustable Home Bed Rail89.9506.00
Bed Cane81.95559.50
Doorknob Gripper4.9510012.50
Easy Grip Utensils32.9515016.00
Economy Patient Gowns6.95
Full-Page Magnifier4.99
Giant TV Remote34.95
Inflatable Shampoo Basin39.95
Jar Opener5.95
Lamp Switch Enlarger4.95
Medication Dispenser135.95
No Rinse Shampoo34.95
Tilting Overbed Table114.95
Trolley Walker139.95
Wheelchair Poncho51.95

<TBODY>
</TBODY><COLGROUP><COL><COL span=4></COLGROUP>
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254
If I understood well. You need a way to hide the errors and at the same time be able to perform calculations with the cells that have the Blank value correct?

The solution for it is to use a "0" instead of a blank value.

=IFERROR(VLOOKUP(A1,prices,2,false),0)

and then hide all the zero values. This is my favorite

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.
  • Select the cells that contain the zero (0) values that you want to hide.
  • On the Format menu, click Cells, and then click the Number tab.
  • In the Category list, click Custom.
  • In the Type box, type 0;-0;;@

More info here http://office.microsoft.com/en-us/excel-help/display-or-hide-zero-values-HP005199879.aspx
 

haj284

New Member
Joined
Jul 10, 2012
Messages
14
I tried that and everything is coming up zero even in cells where there should be numbers?? I wonder what I am doing wrong
 

haj284

New Member
Joined
Jul 10, 2012
Messages
14
I tried that and everything is coming up zero even in cells where there should be numbers?? I wonder what I am doing wrong
 

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254

ADVERTISEMENT

I tried that and everything is coming up zero even in cells where there should be numbers?? I wonder what I am doing wrong

Did you tweak my formula to fit your needs? The formula was for you to use as a guide but I cannot give you a formula unless I see your workbook or I manage to replicate your particular scenario. The second one is the least likely to succeed.

Thanks
 

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254

ADVERTISEMENT

You can upload it to google documents of use drop-box and get the link here
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,859
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
haj284, See if this helps with what fredlo2008 is saying to you

Excel Workbook
ABCDEF
1ItemColumn1Column3QtyPer UnitTotal
2Economy Patient Gowns**46.95*
3Doorknob Gripper**54.95*
4Giant Tv Remote**3 *
5Medication Dispenser***135.95*
6No Rinse Shampoo***34.95*
7Tilting Overbed Table***114.95*
Sheet1





Excel Workbook
ABCDE
1***SubtotalShipping
2Adjustable Home Bed Rail89.95*06
3Bed Cane81.95*559.5
4Doorknob Gripper4.95*10012.5
5Easy Grip Utensils32.95*15016
6Economy Patient Gowns6.95***
7Full-Page Magnifier4.99***
8Inflatable Shampoo Basin39.95***
9Jar Opener5.95***
10Lamp Switch Enlarger4.95***
11Medication Dispenser135.95***
12No Rinse Shampoo34.95***
13Tilting Overbed Table114.95***
14Trolley Walker139.95***
15Wheelchair Poncho51.95***
Prices
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,859
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe he is confused with my named range "prices" and the sheet name "Prices"
Just in case you are right I have kept away from the named range
Excel Workbook
ABCDEF
1ItemColumn1Column3QtyPer UnitTotal
2Economy Patient Gowns**46.95*
3Doorknob Gripper**54.95*
4Giant Tv Remote**3 *
5Medication Dispenser***135.95*
6No Rinse Shampoo***34.95*
7Tilting Overbed Table***114.95*
Sheet1

Excel Workbook
ABCDE
1***SubtotalShipping
2Adjustable Home Bed Rail89.95*06
3Bed Cane81.95*559.5
4Doorknob Gripper4.95*10012.5
5Easy Grip Utensils32.95*15016
6Economy Patient Gowns6.95***
7Full-Page Magnifier4.99***
8Inflatable Shampoo Basin39.95***
9Jar Opener5.95***
10Lamp Switch Enlarger4.95***
11Medication Dispenser135.95***
12No Rinse Shampoo34.95***
13Tilting Overbed Table114.95***
14Trolley Walker139.95***
15Wheelchair Poncho51.95***
product pricing and shipping
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,846
Messages
5,598,423
Members
414,237
Latest member
tereres

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