Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query excel data from within the same workbook (Excel 2002)

    Hi All!

    I'm generally an Access/Word person, but right now I'm desperately trying to work in Excel. Feeling like I have to think backwards. And not being able to search doesn't help much ;-) Can someone point me in a good direction for doing some of the following things?

    I am querying a mainframe database and pulling down information from several tables by date into a worksheet. What I'd like to do is be able to have queries from separate sheets in the same book that pull the data for the year from the main worksheet, but I can't figure out how to query from within the same worksheet.

    Once I get the correct information into the correct worksheet (ie. 2002 data in a 2002 worksheet, 2001 in a 2001 worksheet, etc), then I need to transpose the data. In other words, I have a bunch of ratios running across the top of the page, and a bunch of companies running down the left side. I know I can click the paste button to transpose this information to have the ratios running down the left and the companies running across the top (for comparative analysis), but is there an automated way to do this?

    The problem is that I'd like to be able to do this on a regular basis, but I'm not quite sure how.

    Any pointers, references, or even a suggestion for a good book (although the ones I have here don't seem to help) would be greatly appreciated.

    THANKS! :-)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Those "Excel" queries, do they involve just single tables?

    If so, have a look at the following things in Excel:

    - Data, filter, autofilter (or advanced filter)
    - The Database worksheet functions (DSUM, DCOUNT, DAVERAGE, etcetera)
    - Pivot tables (can easily create year-overviews with the year as a page-dropdown)

    If the queries are multi-table ones, I suggest doing them outside of Excel, e.g. creating them in the database application itself or to use msQuery to create them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    I do something similar. I have a macro import all the data to one worksheet and define a "Range Name" to include the whole pile of data. Then on other pages I have pivot tables that summarize the data, referencing the data by it's range name. That way the pivots can be updated accurately even if the number of rows in the original data import changes.

    Start playing around with the pivot tables and I'm sure you'll figure them out.

    Autofilter is also a good suggestion if you just want to show the original data rows but limited to different criteria. Pivots do COUNT and SUM queries -- good for summarizing a big pile of data.

    I also use VLOOKUP formulas sometimes to "look up" matching data on other sheets. This is a one-to-one kind of matching.

    If you already know Access, I doubt any book will help you out as much as just using online help and doing a few little experiments with these features. I'll bet you'll have a solution in no time!
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Hi,

    Somehow I could not get filter to work. I want to filter my results and put only a certain date into a new worksheet, but can't seem to accomplish that.

    I don't think a pivot table will work either, I've been trying to use that with no luck. I don't really want to pivot my results. What I want to do is transpose them, so that the companies go across the top and all the ratios (which are field names in the source data) go down the left. I'm able to do this manually, after I've manually cut & pasted the data into a separate worksheet.

    I'm also not trying to do dsums or anything like that. I just want to present the existing data in a different way.

    Here's what I see in my mind: First sheet, all data, one big table, with ratios for selected companies spanning several years. The ratios go across the top (because they are field names in my source data/mainframe database) and companies go down the left side (because they are records in my source data). Go to next sheet (labelled 2002): I can query the first sheet to just pull that information for year end 2002, then transpose it so that you can see the ratios going down the left side and the companies going across the top, so that you can easily compare a ratio between companies. Next sheet will be 2001, then 2000, etc.

    In other words, my mainframe data looks like this:

    <table border=1><td>Name</td><td>Report Date</td><td>Total Assets</td><td>Return on Assets</td><td>Net Income</td><td>Company A</td><td>12/31/2002</td><td>500</td><td>0.9</td><td>45</td><td>Company A</td><td>12/31/2001</td><td>400</td><td>1.0</td><td>40</td><td>Company B</td><td>12/31/2002</td><td>600</td><td>0.8</td><td>40</td><td>Company B</td><td>12/31/2001</td><td>500</td><td>0.9</td><td>45</td></table>

    What I want is:

    For 2002:
    <table border=1><td></td><td>Company A</td><td>Company B</td><td>Total Assets</td><td>500</td><td>600</td><td>Return on Assets</td><td>0.9</td><td>0.8</td><td>Net Income</td><td>45</td><td>40</td></table>

    (Please don't check my figures, I just plugged them for an example)

    Does this kind of make sense? Is this something you would normally do in Excel?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Hi Dory,

    Pivot tables would be a nice way to automate this, but I'm not quite sure that that's what I want to do. They seem to aggregate the data, which is not want I want to do. I just want to display the data in a different way.

    My big limitation in Access is that I want to use the field names as data. I posted an example above. I figured since Excel doesn't really have field names--everything is data-- then I wouldn't have this limitation?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Here is an example of getting your results with a Pivot Table

    Steve

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    I had the same Pivot Table in mind. It doesn't HURT to aggregrate the data, does it? It's already aggregated. That handy "Page field" lets you select the year to display very nicely.

    I'm not really sure what's going on there with your data and why transposing it would make it easier to read, why the field names are "part of the data", etc....
    Pivots, Autofilter, PasteSpecial...Transpose, =VLOOKUP() or =HLOOKUP() or some combination of the above is all I got for ya. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    If comparing the data between companies is the goal, maybe a chart based on the pivot table would really do the trick. You can use data labels or a data table to show actual numbers.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

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

    Re: Query excel data from within the same workbook (Excel 2002)

    Steve IS a genius with Excel!

    There is no row field in his pivot table. There are three data fields, each set to Sum. But since there is only one value for a given date, the sum *is* the single value.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    To see how the table is set up, right click on it, select WIZARD and look at the set up. The one problem is you can NOT name the "fields" to match your the names that are given.

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Thank you, Hans. That means a lot coming from you.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    And how the heck do YOU have time for it all, Hans? Access, Excel, and wherever else you travel in the course of a day ;-)

    Thanks again,

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Okay, so then how did you do it....

    In the example, the field Return on Assets was added to the data area. In the left column, it says ROA. When I do it, all I get is Sum of Return on Assets.

    If I type in the name of the field and then hide the "Sum of" section, then my spreadsheet isn't really automated, because if a field is added, then all the field names will be off a bit....

  13. #13
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Look at the spreadsheet that was posted above.

    Right click on the Pivot Table...choose Wizard...click Layout button.
    See the "Page" field off on its own in the top lefthand side? That's where you drag the date.

    Name should be in the Column area.

    Do not put any fields in the Row area. Leave that area empty.

    Three fields all go into the Data area. You can double-click on them to change the text that will be used to label them in the resulting chart.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Thanks, Dory,

    The part I was missing is that you can "alias" the stuff in the middle by double clicking on it!

    This is all so cool.... <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    Still, I'm going back to Access <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

  15. #15
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query excel data from within the same workbook (Excel 2002)

    Cecilia,

    It appears that you are asking for 2 tasks:
    A. Select a year (maybe optional)
    B. Transpose the row/column into a new table.

    I have used your table and data from your earlier posting and pasted them to the attached workbook in a Range ("SourceData"). This is where you would download your data from your query.

    Optionally you may select a year.

    Then click on the Transpose macro button to transpose your selected data.

    Hope this works.
    Paul

Page 1 of 2 12 LastLast

Posting Permissions

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