Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Generate report from lookup table (Excel XP)

    I have a large lookup tables--one on each of twelve different worksheets in a workbook. Each of these worksheets represents financial information for a particular month. I have 30 different columns that represent salesmen [women] and 500 rows that represent products. I should mention that Column A and B contain a part number and item description respectively (i.e., Column C is the first salesperson column---the next 29 columns after column C are sales people). This workbook is close to 100Mg...so I want to generate the reports from a separate workbook as I am running into memory limitations on my PC.

    A salesperson doesn't sell all items. So I have many, many zeros--reflecting no sales of that item--in each salesman column. What I want to do is write a macro in the new separate workbook that creates a report by salesperson and lists only those items sold for the month. So if the sales person in column C sold only 10 out of the possible 500 items, the report would indicate the sale person and then have three columns of information: part number (column a), description (column [img]/forums/images/smilies/cool.gif[/img], sales $ (column c)---only ten consective rows (no blank rows) since only 10 products were sold. Then a total for column C. I would then need this macro to do the same action on the next 29 columns in the "Jan" worksheet in the original 100mg workbook . Then each month the macro would access the correct worksheet (Feb., Mar, April, May, etc.) in the original workbook to generate the reports.

    I don't need a worksheet for each salesperson report...so the next salesperson report (for example, column d) can be a couple of rows after the previous sales person (for example, column C). I would need the a separate worksheet for each month to correspond with the twelve worksheets (months) in the original workbook.

    I have played with some code and my "new" report has blank lines where there are no sales in the original workbook. I can not discern how to "read" the value in the original workbook and if its zero to move to the next row while still keeping the same row location in the "new" report waiting to write the next product that does have a value. How do you "read" from one worksheet, and if the criteria test "fails" move down to the next row while keeping the report row in the worksheet the same waiting to write the next value when the criteria test "passes"? I have also had to copy the "jan" worksheet from the 100Mg workbook to a new workbook in order to start this report process as the original workbook is so large and eats up all my memory. I have seen in the lounge several posts that indicate that I can get an "add-on" that will permit me to pull information from a closed workbook. Is this something I need for this project or is there something else I am missing?

    I hope I have explained this sufficiently....thanks for your patience and reading through it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Generate report from lookup table (Excel XP)

    Have you considered moving the data to a database application such as Access? That would seem to be much more suitable for what you're doing/trying to do. You'd be able to arrange the data differently and to use queries to extract information from the data.

    Note: A worksheet formula can refer to a cell in a closed workbook, but VBA code needs to have a workbook open in order to read from it.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Generate report from lookup table (Excel XP)

    Hans,
    I know even less about Access than Excel...Wow, this is a scary thought!! But I guess your commentary confirms that what I am trying to accomplish is not practical in EXCEL. Maybe this is an incentive for me to poke around more in Access. I know how to import a table, but my query writing, well, leaves much room for improvement. THANKS. Have a nice weekend....take care. Jim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Generate report from lookup table (Excel XP)

    It is possible to write code to do what you want in Excel, but I fear it will be slow.

    BTW, if the file size is a problem, wouldn't it be better to store each month's data in a separate workbook?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generate report from lookup table (Excel XP)

    I am sure that this could be done with VBA (however, probably pretty complex), but it is impossible to say exactly what it would take without seeing a copy of the workbook. It is just too difficult to follow from a description. What you want is probably going to be more complex than anyone would be willing to tackle for free here on the Lounge.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Generate report from lookup table (Excel XP)

    Hans,
    Thanks for the reply. I inherited the initial sale person workbook nearly 3 years ago and it has grown over the years from 7 individuals to now 30 sales associates. The monthly worksheets are linked to a "master worksheet" that adds any new parts / desriptions to the monthly worksheets. I would need to re-write these links...and so far, I have resisted. The file is now very near 100mg so I either need more memory in the PC or will have to break the monthly worksheets into separate, stand-alone, workbooks. Again, thanks for your advice. Take care.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Generate report from lookup table (Excel XP)

    Legare,
    Thanks for the insight...appreciated. Take care.

Posting Permissions

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