+ Reply to Thread
Results 1 to 11 of 11

Need to consolidate an order form.

  1. #1
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Need to consolidate an order form.

    I made a spreadsheet I use as an order form. The first sheet is the order form where I enter in quantities but instead of printing out the whole invoice which makes it difficult to pull the items from the shelf, I made a second sheet named "Print" that mirrors the "Invoice" sheet but only displays the cells that have quantities entered. The problem I would like to fix is on the "Print" sheet there many empty cells. Is there a way to fetch the data from the "Invoice" sheet but consolidate it so it only takes up as many cells as there is data for? If there are 20 items entered, then it would be a list with 20 rows, 1 for each item. Attached is the file so you can better see what i'm talking about. The third sheet titled "Objective' is the goal i'm trying to achieve. Thank you.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to consolidate an order form.

    try to not use merged cells then we can try

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Need to consolidate an order form.

    see attached
    result from all 4 tables
    done with PowerQuery

    Power Query for
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Re: Need to consolidate an order form.

    I'm going to have to play around with Power Query tomorrow. I'm a excel noob and it never ceases to amaze me what its capable of. Thank you!

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to consolidate an order form.

    You are welcome

    and don't use merged cells!

  6. #6
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Re: Need to consolidate an order form.

    Do merged cells increase the difficulty? Can you explain? Im sorry, I'm super new to this amazing program.

  7. #7
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Re: Need to consolidate an order form.

    I think Power Query is what I've been looking for to help me keep up with our inventory. Do you think this addon would be capable of pulling quantities from hundreds of excel files in a folder and adding them together in a single inventory spreadsheet?

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to consolidate an order form.

    There should be warning if you click on Merge and Center : Don't ask for help anymore !

    however you can use it but if you want to use something like excel table it will destroy your merged cells and you'll see a big mish-mash with your data

    PowerQuery is designed just for BigData and multi sheets or multi files but you need to remember about Excel (not PowerQuery) limitation: no more than 1 million rows loaded in single sheet

  9. #9
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Re: Need to consolidate an order form.

    im sorry, i was not aware of the unwritten rules of Excel and the forbidden use of cell merging. I will attempt to do better.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to consolidate an order form.

    no need to be sorry , it was a joke however this is the true about merged cells. you can use it but wise and not with excel tables. your invoice has ranges not a tables

    try example: use any simple range, use Ctrl+T to change range to table and try merge cells there. you will see what will happen

    Last edited by sandy666; 05-28-2018 at 09:12 PM.

  11. #11
    Registered User
    Join Date
    11-29-2017
    Location
    Delaware
    MS-Off Ver
    2013
    Posts
    13

    Re: Need to consolidate an order form.

    I did it, I deleted all the merged Cells!! I've also figured out how to use Power Query to track sales and even used Conditional Formatting to turn the items red when they run out of stock. I still haven't figured out how to turn the "Print" worksheet into a consolidated version without all the blank spaces. I'll attach the two files that but I don't know if they'll still work together after they've been uploaded and downloaded on another computer but give them a try. Delete the numbers in the "Cases Instock" column in the Inventory file (sheets "Food Inventory" and "Non-Food Inventory") and you'll see the items on "Invoice" turn red. I'm curious to hear from a pro how I did. A test SBI # is 101010. Copy the "Invoice" and name it Invoice 1, Invoice 2, Invoice 3 and put it into a "Invoice3" folder on your desktop so the query works. Without all the merged cells, see if you can consolidate the 2nd sheet so all the items are together. If you don't mind. Thanks for the help!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidate multiple workbooks data in an order from folder
    By sarajun_88 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2017, 03:45 PM
  2. How to consolidate values in cells in order and remove duplicate?
    By london7871 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-23-2016, 11:33 PM
  3. [SOLVED] Submit Order Button not working in my Excel Spreadsheet Order Form
    By KazzICC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2015, 09:29 PM
  4. [SOLVED] Order Form to Summarise Order on another sheet with a Submit Order Button
    By KazzICC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-01-2014, 03:25 AM
  5. Want to auto-populate an order form from a storeroom count form
    By bezam in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-17-2014, 09:02 PM
  6. [SOLVED] Consolidate grocery order by removing unused rows
    By Jamesera27 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-30-2014, 11:33 AM
  7. Aligning rows in order to consolidate
    By padrald0w in forum Excel General
    Replies: 3
    Last Post: 08-26-2011, 01:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1