Show certain detailed data from other sheet

[eve_star1]

HI,
I'm seeking help on this pulling data formula.

I want to try two type of method to pull the data from other sheet where the data will be updated monthly.

I have 3 worksheet which named as following:

"Data" - This is the worksheet where will keep on updating data by monthly

"summary by state" - this is where the data will be shown base on the state selection

"summary by all state" - The data will be automatically pull from "Data"

If you take a look at the attached file, you may get what I'm try to explain.

Thanks for help.

[Pete_UK]

Take a look at the attached file - it does what you asked for. As you add more data, you must ensure that the formula in column K of the Data sheet is copied down beyond the amount of data that you have (as shown by the hyphens). All other aspects are automatic, but you must also ensure that the formulae on the other sheets are copied down beyond the amount of data that you expect.

Hope this helps.

Pete

[eve_star1]

Take a look at the attached file - it does what you asked for. As you add more data, you must ensure that the formula in column K of the Data sheet is copied down beyond the amount of data that you have (as shown by the hyphens). All other aspects are automatic, but you must also ensure that the formulae on the other sheets are copied down beyond the amount of data that you expect.

Hope this helps.

Pete

Thanks Pete

It is amazing. It also automatically sorted by follow the state.
Currently we only apply 1 selection - "State".
Is there possible we have two selection - "State" & "Channel" ?
But can we make it sorted by Customer Name & State also?
If possible, the "State" selection beside select the state name, it also can allow us to select "ALL State" which is show all the data.

Thank you

[eve_star1]

Take a look at the attached file - it does what you asked for. As you add more data, you must ensure that the formula in column K of the Data sheet is copied down beyond the amount of data that you have (as shown by the hyphens). All other aspects are automatic, but you must also ensure that the formulae on the other sheets are copied down beyond the amount of data that you expect.

Hope this helps.

Pete

Thanks Pete

It is amazing. It also automatically sorted by follow the state.
Currently we only apply 1 selection - "State".
Is there possible we have two selection - "State" & "Channel" ?
But can we make it sorted by Customer Name & State also?
If possible, the "State" selection beside select the state name, it also can allow us to select "ALL State" which is show all the data.

Thank you

[eve_star1]

i still studying the formula.

=IF(ROWS($L$1:$L11)>MAX(Data!Q:Q),"",MATCH(MATCH(ROWS($L$1:$L11)-1,Data!Q$2:Q$8)&"_"&ROWS($L$1:$L11)-INDEX(Data!Q$2:Q$8,MATCH(ROWS($L$1:$L11)-1,Data!Q$2:Q$8)),Data!K:K,0))

Could you explain how to read this formula??

[Pete_UK]

It's not always easy to change a working solution to suit new requirements. It would take quite some time to do so in this case, and I don't have that time free at the moment. I have changed the drop-down to allow you to select All States, though I'm not sure why you want to do this as you have the same thing in the other sheet.

Hope this helps.

Pete

[popipipo]

A pivot table will also work.

[eve_star1]

It's not always easy to change a working solution to suit new requirements. It would take quite some time to do so in this case, and I don't have that time free at the moment. I have changed the drop-down to allow you to select All States, though I'm not sure why you want to do this as you have the same thing in the other sheet.

Hope this helps.

Pete

Thanks again Pete.
I try to understand the formula but fail. Thats why i wonder is it if make in same sheet with create "ALL State" option, would it be more easier.

=IF(ROWS($L$1:$L11)>MAX(Data!Q:Q),"",MATCH(MATCH(ROWS($L$1:$L11)-1,Data!Q$2:Q$8)&"_"&ROWS($L$1:$L11)-INDEX(Data!Q$2:Q$8,MATCH(ROWS($L$1:$L11)-1,Data!Q$2:Q$8)),Data!K:K,0))

But if you could explain how to understand the formula, it would help me a lot cause yours formula really amazing.

Thank you

[eve_star1]

A pivot table will also work.

Thanks popipipo

We have thought of that before but we might need to create link so if use formula is much easier.