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 Office
Satisfied Customers: 6590
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office 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: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year 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 1 year 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 1 year 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 1 year ago.

http://filesxpress.com/d-b4693972

can you see the file?

Expert:  Jess M. replied 1 year 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 1 year 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 1 year 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 1 year ago.

Wow!

That is amazing, thank you.

Melissa

Expert:  Jess M. replied 1 year 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 1 year 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 1 year 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 1 year ago.

great, thank you.

be back in touch soon.

Expert:  Jess M. replied 1 year ago.
Thanks, ***** ***** your time.

Regards,
Jess
Expert:  Jess M. replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year 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., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6590
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
< Previous | Next >
  • http://ww2.justanswer.co.uk/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    23
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.co.uk/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.co.uk/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    241
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.co.uk/uploads/JA/JACUSTOMERf8udkdxk/2013-8-3_15150_323738101505074393259301621172992o.64x64.jpg KamilAnwar1's Avatar

    KamilAnwar1

    Office Specialist

    Satisfied Customers:

    122
    8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
  • http://ww2.justanswer.co.uk/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    111
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.co.uk/uploads/WI/Windowspcfix/2013-8-19_153826_ja12.64x64.jpg James K.'s Avatar

    James K.

    Consultant

    Satisfied Customers:

    110
    Technical Director of IT Company
  • http://ww2.justanswer.co.uk/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    56
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions