Fornula to use to retrieve data given specified month and department.

**MikeAW2010** · 08-18-2012, 04:04 PM

CROSSPOST: http://www.mrexcel.com/forum/excel-q...ml#post3243809

I have data in table that I need to find both the Highest and Lowest value from a Particular date range.
I am not allowed to change the format of the data table as its owned by a company and used by several more people than just me.

Here's what I need. I need to beable to type in "July" and Excel pulls up the lowest value in the department I choose in one cell and highest value of the SAME DEPARTMENT in the next cell from the entire month of July so it would need to search all of "July 1/10, July 11/20, July 21/31" just by typing in July. There are over 20 departments but I only need information from 4 departments. Each department will be in a different cell. Also the departments will be apart of the formula, they will not be chosen based on their highest or lowest values, they are set pre-determined departments....

so it would display like this...

..................Lowest Value....|...Highest Value..|
---------------------------------------------|
Department 1......................|......................|
Department 2......................|......................|
Department 3......................|......................|
Department 4......................|......................|

Here is the layout...
Data.jpg

Please advise as to how this can be done?

**dogberry** · 08-18-2012, 04:16 PM

Hi

You can use the filter for that it does not alter the format or layout of the data, it just ignores the data you dont want and displays what you want copy and paste the results, remove your filter and everything is as it was.

Chris

**MikeAW2010** · 08-18-2012, 04:55 PM

Thanks for the recommendation but the filter tool wont be enough as I need it to be reported on an altogether different worksheet independent of the table and other data will be automatically drawing this data in and the filter will cause errors with that as it needs to be available while the worksheet is closed as well and I can't make changes to or save this table. Also the data on these tables get updated frequently so even if I copied and pasted it would very quickly become outdated.

The process MUST be 100% automated.

**Max, Singapore** · 08-18-2012, 06:08 PM

Assume your source data is in Sheet1,
running in A2:C2 down, eg:

1-Jul-12 Dept1 90
5-Jul-12 Dept1 77
2-Jul-12 Dept1 90
1-Jul-12 Dept2 0
4-Jul-12 Dept2 89
2-Jul-12 Dept2 3
16-Jul-12 Dept2 90
etc

In another sheet, say in Sheet2,
Assume you have the startdates and enddates
for the desired period specified in B1:B2, eg
1-Jul-12
15-Jul-12

and you have the list of required Depts running in A4 down, eg
Dept1
Dept2
etc

To extract the Min values (which are greater than zero)
Put this in B4, array-enter (press CTRL+SHIFT+ENTER to confirm):
=MIN(IF((Sheet1!$A$2:$A$1000>=$B$1)*(Sheet1!$A$2:$A$1000<=$B$2)*(Sheet1!$B$2:$B$1000=$A4)*(Sheet1!$C$2:$C$1000>0),Sheet1!$C$2:$C$1000))
Copy down

To extract the Max values
Put this in C4, array-enter (press CTRL+SHIFT+ENTER to confirm):
=MAX(IF((Sheet1!$A$2:$A$1000>=$B$1)*(Sheet1!$A$2:$A$1000<=$B$2)*(Sheet1!$B$2:$B$1000=$A4),Sheet1!$C$2:$C$1000))
Copy down

------------
Just make a copy** of Sheet2 if you need to extract results simultaneously but for a different period (startdate and enddate) to be specified. **Or as many copies as you may need

**MikeAW2010** · 08-18-2012, 06:33 PM

Thats not how its setup. The dates are in row 2. They're not with the departments in the columns. The dates are in row 2. The department s are in the column. The data is ranged between them... is my image displaying?[COLOR="Silver"]

---------- Post added at 03:33 PM ---------- Previous post was at 03:28 PM ----------

Thanks for the recommendation but I'm not allowed to alter the data table in anyway so I can't convert it to a pivot table.

**MikeAW2010** · 08-18-2012, 07:02 PM

Thanks but this isn't how the dates or data is arranged. First the dates aren't real dates, they're just date ranges.

**MarvinP** · 08-18-2012, 06:26 PM

Hi Mike,

This is a perfect reason for learning Pivot Tables. See the attached where I've created some fake data. Then I created a pivot table from the data and grouped it by Month. Then showed the amounts Min and Max by department. There are lots of easy ways to filter or display the data by dragging fields around in the Pivot Table List boxes.

I hope this gives you an idea of how to use Pivots to solve this problem.

ALSO - change the title of this thread so we can find it again - as Jeff suggests below.

**jeffreybrown** · 08-18-2012, 06:53 PM

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**MikeAW2010** · 08-18-2012, 07:18 PM

Done. I'm on a tight time limit and need an answer fast which is why I crossposted

**jeffreybrown** · 08-18-2012, 07:12 PM

You sure are hitting them all...

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

**MikeAW2010** · 08-19-2012, 02:30 AM

Originally Posted by jeffreybrown

You sure are hitting them all...

Know what ... you're becoming extremely annoying.

I'm typing from a darn cellphone because.I don't have a computer at home and I need an answer for when I get back to work. I copied and pasted because.its the only thing I can do that doesn't take a bloody hour to do on a cellphone.You have nothing but criticism over the most pointless matters and if this is how you treat people who are coming for help then I don't need it. Ill find my answer elsewhere... you can delete this thread now....or did I forget a rule about that too?

**Max, Singapore** · 08-19-2012, 04:18 AM

Think you've got 2 other answers in your cross post in that mrexcel site which could solve it for you
Btw, no need to get worked up because of forum rules and the diligence to which moderators try to apply it to ensure that the forum stays participative and useful to all. You've got several well intentioned responses here as well, which were posted primarily for your benefit which may come in handy for other scenarios

**MarvinP** · 08-19-2012, 11:12 AM

And my answer didn't work for you?? Why??

Fornula to use to retrieve data given specified month and department.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Fornula to use to retrieve data given specified month and department.

Re: I need help with a complicated look up...

Re: I need help with a complicated look up...

Re: I need help with a complicated look up...

Re: I need help with a complicated look up...

Re: I need help with a complicated look up...

Re: I need help with a complicated look up...

Re: Fornula to use to retrieve data given specified month and department.

Re: Fornula to use to retrieve data given specified month and department.

Re: Fornula to use to retrieve data given specified month and department.

Re: Fornula to use to retrieve data given specified month and department.

Re: Fornula to use to retrieve data given specified month and department.

Re: Fornula to use to retrieve data given specified month and department.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions