Hello,
I have a workbook containing prices in 4 worksheets using the same template. I need to tell excel to list the name of the sheet with the lowest value for each cell in one column and the actual lowest value in another column of my 5th worksheet. I have attempted formulas using the MIN and IF formulas, but I get various errors. Can someone tell me how to accomplish this? I appreciate any replies. Thank you.
Last edited by WORKINGBEE; 02-21-2013 at 02:58 PM.
Not the most eliquent but since you only have 4 sheets (i used 3 as an example
First in a specific cell on each sheet, enter this formula
That pulls the sheet name out and places it in that cell (my example in E1)Formula:
Please Login or Register to view this content.
Then on the summary sheet, I have data on each of your sheets in B2:C10
In B3 of the summary sheet,
copied and pasted across and downFormula:Please Login or Register to view this content.
Then in the cell where you want to know where this min value came from (in my case, starting in D3)
Formula:Please Login or Register to view this content.
Is that what you are looking for?
Note: if there are two values that tie for minimum, Excel will only find the first.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Oh! Thank you so much for responding! I will go try this.
Yes, yes, yes! Thank you very much. This worked and I learned a lot too.
Glad it worked for you.
That was my first time using this forum and I don't know if this is the way to go about asking another question to you. When I entered the formulas you figured out, they worked great. Now I have the need to find the percentage of times each sheet was found to have the lowest price. I tried doing a COUNTIF formula but It seemed to count the pages an excessive and incorrect amount of times. I only need to find the percentage of times each page was found on the summary page to contain the lowest price. Would you mind telling me the proper way to do that also? I hope you will see this post, as I am unsure if I need to start a whole new thread or just continue on like this. Thanks again for your help.
If it's a whole new issue, then you should start a new thread. If it's a continuation of an issue, you can continue in the same thread. We'll consider this a continuation of an issue.
If all of your sheet names are together on your summary sheet, with the sheet name of interest in A35,
=COUNTIF($A$2:$D$100, A35)/COUNTA($A$2:$A$100) format cell as percentage
If the ranges are not congruient, then need to sum the different names like so
=(COUNTIF($A$2:$A$100, A35)+COUNTIF($C$2:$C$100, A35) + COUNTIF($E$2:$E$100, A35))/COUNTA($A$2:$A$100, $C$2:$C$100, $E$2:$E$100)
See Attachment
Once again, you have given me excellent information. I so much appreciate you. I have saved the format of these new sheets and when I have to update these in a few months I would like to add a comparison page that will show the amount of change for each item. I am not sure what formulas would need to be used to compare two identical sheets and post the results on separate page showing the percent of change up or down for each item. This is something I will not be able to do until I create an updated identical price list, but I could start building the comparison page if I knew what formulas or what procedure needs to be done to obtain those results.
I will tell you this, doing all this work with the excel spreadsheets has most definitely peaked my interest and I would LOVE to be able to take a course (or two) on the subject. I am very interested in learning this, but at this time I cannot go to school. I only have a basic knowledge on this subject and most of that I learned by trial and error. When I went to school over 20 years ago, they weren't teaching this. I learned on Lotus. I do remember some of that but since I never used the program in my work, most of that knowledge faded away.
So I really am thankful for this forum and people willing to help. Thank you.
Glad we could help. If you have more questions, it's always easier if you upload a spreadsheet (Go Advanced>Manage Attachments) so that we can see exactly how your data is laid out. That can make a huge difference in what formulas we'd use.
Peruse this forum for a bit, looking at posts that seem like they relate to things you'd use and you will learn a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks