# Returning single cell value looking at range across multiple sheets

1. ## Returning single cell value looking at range across multiple sheets

Ok, I have a monthly spreadsheet with daily information on individual sheets. I have included a summary page that gives totals, averages, and MIN/MAX values for amounts, volumes, and times. Each sheet has the same information for three entities. Therefore, values are by individual entity and combined. Example Maximum function for the combined entities would be: =MAX('11-01:11-30'!E8:E10,'11-01:11-30'!E16:E29,'11-01:11-30'!E35:E48). The problem I am having is that I want to report the date that corresponds to the sheet that contains the maximum/minimum values (the date is contained in M4 of each sheet). I've tried using SUMIF (=SUMIF('11-01:11-30'!E8:E10,'11-01:11-30'!E16:E29,'11-01:11-30'!E35:E48,"=MAX('11-01:11-30'!E8:E10,'11-01:11-30'!E16:E29,'11-01:11-30'!E35:E48),'11-01:11-30'!M4)), but that only works on a single sheet. I'm trying to avoid a ton of nested IF's. Any suggestions?

Thanks,
Kris

2. It might be helpful to include the reasoning for the request. Basicly, the daily sheets are reports of financial file transmissions for three banks. I'm given the date, each trasnsmissions \$ value, item count, transmission start time and end time. From that, I calculate the duration and throughput (items(k)/minute) of each transmission. On the summary page, I list the max and min duration and throughput for the month. I would like to include the date of each for research purposes. In other words, it's nice to know the longest transmission duration, but knowing the date it occured would also be helpful. I just want to be able to associate the date with the occurance on the summary page. Since I work for a bank (hello SOX404), I'm being somewhat intentionally vague.

Thanks,
Kris

3. Anyone? Perhaps, I'm posting in the wrong place? Any help appreciated.

Thanks,
Kris

As they say a picture is worth a 1000 words. Can you do a new post with a detailed example

VBA Noob

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1