Formula Involving Multiple Worksheets

[dinbo123]

I would like my formulas on a page called "Reports" to reference data from the same cell range on multiple other worksheets within the same workbook. The other worksheets are in order labeled Women, Men, Toddler, Infant. Below are two different formulas I am using:

=SORT(UNIQUE(FILTER(Women!A2:A499,Women!A2:A499<>0,"")))

=FILTER(Women!A2:D499,(Women!A2:A499>=A3)*(Women!A2:A499<=A5)*(ISNUMBER(SEARCH(A7,Women!C2:C499))),"No records found")

It works when only referencing one sheet - i.e. Women. But when I change the formulas to include a range like below, it gives me a VALUE error.

=SORT(UNIQUE(FILTER(Women:Infant!A2:A499,Women:Infant!A2:A499<>0,"")))

I want to create a "report" by pulling data from the different sheets, and then filtering them based on date or term. As I've said, the codes work when referencing one sheet, but not multiple.

I am trying to avoid using queries or MACROS

I'm not sure if this is even possible, but I know it works for the SUM function, etc. as I have done it in the past.

Any help is appreciated!

[#2]

@dinbo123

Copy the data form the different sheets to a single sheet. You can then use formulas like the ones you now have.

[#3]

@Hans VogelaarI appreciate the suggestion, but I need to keep the data separate and add to it as time goes on. The formula allows the data to be interactive. It's pulling the data from a separate sheet right now. I want it to do that across multiple sheets to combine into one table.

The information I'm providing in this post is for a much smaller, simpler version of what I eventually want to apply it to. The end product needs to remain separate for record purposes. The formula I'm looking to create would allow me to pull data from all sheets dependent on the criteria I enter.

I have used it for the sum function in the example formula below. For some reason, the same range method is not working for the functions I'm using this time.

=SUM(Women:Infant!C2)