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.

You don't need to specify the sheetname on BOTH sides of the : in a range reference.
Sheet1!A1:A10 is all that is required.

Now Sheet1!A1:Sheet1!A10 IS valid when written exactly as that in a regular formula.
But INDIRECT doesn't like it.

So this
INDIRECT("'Walk in Sales'!A"&'Counter'!$B$1&":'Walk in Sales'!A"&'Counter'!$B$2)
should just be
INDIRECT("'Walk in Sales'!A"&'Counter'!$B$1&":A"&'Counter'!$B$2)

Try this

Please Login or Register  to view this content.

Works great. Thanks!

Welcome.Pl mark the thread solved.

You're welcome.

Good afternoon,

I was reading this thread hoping to help me in my doubt, and it seems it does, but still my formula does not work.

The problem is the same, and i'm writing:
=SUMIFS(INDIRECT("'1. Movimentos pilhas 2016'!H"&M9&":H"&O9);'1. Movimentos pilhas 2016'!L:L;"P")

being '1. Movimentos pilhas 2016'! the other sheet where I want to refer to;
M9 and O9 are cells in the working sheet (same workbook) where number values are calculated with a formula.

What am i doing wrong?

Thank you,

SUMIFS requires all the ranges to be of similar dimensions.
So you would have to specify the same row numbers on L:L as you do to H:H.

Also, You don't need indirect for this (indirect is only needed if it's the sheet name that needs to vary based on contents of a cell).

If I Understand right, you have the Non-Indirect formula like

=SUMIFS('1. Movimentos pilhas 2016'!H??:H??,'1. Movimentos pilhas 2016'!L??:L??,"P")
And the ?? are the start and end rows you need based on M9 and O9.

Try
=SUMIFS(INDEX('1. Movimentos pilhas 2016'!H:H,M9):INDEX(H:H,O9),INDEX('1. Movimentos pilhas 2016'!L:L,M9):INDEX(L:L,O9),"P")



But even further, do you really need to drill down the row #s? Why not just use the entire columns ?
=SUMIFS('1. Movimentos pilhas 2016'!H:H,'1. Movimentos pilhas 2016'!L:L,"P")