How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Microsoft
Satisfied Customers: 6706
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Question Here...
Jess M. is online now

There, I would like to set up a print report from a sheet

Resolved Question:

Hi There,
I would like to set up a print report from a sheet consisting of columns A to G. If the value in column G is zero I would like for that line not to come out in my print report.
Can you tell me if this can be done and if so how I can learn how to set it up.
Thank you
Melissa
Submitted: 2 years ago.
Category: Microsoft
Expert:  Jess M. replied 2 years ago.
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Are you doing this in MS Excel?
Also, based on your description, column A to F will ALWAYS print, except for G wich only print if it is NOT zero. Is this correct?

Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 2 years ago.

Hi there,

I am using Microsoft.

No I would like for the entire line not to print if column G has a zero balance.

Reason for asking:

I am setting up a ordersheet and on my spreadsheet I have all our products but only want a report with the products I would like to order; as in have a value in column G.

Melissa

Expert:  Jess M. replied 2 years ago.
Hi Melissa,

Thank you for writing back with that information. Can you send me a sample or dummy excel file with few data so that I can directly see what you want to achieve? You can upload the sample or dummy excel file here when you reply, or you can upload it to http://filesXpress.com and then give me the short download link.

Thank you.
Jess
Customer: replied 2 years ago.

http://filesxpress.com/d-b4693972

can you see the file?

Expert:  Jess M. replied 2 years ago.
Melissa,

Yes, I got the sample file. Which sheet are you referring to? Is it the DC Par or the Count Sheet?
Customer: replied 2 years ago.

Yes, it is actually H tho with the zero balance.

I need to get a report from this sheet that only has the products that I want to order not the ones I already have.

Thank you for your help

Expert:  Jess M. replied 2 years ago.
Hi Melissa,

Thank you for that clarification. So you are still to create that separate sheet that summarizes the products to order and that information is based on the DC Par sheet with respect to the ORDER column in G.

Please allow me to create that Order Sheet for you. However, to better create that order sheet, I need to rearrange the DC Par data for lookup purposes.

Please give me a moment to do this for you.

I will keep you posted.

Best regards,
Jess
Customer: replied 2 years ago.

Wow!

That is amazing, thank you.

Melissa

Expert:  Jess M. replied 2 years ago.
You're welcome. Please give me a moment and I will give you the download link when I have completed the file for you.

Best regards,
Jess
Customer: replied 2 years ago.

Hi there,

I have to pop out now and haven't used your service before, will you still be here when I get back or how do I get back in contact with you. My e-mail is***@******.*** if we lose contact.

Melissa

Expert:  Jess M. replied 2 years ago.
Hi Melissa,

Thank you for writing back. I am still working on your Excel file and this can take a while. Yes, you can leave this and just bookmark this page so that you can easily get back to me here when you are back online. You can also use the link in the email that you receive from our system to get back to me in this question.

Thank you.

Best regards,
Jess
Customer: replied 2 years ago.

great, thank you.

be back in touch soon.

Expert:  Jess M. replied 2 years ago.
Thanks, ***** ***** your time.

Regards,
Jess
Expert:  Jess M. replied 2 years ago.
Hi Melissa,

Thank you for patiently waiting. After severak hours of building the right formula for you, I have completed your Excel file requirements. So here are the things I did:

1. I created 2 Order Sheets. (Order CATS means cetegories are retained while Order ALL means all items to buy no category shown)

2. In these 2 order sheets, I retained the other columns in the original DC Par.

3. In either of the 2 order sheets, only items for order, that is, with Order value > 0 that are shown for printing.

4. Column A uses array formulas to list the items where order value is greater than 0. The rest of the columns use VLOOKUP.

Here is the final file for you to download:
http://filesxpress.com/d-8d165d03

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess
Customer: replied 2 years ago.

Hi there,

I have received the file and this is very close to what I am after. The only thing extra is that I don't want the blank lines.

Is this possible?

Thank you

Melissa

Expert:  Jess M. replied 2 years ago.
Hi Melissa,

Thank you for writing back and I am glad that you got the file I created for you. Unfortunately, the only way to remove the blank lines is by combining the items for order thus removing the categories (see the other sheet named Order Sheet ALL).

Using the array formula I built for you, Excel will only show the product item when Order column is NOT zero as you desired. However, if you want to retain the categories, the "search" for these product items will be limited to the actual number of items in each category.

For example, for Champagne & Wine, there are 24 rows including your original 2 blanks. Now, the AREA allocated in the report for Champagne & Wine is also 24. It is WITHIN this area that the search results will be displayed. So if only 16 items are to be listed, the bottom part of the allocated area will be left blank.

As you can see, the actual report area for each category IS THE SAME as the source data -- DC Par. So there is really no way to eliminate the blank lines since they too contain the same formula with the non-blank lines since if their order column is NOT zero, the products will show up there in those lines.

So the only way to eliminate blank lines is to perform the search and lookup as one data WITHOUT the categories like the other sheet I provided called Order Sheet ALL.

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need assistance in the future, you can request me any time by posting a new question starting with "For Jess M" so that I can assist you immediately.

Thank you!

Best regards,
Jess
Customer: replied 2 years ago.

I am so sorry, I misread my own spreadsheet. That is so brilliant and I will definitely be giving you the biggest smiley face ever.

The only thing I would still like to know is if I need to change the Par level and product listing or move things around is there anything i need to know.

I was hoping you would have shown me how to do it myself but it does look a bit complicated for me so I am also appreciative that you did it for me.

If you can explain that would be great but I am already very happy. Thank you

Expert:  Jess M. replied 2 years ago.
Hi,

Thank you for writing back and I am glad that the Excel file I made for you helped. Now, regarding your concerns, the only thing that matters in the DC Par sheet is the Order column (G) and the product column (A). The rest of the columns (B to F) are direct references only. That means, if you change any value in column B, C and E (since the rest of the columns are blanks or spacers only I believe), in the Order Sheets, these new values will be reflected.

So please take note of the following:

1. The Data Entry sheet will be DC Par, it is in this sheet that you add, edit or delete values

2. The report sheets (Order Sheet CATS and Order Sheet ALL) are meant for viewing and printing only, since the report area (the area with your product listings and their corresponding values) contains formulas that produce the data. So entering anything there can overwrite a formula that will break the reporting system.

3. I do not know what Par Level is, but I believe it is the entry in column B and C. You can change anything there without any issues. That is, from 18 Bottle you can change it to 5 Liters or however you want to change any entries in columns B and C, it will be alright.

4. VERY IMPORTANT. Since your data is in table form in DC Par, you need to ADD enteries PER ROW. That is, suppose you need to ADD a new product under Beers and Cider. All you need to do is use the INSERT ROW command. This is to ensure that the formulasin the Order Sheets will update automatically. And after INSERTING a row of data in a particular category in DC Par, you need to also ADD the same number of rows in the corresponding category in the Order Sheet CATS (since Order Sheet ALL has no problem updating, only CATS).

For instance, if you add 3 rows for additional 3 products under Beers & Cider in DC Par, you also need to ADD 3 rows in the Order Sheets CATS under the Beers & Cider category. After that, you just need to copy the formula DOWN to the added rows.

To add or insert rows, RIGHT-CLICK on the row-number where you want to INSERT the new row. Then click on Insert. In my screen shot below, I right clicked in 31 and when I click Insert, a blank row 31 will be inserted and the other data will move down.

http://filesxpress.com/d-07bfbbaf

Lastly, I mentioned that after you inserted new rows in DC Par and enter your additional data, you also need to INSERT the same number of rows in Order Sheet CATS under the correct category. And after that, you just need to COPY the formula DOWN to reflect the new changes. I will teach you how to copy the formula down.

Suppose you added 3 rows in Beers & Cider, in the Order Sheet CATS, click to select the FIRST entry, position the mouse pointer in the lower-right corner of the selected cell where you see a black square, the mouse pointer will turn into a black PLUS sign, click on that small square that is called Fill Handle, then drag down to the last cell including the newly added cells. That will copy the formulas down.

Screen shot here:
http://filesxpress.com/d-f14b076b

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need assistance in the future, you can request me any time by posting a new question starting with "For Jess M" so that I can assist you immediately.

Thank you!

Best regards,
Jess
Jess M. and 2 other Microsoft Specialists are ready to help you

Related Microsoft Questions