Limit a formula to only look in a certain range

I'm working with a formula that looks like this:

=TRIM(INDEX('#001'!$A$1:$S$10000,MATCH(LARGE(IF('#001'!$K$1:$K$10000<>"",IF('#001'!$B$1:$B$10000=" MENS ",IF(ISNUMBER('#001'!$L$1:$L$10000),'#001'!$L$1:$L$10000-ROW('#001'!$L$1:$L$10000)/10^5))),1),IF('#001'!$K$1:$K$10000<>"",IF('#001'!$B$1:$B$10000=" MENS ",IF(ISNUMBER('#001'!$L$1:$L$10000),'#001'!$L$1:$L$10000-ROW('#001'!$L$1:$L$10000)/10^5))),0),8))

The #001 in the formula refers to a store #. Currently I have 20 stores and have created 20 different sheets. Each sheet contains that stores data.

What I'd like to do is just have all the data on one sheet. I'm trying to figure out if there's a way to modify the formula to look in only a selected range of data. For example, a sheet contains data for 20 stores. At the start of each new store it displays a value of "Store #XXX" in a row located in column A. At the end of the data for each store it display a value of "Total for Store #XXX" in a row located in column A. The data goes from Column A to F and can span a couple hundred rows.

Is there a way to modify the formula at the top to have it only look at the data for store #001, #002, etc...

Thanks in advance for any help

Hi

You could create a series of defined names. If you have Store #001 in A3 and Total for Store #001 in A7, then go
Insert, Name, Define, Name: St001, Refers to: =OFFSET(Sheet1!$A$1,MATCH("*#001",Sheet1!$A:$A,0)-1,0,MATCH("Total*001",Sheet1!$A:$A,0)-MATCH("*#001",Sheet1!$A:$A,0)+1,6), OK.

This will give you the range A3:F7 that relates to store #001. You can then use this as a base to build the other defined names. They are dynamic and will adjust based on the positions of the strings.

You can then use the relevant defined name in your formulas.

HTH

rylo

The only problem is that the range is never consistent. One time store #001 could range from A5 to F20 and the next it could range from A10 to F56.

Hi

The formulas I gave you are dynamic. That means that it doesn't matter where the ranges start and finish, the defined name will move accordingly.

Test the formulas out and see how the update regardless of the number of lines in the range, and where they start and finish.


rylo

I'm not clear on where I should insert the offset function into my formula above. Thanks again for your help.

Also, if you have time I want to try and add the offset function to another, simpler formula I have. It looks like this:

=SUMIF('Data Dump - #001'!$B:$B," CLASS : 011 - M JEANS ",'Data Dump - #001'!$C:$C)

Data Dump #001 refers to store #001's data sheet. Just like with my problem above, I want to be able to modify the formula so that it only looks up data in the sheet that is between two criteria I set. Thanks

Hi

Go back to my original post which gives instructions on where to put the formulas to make the defined names.

If you still can't make it happen, then post up an example workbook that we can work on.


rylo

Ok, I've attached a sample workbook that includes:

1. The cover page where the formula's are located
2. A sheet titled "All" with data from two stores
3. Two other sheets titled 001 and 002 which contain the same data from All but broken out into separate sheets for each store.

Hi

In your defined name formula, you have an extra space in the name "TOTAL FOR STORE : 001". If you remove that, the formula will give you the defined range in the sheet All.

From there, you can either create a series of defined names that relate to single columns. If you change the main one to refer to column a, then you can do an offset from the first by 1 column which will give you the column B range. Same for columns K and L and another for the A:S range. You can then use these names in your formulas.

rylo

Ok, I fixed the extra space but I'm still a little lost about how to proceed. I don't understand what your trying to show me what to do next. I truly appreciate your help on this.

Hi

Have a look at the attached. I've put in the first set of defined names (all on sheet All). StrALL gives the entire range. It does assume that the last cell in column A will be a unique value. I've then put in the relevant items for the 001 store. In L3:L7 I've reworked the formulas for D3:D7 to reuse the dynamic names.

See if this starts to clear things up for you.

rylo

First let me say thanks for working with me on this. I understand now the principles you're applying with the Offset function. I tried applying it to my own worksheet but I think there's something wrong with the formula.

I first tried applying it to my original excel sheet and while it was giving me back results, it was not ordering them based on the largest value in column L. I then went back to your sheet and into the All sheet. I switched the store names so that Store #002 was on top and Store #001 was below. When I did that the formula was displaying results from the wrong store # and not in order from largest to smallest.

I've gone over it a bunch of times seeing if I was doing something wrong but I can't see anything.

That worked fantastically. Thanks for spending so much of your time on this with me. It's going to save me hours each week!