How to Add Only Cells With Values

c.clavin

Board Regular
Joined
Mar 22, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hey guys, I'm attempting to create a Purchase Order from items selected on my 'master inventory list' and transfer those items to a 'Purchase Order' sheet that is formatted the way I'd like for printing/faxing purposes. My 'master inventory list' is ~800 rows, so there's no way I'd want to print ALL of them out on a purchase order, in addition to confusing my vendors. I'm pretty sure there's a formula to exclude all cells without values. Can someone here please help me? Thank you very much.

I've included a photo of the Purchase Order sheet on the left, and 'Master Inventory' list on the right to make it clearer.

QUESTION_zps515a9bf5.jpg Photo by cclavin1 | Photobucket
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

c.clavin

Board Regular
Joined
Mar 22, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Can I take all of the cells and make a pivot table, and use only the cells with values?
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,069
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The SUM function excludes text and blanks.
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,069
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks. Would this keep my categories and individual numbers as well?

Sorry, I don't know what you mean by that. Try posting your data directly to this site.
 
Upvote 0

c.clavin

Board Regular
Joined
Mar 22, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Sorry, I don't know what you mean by that. Try posting your data directly to this site.

I want to use columns in my master inventory list to make up purchase orders on my purchase order template. If I have Column A saying apple, orange, pear, banana, pineapple, lemon and Column B saying 1,2,1,0,2,1 how do I transfer all cells besides 'banana' to my purchase order sheet? I have a master inventory list of over 700 items, but obviously I don't order every item every time. I'd like to take all cells with 0's or no values and hide them for the purchase order sheet, showing only the items with 1,2,3,etc in the 'ordered' column. Is this possible?
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,069
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Filter the quantity column on your Master using greater than 0, then copy and paste the visible cells to your purchase order sheet.
 
Upvote 0

c.clavin

Board Regular
Joined
Mar 22, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks for the response, I was looking for something that would take the 'non zero quantity' cells and auto-input them into the purchase order template for automatic processing. Do you know of any way this would work?
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,069
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks for the response, I was looking for something that would take the 'non zero quantity' cells and auto-input them into the purchase order template for automatic processing. Do you know of any way this would work?
That can be done with VBA. Whoever writes the code will need to know the layouts and names of both sheets so the correct data can be extracted and placed in the right place.
 
Upvote 0

Forum statistics

Threads
1,191,501
Messages
5,986,927
Members
440,067
Latest member
Swatts1

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