Results 1 to 8 of 8

Using INDIRECT formula to reference a dynamic range on another sheet

Threaded View

  1. #1
    Registered User
    Join Date
    09-26-2015
    Location
    Sherwood Park, Canada
    MS-Off Ver
    Home 2011, Work 2016
    Posts
    52

    Using INDIRECT formula to reference a dynamic range on another sheet

    I am trying to use the INDIRECT formula to reference a dynamic range on another sheet.

    I've attached my worksheet.

    Basically, The excel sheet is used to record different types of trips sold and I would like to determine how many of each trip type are going out the next day. I wrote the following formula to determine that:

    =SUMIFS(INDIRECT("A"&$H$2&":A"&$H$3),INDIRECT("C"&$H$2&":C"&$H$3),(TODAY()+1),INDIRECT("E"&$H$2&":E"&$H$3),"LHB-FD-RC-K-OT")

    This allows me to type in a lower range and an upper range (in cells H2 and H3) to search through a dynamic range of rows. The reason I need a dynamic range of rows is that sometimes trips are copied down to another day and I do not want it to double count these trips because the entries may appear twice.

    This works quite well and gives me all the information I want to know. However, it is on the same sheet as the data (Walk in Sales) I would like to move it to a separate sheet (Counter) to make to excel file cleaner. I would like to be able to enter the rows for the dynamic range into cells B1 and B2 on the "Counter" sheet but still have the formula look through the specified rows on the "Walk in Sales" sheet. I think the formula should look like this:

    =SUMIFS(INDIRECT("'Walk in Sales'!A"&'Counter'!$B$1&":'Walk in Sales'!A"&'Counter'!$B$2),INDIRECT("'Walk in Sales'!C"&'Counter'!$B$1&":'Walk in Sales'!C"&'Counter'!$B$2),(TODAY()+1),INDIRECT("'Walk in Sales'!E"&'Counter'!$B$1&":''Walk in Sales'!E"&'Counter'!$B$2),"LHB-FD-RC-K-OT")

    However, it does not seem to work. Does anyone have any ideas?

    Additionally, it would be convenient if I could like the last part of the formula ("LHB-FD-RC-K-OT") to a reference cell with that text so i would not have to modify the formula for each trip, but this also does not work. Any ideas for that as well?

    Thanks in advance.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] named range via indirect. How to reference a specific sheet
    By Decar in forum Excel General
    Replies: 2
    Last Post: 11-28-2013, 03:05 PM
  2. [SOLVED] Reference a dynamic range on another sheet and do something if it is blank
    By BeachRock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 01:22 PM
  3. VBA Dynamic Range - An Indirect Cell Reference
    By ExcelHelp2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2013, 11:08 AM
  4. [SOLVED] Dynamic Range Reference to Another Sheet
    By jasperhuang93 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-20-2012, 09:49 AM
  5. Replies: 1
    Last Post: 02-18-2012, 10:31 AM
  6. Indirect reference to a sheet in this formula
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-13-2007, 11:25 AM
  7. Help with an Indirect formula to reference sheet names
    By PCLIVE in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2005, 01:05 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