+ Reply to Thread
Results 1 to 11 of 11

Expanding a range across multiple sheets

  1. #1
    Registered User
    Join Date
    11-12-2013
    Location
    London
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    27

    Expanding a range across multiple sheets

    Hola!

    I'm in need of a little pointer/advice.

    The following formula extracts invoice data for use as part of a 'statement of account' workbook, with separate sheets for each month.

    =IF(ISERROR(INDEX('[Sales 2013-2014.xlsx]April 2013'!$B$3:$K$50,SMALL(IF('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50=$A$8,ROW('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50)),ROW(2:2)),5)),"",INDEX('[Sales 2013-2014.xlsx]April 2013'!$B$1:$K$50,SMALL(IF('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50=$A$8,ROW('[Sales 2013-2014.xlsx]April 2013'!$C$1:$C$50)),ROW(2:2)),5))

    It's working beautifully for 1 month (April 2013), but I would like to modify the above so that it uses the same range for all 12; 'April 2013', 'May 2013', 'June 2013', 'July 2013' etc etc.

    Any feedback gratefully received.
    Last edited by Kym-B; 01-13-2014 at 02:40 PM.

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    Can you post a workbook with representative data illustrating the problem? The formula is only showing a small "picture" of what you have and what needs to be done.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    11-12-2013
    Location
    London
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    27

    Re: Expanding a range across multiple sheets

    Statement of account https://www.dropbox.com/s/kdz4pk6s70...2013-2014.xlsx

    Source data https://www.dropbox.com/s/kdz4pk6s70...2013-2014.xlsx

    On the "Statement of Account" I select a client by clicking their name (first line of the address section, with the idea that this will query the sales sheet (source data), and list all invoices for the year for that client. The formula works for one month, in this case April 2013, but I need it to query all months (April-March), and I'm a bit stumped.

    Thank you for your wisdom!

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    Both workbooks that you have uploaded are Sales 2013-2014. There isn't a Statement of Account that I can find.

  5. #5
    Registered User
    Join Date
    11-12-2013
    Location
    London
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    27

    Re: Expanding a range across multiple sheets

    Oops!

    What an idiot, I pasted the same URL twice. Here's the Statement of Account file:

    https://www.dropbox.com/s/5lhydmkswo...%20design.xlsx
    Last edited by Kym-B; 01-14-2014 at 04:43 AM.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    This is turning into a nightmare unless I can find a way to address both the name and the workbook tab within a single formula which I have not been able to do without creating a situation that cannot be maintained by you.

    I'm researching the addressing of external references but so far have only found references to single variables and not two like you require. The solution my be in the use of VBA or a different arrangement of the sales data to facilitate the retrieval of data.

    Is there a practical reason why the data has 1 worksheet per month? This seems to be very inefficient on a number of fronts.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    Please delete................duplicated post
    Last edited by newdoverman; 01-14-2014 at 10:49 AM.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    I could only get this to work as a single workbook. The links to the workbook via cell reference just didn't get along with the cell reference for the worksheet.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-12-2013
    Location
    London
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    27

    Re: Expanding a range across multiple sheets

    Sorry NDM, I've been out of the office for over a day and am just catching up. I'm on my home PC at the moment and don't have Excel on it, but I'll have a play with that when I'm back tomorrow.

    I do want you to know though that I really appreciate you having taken the time out to help me as you've done.

    Just to quickly answer your earlier question, unfortunately the Sales sheet is kind of set in stone (I would rather it was all in one sheet, and makes more sense to me) unfortunately the "powers that be" insist that is the way it must be (I suspect it's purely because that's the case as it way it was originally set up rather than it is the best solution).

    I had a notion of making an intermediary worksheet that would harvest the Sales sheet data onto one page and that to be used for the SOA.

    Anyway, thanks ever so much for what you did and I'll report my findings tomorrow!
    Last edited by Kym-B; 01-15-2014 at 01:56 PM.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    If that is the case, it is sad because you can get all the reports that each monthly sheet has by filtering a single data set and have far more control. I think some "education" is in order.

    I can't get the double cell reference to work in addressing worksheets in an external data source. I can't even find any references to doing so. I'm afraid that I am at the end of what I can accomplish unless I get hit by "lightening" or something. Perhaps someone else has an insight into how to accomplish this.

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Expanding a range across multiple sheets

    I think that this is what you are intending. Seeing that you have tabs for each division, there is no longer a need for the Data Validation on each tab. I eliminated that DV in B2 of each worksheet. As data is added to the download worksheet, each tab is automatically updated.
    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. For each cell in range loop does not recognize expanding range
    By jhren in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2013, 07:49 AM
  2. Expanding/Collapsing grouped Tabs/Sheets
    By nskrbin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2013, 04:36 PM
  3. Adding to spread sheets but my tables are not expanding their range
    By emilyloz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2013, 10:02 AM
  4. Using a Sheet Tab as a Button for Expanding/Collapsing Sheets.
    By heifai in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2013, 12:40 AM
  5. [SOLVED] Copy range from multiple sheets, into a master sheet and moving over by variable col range
    By g1eagle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2012, 01:36 PM

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