nested IF statements with INDEX/MATCH

[PNY80]

I wrote a formula that:
1) checks for a certain criteria, and if it's true, then
2) references a specific worksheet to list all unique values (names) and their respective data points

{=INDEX(month!$A$2:$D$402, MATCH(0, COUNTIF($C$6:C6, month!$A$2:$A$402)+(month!$D$2:$D$402<>$D$3), 0), COLUMN(month!A1))}

I need to expand the above formula so that the criteria that it checks can be done across multiple sheets. The only way that I can think of is using nested IF statements, but I was wondering if there is a more "elegant" way to accomplish this.

please advise!

 

[PaddyD]

"...so that the criteria that it checks can be done across multiple sheets..."

I suspect you have a 'data set up' problem more than a formula problem here - doing conditional stuff across multiple sheets with formulas gets complicated quickly.

Can to describe your data etc in more detail and give us a worked example of the problem you're trying to solve?

 

[PNY80]

Hi Paddy D - thanks for your reply!

In my workbook, there’s a worksheet that pulls data points based on specific criteria that the user chooses and summarizes in a report.

The first column of the report retrieves the unique names in a data list that resides in another worksheet that has multiple rows of records for each name based on the date the data was entered.

The data points are summed based on whether the person viewing the report wants to view by:
-month
-quarter
-end of year
-year to date

So far, I have the report set up so that a user can view based on a specific month they choose, but since I need to have the capability to allow the user to view by a specific quarter, end of year and year to date, I’m thinking that the only way to accomplish this is to use nested IF statements with INDEX/MATCH.

Is there a more elegant way to accomplish this?

 

[PaddyD]

HI. I've been off doing other things. Is this still a problem or have you got it sorted ?

If it's still 'live', then post back - still need a bit more info - data structure of the sheets with the data, why you have more than 1 data sheet, naming conventions etc. Aworked example would help too.