Getting Max & Min and Last Filled Row No from Multiple Sheets with the same Format from a specific Column.
Dear Forum,
I need to get the Max and the Min Values for a particular Lookup Value and also the Last Filled Row No from Multiple SHeets.
Basically, to make comparisons from all the Sheets...under consideration....
Warm Regards
e4excel
Last edited by e4excel; 02-14-2012 at 01:49 AM.
Please attach a sample file so its easier to understand your data.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi Arlette,
I was working on a Cricket File for our Company to maintain the Statistics..
Now Each Match is stored in a seperate SHeet and the statistics are maintained only for one-side for our employees and not for the opponents.
I need to get the below mentioned calculations from the DIfferent Sheets in the Total Sheet
I need to get the Max & Min No of Runs Scored for our Employees from the three Sheets.
Likewise I would also need to know the TOP_MOST Batting ORDER and the LOWER_MOST Batting Order...and also if I could get the Last Filled ROw No for another requirement.
Hope this can be achieved using Formulas...
Please find the attachment..
Warm REgards
e4excel
Last edited by e4excel; 02-10-2012 at 07:20 AM.
Dear Forum,
Getting MAX from DIfferent SHeets is possible but getting the same MAX for a particular LOOKUP value + if the Numbers are also suffixed with asterisk (*) to indicate that the BATSMAN was not out..
Complexities involved are not only due to the presence of Lookup Value but also due to the Data to be searched being on different rows..
Warm REgards
e4excel
Lets say if We were to have a Helper column with only nos excuding the "*" then is it possible to get the Max-Min for a particular lookup where the data is stored at different locations.
Could you please put the expected results to sheet Total? And show us how do they calculated?
Quang PT
Hi Bebo,
For Max for the First Player
Pritam Mohanty - Max would be by comparing the three sheets that is Match-1,..Match-3 and then the Max No of Runs scored in three matches he's played- would be 34,14,8 and so 34 should be the answer for Max and 8 for Min No of Runs..
Same for the Wickets too, Example Player is Amit Malhotra where his Max No of Wickets would be to comapre the Column I from the 3 Sheets..
1,2 and 4 so Max Outs - 4 and Min Outs -1..
To know whats the Top-Most Order where a particular player has played and also the Lower-Most Order.
Considering the example of Amit Malhotra - he's an allrounder( Pinch-Hitter) of sorts..
He's Top-Most would be 4 as he batted at the Fourth Position in the Match-2 and Lower-Most Order would be 10 as he batted at the 10th Position twice.
Also, if you could also help me with a generic answer to know the last filled row no incase the three sheets had different amounts of data..
I am hoping for a Formula solution if its possible or else even a VBA would do..!!!!
Warm REgards
e4excel
How would you expect the "last filled row" be, in this case?
Hi Bebo,
If I am looking at the Column A from each sheet Match-1,2 and 3 then lets say the answer would be 13 as in this case all the sheets contains the same no of Data however, if there was any disparity in the no of filled rows in all the three sheets then i Need the Max filled Row no amongs the SHeet Nos.
SO lets say- Sheet1- 10 Rows, Sheet-2 14 Rows and the Sheet-3 contains 6 Rows then the Last filled Row no would be 14.
These Sheets are not going to remain just three but would keep on increasing..
I think may be A defined Name for the SHeet Names would be a good idea..
Warm REgards
e4excel
See attachment to check whether it works or not.
Last filled row:
=MAX(MATCH("ZZZ",'Match-1'!A:A),MATCH("ZZZ",'Match-2'!A:A),MATCH("ZZZ",'Match-3'!A:A))
I have no idea for using formula if there are lots of worksheets... may VBA is best solution!
Dear Bebo,
I am aware of this method of using the SUMIF and I liked the approach however is there a way of getting it done for Multiple SHeets and Different Rows , I can always add a helper column to just have Number without the asterisk..
Your approach is indeed praiseworthy as You used the SUMIF to counter the different rows this can be iterated for different sheets. as the SHeets would keep on increasing and it can be maintained for atleast a year where as many as 35-38 Matches might be played.
Warm REgards
e4excel
Last edited by e4excel; 02-12-2012 at 03:26 AM.
Dear Forum,
Is there a way of getting the Max of the Filled Rows from Multiple Sheets with a Dynamic Formula ?
I intend to use a defined name to store the Sheet Names..
REgards
e4excel
Try this Array Formula.Originally Posted by e4excel
Enter the sheet names in a range & define a name SheetList, then
=MAX(IF(T(OFFSET(INDIRECT("'"&SheetList&"'!A1"),ROW(A1:A200)-ROW(A1),))<>"",ROW(A1:A200)))
NOTE: If you enter sheet names in vertical cells, use TRANSPOSE(SheetList)
Sheet separator in Array must be Comma NOT semi colon. TRANSPOSE will do that, if it is on vertical cells.
Assume column A contains only text NOT numbers.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Thanks a Lot Haseeb, Very Helpful
Wow that Surely Works and on adding new sheets also it should work..
However, IS there a different solution for text and numbers , cant we have a more generic solution which can still give us the Last Filled Row No from Multiple Sheets immaterial of its nature of content...
Warm REgards
e4excel
Dear Haseeb,
I was studying your code its just amazing, you actually have three arrays which are then compared for the MAX..
Can you please explain the code , would really appreciate that..
Warm REgards
e4excel
Try this Array Formula.
=MAX(IF(SUBTOTAL(3,OFFSET(INDIRECT("'"&SheetList&"'!A1"),ROW(A1:A200)-ROW(A1),)),ROW(A1:A200)))
As I mention previous post, If you enter sheet names in vertical cells, use TRANSPOSE(SheetList)
Hey Thanks Haseeb for the Independent Version...!
CAn you please explain the code ...
especially the TRANSPOSE portion...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks