# How to Add Only Cells With Values

#### c.clavin

##### Board Regular
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
Can I take all of the cells and make a pivot table, and use only the cells with values?

#### JoeMo

##### MrExcel MVP
The SUM function excludes text and blanks.

#### c.clavin

##### Board Regular
The SUM function excludes text and blanks.

Thanks. Would this keep my categories and individual numbers as well?

#### JoeMo

##### MrExcel MVP
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.

#### c.clavin

##### Board Regular
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?

#### JoeMo

##### MrExcel MVP
Filter the quantity column on your Master using greater than 0, then copy and paste the visible cells to your purchase order sheet.

#### c.clavin

##### Board Regular
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?

#### JoeMo

##### MrExcel MVP
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.

Replies
1
Views
313
Replies
1
Views
379
Replies
1
Views
938
Replies
0
Views
289
Replies
5
Views
547

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.

### Which adblocker are you using?

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

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