1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Pulling data from multiple sheets within a workbook by using a wildcard

Discussion in 'Business Applications' started by cmnicholls76, Feb 24, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. cmnicholls76

    cmnicholls76 Thread Starter

    Joined:
    Aug 27, 2007
    Messages:
    5
    Hi all,

    I'm using excel 2003 and trying to find out if there is an easy way of using data available in multiple sheets without putting in each sheet's name individually.

    For example, each 'customer' has 2 sheets within a workbook, identified by a 'customer' number.
    eg. sheets for customers 123, 345, 789:
    D123 / X123 / D345 / X345 / D789 / X789

    I'm actually using the function DCOUNT at the moment, with each 'customer' having their own workbook. When I use the DCOUNT function, EACH CUSTOMER'S workbook needs to be open, which is a pain. (and it craps itself if they're not open)

    =DCOUNT([123.xls]Data!$B:$F,"Result",C$1:E2)+DCOUNT([345.xls]Data!$B:$F,"Result",C$1:E2)+DCOUNT([789.xls]Data!$B:$F,"Result",C$1:E2)

    by using the two sheets for customers (D and X) is there a way to search for D* (like a wildcard which would only use sheets D123, D345 and D789, but not X123, X345 and X789) within the DCOUNT function? Something like:

    =DCOUNT(D*!$B:$F,"Result",C$1:E2)

    Any help would be greatly appreciated!!

    Chris
     
  2. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, why different Workbooks for different Customers, why not just one Workbook.
    Also why Excel for an obvious Access Application?
    I am not so good with the Functions, you need Firefytr, Bomb etc for that, but I could do it in Visual Basic.
     
  3. cmnicholls76

    cmnicholls76 Thread Starter

    Joined:
    Aug 27, 2007
    Messages:
    5
    Okay I'll come clean.. I'm a croupier and they're not really customers, more roulette dealers. Its a tracking system at the local casino to predict how croupiers spin the ball (clockwise/anti-) on left and right-handed tables. :eek:

    1. I'm trying to compile the data into one workbook, but for each dealer I need to be able to log thousands spins over time (65k records with excel).

    2. I thought it would be better to use a database program, but I have zero experience with them and some with excel, hence the persistance with excel (and its working nicely, except for the single workbook problem)...

    3. Any other help would be much appreciated!

    Chris
     
  4. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you already have the data in Excel I could import it in to an Access database for you, but the next question is what do you do with the data?
    When it comes to summaries and calculations of a lot of data then access Queries take some beating.
    It avoids the kind of problem that you are having now.
    I can do what you want using a VB Macro, but I would need a few details like are the Workbooks in one folder, how many workbooks, what form do the Workbook names take etc.
     
  5. cmnicholls76

    cmnicholls76 Thread Starter

    Joined:
    Aug 27, 2007
    Messages:
    5
    At the moment I have a workbook for each dealer and under the Data sheet i have the columns: Date, TableID, Result (number spun), ResPos (slot number on the wheel the result relates to), R/L (right/left-handed table), SpinDir (clockwise/anti-) and Diff (number of slots in a clockwise direction around the wheel between the current and last spins).

    On a Statistics sheet I calculate the frequency of each Diff (variance in slots of between 0 [same number] and 36 [36th slot clockwise, or number to the left of the last number]) for left hand table (clockwise, left hand (anti-), right (clock) and right (anti-). I then graph the variance frequency.

    What I have done using multiple books is graph total spins for a table for all dealers, which over time should show inconsistencies with the wheel itself.

    I don't know if access can crunch the numbers like excel can (since I have no access exp.) but I'm willing to sgo down that path if will be less rocky...

    Cheers!

    Chris
     
  6. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, can you post an example of the Excel worksheet so that I can see it's layout?
    I can then create an Access database to receive the data and we can give it a try.
    If you are only collating data per Dealer why do you need the "Cross Workbook" requirement, or is that for something new?
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/686553

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice