Re: $$Excel-Macros$$ Count in cell formula
@ Gawli Anil, I am not seeing anyone spent time in giving solution for this, Just seeing copy paste, *Including your post*. On 18-01-2013 10:32 AM, ?? wrote: Hey Our User , Yaar don't get personal afterall we have to help others. Warm Regardsm Gawli Anil On Fri, Jan 18, 2013 at 2:46 AM, Excel_Lover idforex...@gmail.com mailto:idforex...@gmail.com wrote: ha ha!!! On Thu, Jan 17, 2013 at 2:22 PM, Ms-Exl-Learner ms.exl.lear...@gmail.com mailto:ms.exl.lear...@gmail.com wrote: @ Noorain Ansari, Could you please explain what is the use of /*Sumproduct*/ in your below formula? /*=SUMPRODUCT(LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1)*/ Why not it should be /*LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1*/? Since both will result the same answer. *My Suggestion Posted HALF AN HOUR Before to your post* *=IF(LEN(TRIM(C5)),LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1,)* It's surprising you have taken 30 Minutes time to replace the IF function??? even there is no Logic with replacement formula (/*Sumproduct*/ ) On 17-01-2013 4:19 PM, NOORAIN ANSARI wrote: Dear Amar, You can also try... /*=SUMPRODUCT(LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1)* / -- With Regards, Noorain Ansari http:// http://www.noorainansari.comnoorainansari.com http://www.noorainansari.com http:// http://www.excelvbaclinic.blogspot.comexcelvbaclinic.com http://www.excelvbaclinic.blogspot.com On Thu, Jan 17, 2013 at 3:40 PM, amar takale amartak...@gmail.com mailto:amartak...@gmail.com wrote: Dear champs Pl suggest simple count formula in cell -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post
Re: $$Excel-Macros$$ Count in cell formula
* In D5 cell =IF(LEN(TRIM(C5)),LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1,) Drag it down...* On 17-01-2013 3:40 PM, amar takale wrote: Dear champs Pl suggest simple count formula in cell -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Count in cell formula
@ Noorain Ansari, Could you please explain what is the use of /*Sumproduct*/ in your below formula? /*=SUMPRODUCT(LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1)*/ Why not it should be /*LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1*/? Since both will result the same answer. *My Suggestion Posted HALF AN HOUR Before to your post* *=IF(LEN(TRIM(C5)),LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1,)* It's surprising you have taken 30 Minutes time to replace the IF function??? even there is no Logic with replacement formula (/*Sumproduct*/ ) On 17-01-2013 4:19 PM, NOORAIN ANSARI wrote: Dear Amar, You can also try... /*=SUMPRODUCT(LEN(C5)-LEN(SUBSTITUTE(C5,,,))+1)* / -- With Regards, Noorain Ansari http:// http://www.noorainansari.comnoorainansari.com http://www.noorainansari.com http:// http://www.excelvbaclinic.blogspot.comexcelvbaclinic.com http://www.excelvbaclinic.blogspot.com On Thu, Jan 17, 2013 at 3:40 PM, amar takale amartak...@gmail.com mailto:amartak...@gmail.com wrote: Dear champs Pl suggest simple count formula in cell -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: formula required
@ Prince, Do you think is it a quality answer? and Dont you think you are re-directing the OP in wrong approach? =SUM(($D$2:D5=D2)**1**(G2:M2))*with CSE*in Cell O2 Could you please explain what is the need of **1* *and what is the need ofCSE here* *why not then one (Non Array Approach) which Manoj suggested before your post*? * On 14-01-2013 12:32 PM, Prince wrote: Hi Prashant, I hope it may help you: =SUM(($D$2:D5=D2)*1*(G2:M2)) with CSE in Cell O2 Regards Prince On Monday, January 14, 2013 12:19:32 PM UTC+5:30, ppawle.excel wrote: Dear Team, Need a formula to look up a filed give sum of figure , sample file attached Regards, Prashant -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Finding the relative position of an item in a list
*In F1 cell *- _*Non Array formula *_ *=LOOKUP(2,1/(A1:A8=D1),ROW(A1:A8))* OR *=SUMPRODUCT((MAX((A1:A8=D1)*ROW(A1:A8* On 10-01-2013 4:41 PM, Hilary Lomotey wrote: Hi Experts, In the attached, i have a list of items, some are repetitive in the list, if i want to find the relative position of each item what formula can be helpful, the normal match will for instance if an item appears twice in different positions will only give me the position of the first item, but i want a formula, that will give me the position of the second item if i select it -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Index of sheets
Not possible without the help of Excel VBA or Third Party Addins. On 10-01-2013 10:57 AM, Hari wrote: Thanks for your help. but is there any formulae to get the same answers. Thanks, Harish On 10 January 2013 10:11, The Viper viper@gmail.com mailto:viper@gmail.com wrote: Perhaps! He requires the list of available worksheet names from active workbook Sub SheetIndex() Dim i As Integer For i = 1 To ActiveWorkbook.Sheets.Count ActiveSheet.Range(A i).Value = Sheets(i).Name Range(A i).Select Next i End Sub pfa On Wed, Jan 9, 2013 at 5:59 PM, Paul Schreiner schreiner_p...@att.net mailto:schreiner_p...@att.net wrote: What is it that you're looking for? Are you writing VBA code and want to find out how many sheets are in a workbook? Are you trying to use the Sheets() collection to get something to do with the sheets? The Sheets() collection is an array of sheets. The index of the Sheets() collection is the specific sheet of the Sheets array. If the second sheet of the workbook is currently active, that would be Sheets(2) So, when you say get the index of sheets, do you mean you want to know what is the index number in the Sheets collection for the currently active sheet? Or are you not using VBA at all and need something else entirely? Please elaborate on your requirement. /Paul/ - /*“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley */- *From:* Hari harisha.prabha...@gmail.com mailto:harisha.prabha...@gmail.com *To:* excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com *Sent:* Wed, January 9, 2013 6:57:56 AM *Subject:* $$Excel-Macros$$ Index of sheets Dear excel gurus, Please let me know how to get the index of sheets in a given excel workbook. Thanks, Harish -- Yours, Hari. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ How to get max number from a database ?
*In C4 cell* - *Non Array Formula* =SUMPRODUCT(MAX(($A$4:$A$9567=A4)*$B$4:$B$9567)) Drag it down. On 09-01-2013 3:35 PM, karan kanuga wrote: Hi Can anyone pls let me know how do i get the max value or date (using a formula and not pivot) from the database that i have. Attached is the database. Thanks. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Index of sheets
@ Noorain, But Change in Worksheet Name or addition of sheets are not automatically getting added in your solution, for which the user needs to press F2 in the formula cell or close the workbook and re-open it for getting it reflected. Slight enhancement has been done Your A2 cell formula *=IFERROR(INDEX(SheetsName,ROWS($A$1:A1)),)* Is changed to *=IFERROR(MID(TODAY()INDEX(SheetsName,ROWS($A$1:A1)),6,255),)* As usual needs to be dragged down On 10-01-2013 12:12 PM, NOORAIN ANSARI wrote: Dear Hari, See attached file by using only Excel Formula. =REPLACE(Get.workbook(1),1,find(],Get.workbook(1)), ) Create Name range =IFERROR(INDEX(SheetsName,ROWS($A$1:A1)),) use as a formula On Thu, Jan 10, 2013 at 10:58 AM, Hari harisha.prabha...@gmail.com mailto:harisha.prabha...@gmail.com wrote: Thanks for your help. I got the answer for the same through below mail which i was looking for. Thanks, Harish On 9 January 2013 17:59, Paul Schreiner schreiner_p...@att.net mailto:schreiner_p...@att.net wrote: What is it that you're looking for? Are you writing VBA code and want to find out how many sheets are in a workbook? Are you trying to use the Sheets() collection to get something to do with the sheets? The Sheets() collection is an array of sheets. The index of the Sheets() collection is the specific sheet of the Sheets array. If the second sheet of the workbook is currently active, that would be Sheets(2) So, when you say get the index of sheets, do you mean you want to know what is the index number in the Sheets collection for the currently active sheet? Or are you not using VBA at all and need something else entirely? Please elaborate on your requirement. /Paul/ - /*“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley */- *From:* Hari harisha.prabha...@gmail.com mailto:harisha.prabha...@gmail.com *To:* excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com *Sent:* Wed, January 9, 2013 6:57:56 AM *Subject:* $$Excel-Macros$$ Index of sheets Dear excel gurus, Please let me know how to get the index of sheets in a given excel workbook. Thanks, Harish -- Yours, Hari. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message
Re: $$Excel-Macros$$ Need Help to seperate Date time
Please make it in an excel workbook and send it to us for our better understanding of your data structure. On 10-01-2013 1:01 PM, kumar.ashish861 wrote: Dear Seniors, Pls help to seperate date time, if both are in 1 cell. 7/11/2012 13:51 Formula req..! Thanks in advance Ashish kumar -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ need formula
In A2 cell
*=INDIRECT('$D$1'!ADDRESS(ROW(),COLUMN()+1))*
Drag it down and right...
On 10-01-2013 1:14 PM, Rajesh Agarwal wrote:
Dear Sir
If I change the sheet name in column D1 all the rows of column A B
change accordingly
need formula not macro
--
*Rajesh Kumar Agarwal*
*9811063001*
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners
and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Needs excel formula to find the running totals between two dates
_*Compatible, Faster Shorter Length formula *_*=SUMPRODUCT((A2:A11=F2)*(A2:A11= G2),(-B2:B11)+(C2:C11))* On 07-01-2013 9:00 PM, Yahya Muhammad wrote: Thanks Ashish. Is there any way we can combine two SUMPRODUCTS/SUMIFS into one, to reduce the length of formula ? On Mon, Jan 7, 2013 at 4:41 PM, ashish koul koul.ash...@gmail.com mailto:koul.ash...@gmail.com wrote: try this =SUMIFS(C:C,A:A,=F2,A:A,=G2)-SUMIFS(B:B,A:A,=F2,A:A,=G2) On Mon, Jan 7, 2013 at 7:06 PM, Yahya Muhammad yahya...@gmail.com mailto:yahya...@gmail.com wrote: Dear experts I have an excel file to track the bank transactions. It has a debit column for the withdrawals and credit column for the deposits. I can get the running totals by adding another column and just using simple excel formulae. However, sometimes I want to find the running totals in the account between two specific dates. I am currently using a SUMPRODUCT formula as attached, which I feel is too lengthy. I am sure that there will be a better method. Please suggest. Regardstry -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- *Regards* ** *Ashish Koul* */Visit/* */_My Excel Blog http://www.excelvbamacros.com/_/* Like Us on Facebook http://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ PBefore printing, think about the environment. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to
Re: $$Excel-Macros$$ Excel Fun Video
I don't know why you guy's are getting surprised for this one =-O . Because any of you can create lot more like this by just inserting any of your flash files in excel. *Whatever the magic is happening is just a shadow of the flash files and excel is just a window which holds it :-P * Same type of thread which is discussed earlier is given below for reference http://www.discussexcel.com/?place=topic%2Fexcel-macros%2FvCh_fDVbAKA%2Fdiscussion On 01-01-2013 4:16 PM, amar takale wrote: Dear my all Excel Group Members, I found Excel fun Video on Internet while searching Excel Solution I like very much.I hope you all like it fun Video.So I attached this Excel.Enjoy every excel users. Regards Amar -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Sumif not working
@Prince, I am not talking about the ways or methods of getting the result, which I never did, since that is the speciality of excel we can get the same result in many ways. But the previous post is not related to the method or ways of pulling the data, It's about the *errors and wrong usage***of functions. Whenever I see any formula with error I just point out, so that you will be aware of the drawbacks which will help you when building formula's in future. On 26-12-2012 1:38 PM, Prince wrote: Hi Ms-Exl-Learner, Yes dear, you are right. There are so many ways to do the same thing. And thanks alot for your kind information :) Regards Prince On Wednesday, December 26, 2012 12:48:10 PM UTC+5:30, Ms-Exl-Learner wrote: @Prince, Why not just added another m in your text function *TEXT(C16:C35,mmm)* which will avoid unnecessary MID Function. One more thing I don't think you need any case sensitivity function here (Proper in your formula). (TEXT(C16:C35,mmm)=MID(PROPER(B2),1,3))*1) TEXT(C16:C35,mmm) = This one is text output MID(PROPER(B2),1,3))*1 = What is the purpose of *1 in this??? Does it make any sense? On Wed, Dec 26, 2012 at 12:22 PM, Prince prince...@gmail.com javascript: wrote: Hi Rajesh, It may help you,. =SUM((D16:D35)*(TEXT(C16:C35,mmm)=MID(PROPER(B2),1,3))*1) with cse Regards Prince On Wednesday, December 26, 2012 11:24:19 AM UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.com javascript:. To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email
Re: $$Excel-Macros$$ Re: Sumif not working
@Prince, Thanks for the understanding :) On 26-12-2012 2:27 PM, Prince wrote: Yes Dear Ms-Exl-Learner, I also belive in correcting the things so that anything we do should be perfect and smooth. It is best form me that guys like you are always there who can ping me whenever i do any thing wrong . Thanks again for correcting me. :) Regards Prince On Wednesday, December 26, 2012 2:06:31 PM UTC+5:30, Ms-Exl-Learner wrote: @Prince, I am not talking about the ways or methods of getting the result, which I never did, since that is the speciality of excel we can get the same result in many ways. But the previous post is not related to the method or ways of pulling the data, It's about the *errors and wrong usage***of functions. Whenever I see any formula with error I just point out, so that you will be aware of the drawbacks which will help you when building formula's in future. On 26-12-2012 1:38 PM, Prince wrote: Hi Ms-Exl-Learner, Yes dear, you are right. There are so many ways to do the same thing. And thanks alot for your kind information :) Regards Prince On Wednesday, December 26, 2012 12:48:10 PM UTC+5:30, Ms-Exl-Learner wrote: @Prince, Why not just added another m in your text function *TEXT(C16:C35,mmm)* which will avoid unnecessary MID Function. One more thing I don't think you need any case sensitivity function here (Proper in your formula). (TEXT(C16:C35,mmm)=MID(PROPER(B2),1,3))*1) TEXT(C16:C35,mmm) = This one is text output MID(PROPER(B2),1,3))*1 = What is the purpose of *1 in this??? Does it make any sense? On Wed, Dec 26, 2012 at 12:22 PM, Prince prince...@gmail.com wrote: Hi Rajesh, It may help you,. =SUM((D16:D35)*(TEXT(C16:C35,mmm)=MID(PROPER(B2),1,3))*1) with cse Regards Prince On Wednesday, December 26, 2012 11:24:19 AM UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.com javascript:. To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en http://groups.google.com/group/excel
Re: $$Excel-Macros$$ Sumif not working
*In C16**cell* =IF(ISNUMBER(B16),TEXT(B16,),) *Drag it down.* *In D2 cell* =SUMIF($C$16:$C$380,$B2,(D$16:D$380)) *Drag it to right...* On 26-12-2012 11:24 AM, Rajesh thrissur wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Sumif not working
@Lalit, Why not a *Non Array Formula* suggestion? In D2 cell =SUMPRODUCT((TEXT($B$16:$B$380,)=$B2)*(D$16:D$380)) Drag it right Do you think your solution will work in all systems? The answer is *NO*. It *FAIL* in many systems because of different date formatting in *Regional DateTime Setting* :( When you are suggesting alternate solution, then be cautious about the side effects :) On 26-12-2012 11:49 AM, Lalit Mohan Pandey wrote: Hi Rajesh, Apply below formula instead of yours with Ctrl + Shift + Enter =SUM((MONTH($C$16:$C$380)=MONTH($B21))*(D$16:D$380)) and the mistake is the matching range is in date format and the criteria is in Text format. Regards, Lalit Mohan +919711867226 On Wednesday, 26 December 2012 11:24:19 UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Sumif not working
@ Lalit Mohan, You're Welcome and *accept my thanks to you* for *not taking anything in negative* :) Anyone can point out my solutions too... if my suggestion goes wrong :) On 26-12-2012 12:29 PM, Lalit Mohan Pandey wrote: Thanks Ms-Exl-Learner for the suggestion. Regards, Lalit Mohan On Wednesday, 26 December 2012 11:52:11 UTC+5:30, Ms-Exl-Learner wrote: @Lalit, Why not a *Non Array Formula* suggestion? In D2 cell =SUMPRODUCT((TEXT($B$16:$B$380,)=$B2)*(D$16:D$380)) Drag it right Do you think your solution will work in all systems? The answer is *NO*. It *FAIL* in many systems because of different date formatting in *Regional DateTime Setting* :( When you are suggesting alternate solution, then be cautious about the side effects :) On 26-12-2012 11:49 AM, Lalit Mohan Pandey wrote: Hi Rajesh, Apply below formula instead of yours with Ctrl + Shift + Enter =SUM((MONTH($C$16:$C$380)=MONTH($B21))*(D$16:D$380)) and the mistake is the matching range is in date format and the criteria is in Text format. Regards, Lalit Mohan +919711867226 On Wednesday, 26 December 2012 11:24:19 UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.com javascript:. To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Sumif not working
@Prince, Why not just added another m in your text function *TEXT(C16:C35,mmm)*which will avoid unnecessary MID Function. One more thing I don't think you need any case sensitivity function here (Proper in your formula). (TEXT(C16:C35,mmm)=MID(PROPER(B2),1,3))*1) TEXT(C16:C35,mmm) = This one is text output MID(PROPER(B2),1,3))*1 = What is the purpose of *1 in this??? Does it make any sense? On Wed, Dec 26, 2012 at 12:22 PM, Prince prince141...@gmail.com wrote: Hi Rajesh, It may help you,. =SUM((D16:D35)*(TEXT(C16:C35,mmm)=MID(PROPER(B2),1,3))*1) with cse Regards Prince On Wednesday, December 26, 2012 11:24:19 AM UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Merge entry cell
*In K7* =LOOKUP(10^10,E7:H7,E7:H7) *Non *Array Solution. On 14-12-2012 5:50 PM, amar takale wrote: Dear Experts Pls can anybody help me on this matter. As always, Thank you very much for all the help Regards Amar On Thu, Dec 13, 2012 at 1:00 PM, amar takale amartak...@gmail.com mailto:amartak...@gmail.com wrote: Dear all Can Anybody tell me formula to merge cell entry in one column if it is duplicate then over right on it Regards Amar -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Merge entry cell
Even shorter than the previous one, =LOOKUP(10^10,E7:H7) Just type =10^10 in a cell for knowing the evaluated result. On 12/17/12, amar takale amartak...@gmail.com wrote: Hi Dear, Solution is Perfect but waht is 10^10,I confused.I got Output but little confusing. Thank Very much On Mon, Dec 17, 2012 at 4:37 PM, Ms-Exl-Learner ms.exl.lear...@gmail.comwrote: =LOOKUP(10^10,E7:H7,E7:H7) -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- *Ms.Exl.Learner* -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Merge entry cell
Not sure whether this... For 2003 version of excel =IF(ISNA(LOOKUP(10^10,E7:H7)),,LOOKUP(10^10,E7:H7)) For excel version which is higher than 2003 =IFERROR(LOOKUP(10^10,E7:H7),) On 18-12-2012 10:16 AM, amar takale wrote: Dear Parvin, I want like If same number all cell something cell also miss=one number which same If single number=show single number that it simple. I used Ms-Exl-Learner that perfect but if all cell same but last cell different number then show that numbers,I want that time show error then i will know something is problem.If there are no numbers in cell then cell show blank not NA. Regards Amar On Mon, Dec 17, 2012 at 9:54 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.com mailto:ms.exl.lear...@gmail.com wrote: Even shorter than the previous one, =LOOKUP(10^10,E7:H7) Just type =10^10 in a cell for knowing the evaluated result. On 12/17/12, amar takale amartak...@gmail.com mailto:amartak...@gmail.com wrote: Hi Dear, Solution is Perfect but waht is 10^10,I confused.I got Output but little confusing. Thank Very much On Mon, Dec 17, 2012 at 4:37 PM, Ms-Exl-Learner ms.exl.lear...@gmail.com mailto:ms.exl.lear...@gmail.comwrote: =LOOKUP(10^10,E7:H7,E7:H7) -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- *Ms.Exl.Learner* -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1
Re: $$Excel-Macros$$ Help on V Lookup
In addition to the below post, you cannot use Sumproduct to get the Text Data as result. *Ms-Exl-Learner* On 10-10-2012 11:13 PM, Kuldeep Singh wrote: Hi Srinivas, Please use this. =SUMPRODUCT((A:A=G3)*(B:B=H3)*(C:C=I3)*(D:D=J3)*(E:E)) Regards, Kuldeep Singh On Wed, Oct 10, 2012 at 10:55 PM, Excel Beginner excelbegin...@gmail.com mailto:excelbegin...@gmail.com wrote: Hi Shrini, Find the Attachment. -- */Regards,/* */ /* */Excel Beginner/* */ /* On Wed, Oct 10, 2012 at 5:53 PM, Shrinivas Shevde shrinivas...@gmail.com mailto:shrinivas...@gmail.com wrote: Dear All Is it possible to use Vlookup with multple conditio. i e. Can I get the value From Column E if All the column a,b,c,d matches. Thanks in advance -- Shrini -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros%2bunsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles
Re: $$Excel-Macros$$ $$Excel-Macros$$ Need to find out date of latest data
Hi Vijayendra Copy and paste the below formula in AJ6 cell =IF(COUNT($G6:$R6),LOOKUP(10^10,$G6:$R6,$G$5:$R$5),) Drag the AJ6 formula to the remaining cells of AJ Column. Refer the attachment file for details. Hope that helps! Let us know, in case of any further assistance. *Ms-Exl-Learner* On 08-10-2012 12:44 PM, Vijayendra Rao wrote: Dear All, I have some data which includes amount data. I need the latest data’s date. Request you to let me know what formula I can use it for the same. Excel file is attached with example. Regards, Vijayendra 94491 67631 ಧನ್ಯವಾದಗಳು, ವಿಜಯೇಂದ್ರ, ೯೪೪೯೧ ೬೭೬೩೧ -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Latest data's date Solution.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Help on Macro
Hi Shrinivas, A sample workbook with the parameters and the expected output will be helpful for giving exact solution. *Ms-Exl-Learner* On 06-10-2012 9:01 AM, Shrinivas Shevde wrote: Dear Excel Learner First of all sorry for delay. Thanks for the reply and this is exactly I want. Can u help me little more In a master sheet there is sample Name I want to put 2 or 3 (may be more)more parameter like ,Date, Created by ,Cost etc.and all this should get transfer to respective sheet. All this should be mandatory. means if any of the fields are blank then data should not transfer. Thanks in advance. Regards Shrinivas Dear Don. I appretiate u r effort of understanding my problem. I got solution Thanks for help Regards Shrinivas On Thu, Oct 4, 2012 at 11:50 AM, Ms-Exl-Learner . ms.exl.lear...@gmail.com mailto:ms.exl.lear...@gmail.com wrote: Hi Shrinivas, Copy and paste the below code in Standard Module Option Explicit Sub PreserveData() Dim bWs As Worksheet, sName As String Dim i As Integer, myTemp1 As Byte, myTemp2 As Byte Application.ScreenUpdating = False On Error GoTo ShtMissing Set bWs = Sheets(Master Sheet) sName = bWs.Range(E1).Value If Trim(sName) = Then sName = Def. Sheets.Count End If For i = 1 To Sheets.Count If Sheets(i).Name = sName Then myTemp1 = 1 End If Next i If myTemp1 = 1 Then myTemp2 = MsgBox( Sheet ' sName ' is already exist, _ would you like to replace it? _ , vbQuestion + vbYesNo, Sheet Exist) End If If myTemp2 = 6 Then Application.DisplayAlerts = False Sheets(sName).Delete Application.DisplayAlerts = True ElseIf myTemp2 = 7 Then Exit Sub End If On Error GoTo 0 With bWs.Range(A1).CurrentRegion .AutoFilter Field:=2, Criteria1:= .EntireRow.SpecialCells(xlCellTypeVisible).Copy End With Sheets.Add After:=bWs With ActiveSheet .Name = sName .Paste .Range(A:E).Columns.AutoFit .Range(A1).Select End With bWs.Select Selection.AutoFilter Range(E1).Select MsgBox Sheet ' sName ' is created successfully, vbInformation, Task Completed Application.ScreenUpdating = True Exit Sub ShtMissing: MsgBox Master Sheet is Missing, Unable to continue..., vbCritical, Sheet Missing End Sub Press Alt+F8 and Select “PreserveData” Macro and click Run. Refer the attachment file for details. Hope that helps! Please let us know, in case of any further assistance. *Ms.Exl.Learner* On Mon, Oct 1, 2012 at 10:18 AM, Shrinivas Shevde shrinivas...@gmail.com mailto:shrinivas...@gmail.com wrote: Dear Don, Thanks for reply. Please find details what I want. 1.I have many raw material.Consider I have 50 raw material 2.I want to make a sample with this raw material.While making sample I will use some of the material out of 50 .% of the material wull vary . 3.To do this what I noted down all the raw material in one sheet (master sheet fro raw material) say Sheet No 1. 4.I will make 1st sample with 5 raw material and name it say 111. 5.Now while making another sample say 222 I want to store details of the 1st sample. 6.For that I want a macro so that when I run macro raw material which I used for 1st sample will store in another sheet (As I will required this in future for reference and need to take out print) and master sheet will get empty. 7.In a month I am making more that 65 samples. 8.So I amy required 65 sheet. I hope this is clear if anything is not clear please ask. Regards Shrinivas On Fri, Sep 28, 2012 at 6:16 PM, Don Guillett dguille...@gmail.com mailto:dguille...@gmail.com wrote: What you want is easy to do but I fail to see the LOGIC of what you want. Explain... On Friday, September 28, 2012 5:48:22 AM UTC-5, shrini wrote: Dear All Can Any one help me to write a macro for following things. In sheet 1 I have more than 200 items.I want to make a sample by choosing items from this list. 1.I will make a sample 111 by choosing Items A,C D .(rest items will be there but the percentage column will be blank.) 2.I will make a sample 222 by choosing Items B,E, D 3.Now there should be button if I click on that .Macro will create a new sheet and Details of Sample 111 will copy in that sheet In short whnever I will click on button it will create a new sheet and copy details of that sample. Sample file attached
Re: $$Excel-Macros$$ St id Reqd on Max Date
Hi Manoj, Go through the below for Non Array Formula Solution. *Try this for getting the Maximum Date based on Name* =SUMPRODUCT(MAX($A$2:$A$47*($C$2:$C$47=$E2))) *Try this for getting the ID based on Maximum Date of a name* =SUMPRODUCT(($C$2:$C$47=$E2)*($A$2:$A$47=SUMPRODUCT(MAX($A$2:$A$47*($C$2:$C$47=$E2*($B$2:$B$47)) Refer the attachment for details. Hope that helps! Please let us know, in case of any further assistance. --- *Ms.Exl.Learner* On Fri, Oct 5, 2012 at 11:51 AM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Dear Viper, Please can you solve it without erry..please help.. On Fri, Oct 5, 2012 at 11:47 AM, The Viper viper@gmail.com wrote: pfa On Fri, Oct 5, 2012 at 10:58 AM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Dear Expert, Request you to kindly help me on this matter... Please find the attachment.. I want id witch has on max dateeg:- if name1 max date is 3-Oct it show 9564 Regard Manoj -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- *Great day,* *viper* -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4)
Re: $$Excel-Macros$$ Required Passing formula (More confusing)
Or in one shot... =IF(SUMPRODUCT(--((F11:L11)($F$10:$L$10))),Failed,Passed) Refer the attached sheet for detail. . *Ms.Exl.Learner* -- *Ms.Exl.Learner* On Wed, Sep 5, 2012 at 1:24 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Dear Amar, Please Use it... =IF(OR(F11$F$10,G11$G$10,H11$H$10,I11$I$10,J11$J$10,K11$K$10,L11$L$10),Failed,Pass) you can use conditional formatting to change color. Regard On Wed, Sep 5, 2012 at 1:20 PM, amar takale amartak...@gmail.com wrote: Hi manoj, If failed minmum one subject then show failed (required output in yellow) (looking Minmum marks on Row numbers 10) F10:L10. If anyone got marks less than minimum marks then show failed. On Wed, Sep 5, 2012 at 1:10 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Dear amar, Please tell what is passing marks... Regard Manoj On Wed, Sep 5, 2012 at 1:06 PM, amar takale amartak...@gmail.comwrote: Hi All Experts, Pl solved challenging formula for passing. Appreciate the help very much -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security
Re: $$Excel-Macros$$ Simple formula required
Or =IF(ISERR(FIND(),D3)),,TRIM(MID(D3,FIND(),D3)+1,255))) *Ms.Exl.Learner* --- On Tue, Sep 4, 2012 at 2:01 PM, amar takale amartak...@gmail.com wrote: -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Need Help Urgent !!!!!!!!!!!!!!!!!!!
Better you should have created it in Ms-Access. *Ms.Exl.Learner* On Fri, Aug 24, 2012 at 5:45 PM, jocky Beta jocky6...@gmail.com wrote: Hi, Please find the attached excel sheet in which their is a report Tab which generates following report automatically 1)Current Stock 2)Current stock by Reference but following report it did not generate as there is no Macros code for the same. Current Stock by Supplier Entrances by Supplier Entrances by Month Entrances by year Exit by Customer Exit by Month Exit by Year hence request all of you to do the needful. *Regards,* *Jocky* -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ NUM ERROR
Using Vlookup Or Match Function with the duplicated data will pick only the first match and it wont pick the second or third occurrence of the data as expected. In your case your data is having 4 zero values in Total and your data needs to be get sorted by the Total Value. So we are setting the Total Value as base value and trying to pull the remaining data. But due to duplication (0) in Total the vlookup and Match functions will leads to result the first match instead of the next subsequent data. So we need to tell Vlookup or Match function that each data are different by way of adding some other Number with the Total Value which should not be common. So I am using the Row() Function for differentiating each data. =Row() Using the above function in any of the cell will get the current row number of that cell. For example if you use the =Row() in cell C5 then it will result 5 as result. The Row() function will get the Row Number and I just merged the Row Number and Total using the symbol. =ROW()O2 Will merge both the Number by Treating As Text Data, since comes under Text Data Type, so it makes both the numbers [Row() and Total] as text data. But we cannot use the Large or Small function with the Number which is considered as text. So we need to convert the Text Number to real number for calculation purpose. =--(ROW()O2) Value() - Length 7 characters -- (Unary Operator) - Length 2 characters Using Value function or using double hyphen (--) Unary operator in front of the text Number will convert it as Real Number. But the same will result #VALUE! Error when it is referred to Text Characters other than Text Number. Mostly I prefer to use Double Hyphen (--) (Length 2 chrs) instead of Value() Function (Length 7 chrs), because it will be useful for us when we are building a lengthy formula. Hope that helps! *Ms.Exl.Learner* On Fri, Aug 24, 2012 at 2:05 PM, Hilary Lomotey resp...@gmail.com wrote: Nice its working,this is a beautiful formula pal , i am learning new stuff everyday , but tell me what is the essence of =--(ROW()O2) in the formula? thanks On Fri, Aug 24, 2012 at 4:47 AM, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Refer the attached excel for modified solution. Let us know in case of any further assistance. --- Ms.Exl.Learner --- On Thu, Aug 23, 2012 at 8:26 PM, Hilary Lomotey resp...@gmail.comwrote: Hello Bosses, PFA, i am trying to sort my data(in sheet1) by the largest figures(in sheet 2), i am getting a num error in the formular and i also realised that when two figures have same figure it repeats the first instance all though i thought the below formula will resolve that. kindly assist. thanks INDEX(Sheet1!A$2:A$21,SMALL(IF(Sheet1!$N$2:$N$21=P20,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1,),COUNTIF(Sheet1!$N$2:N19,Sheet1!N19))) -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message
Re: $$Excel-Macros$$ Need a Suggestion for the formula
Try This...
=SUMPRODUCT(('Jan12'!F4:F60={Corrective,Implementation,Training,Information})*('Jan12'!H4:H60=DATA)*('Jan12'!G4:G60=Completed))
Let us know in case of any further assistance.
---
Ms.Exl.Learner
---
On Fri, Aug 17, 2012 at 10:13 PM, Santosh Kumar Singh str1...@gmail.comwrote:
Dear Group,
Please suggest
How we can reduce this formula (if Possible)
** **
=COUNTIFS('Jan12'!F4:F60,=Corrective,'Jan12'!H4:H60,=DATA,'Jan12'!G4:G60,=Completed)+COUNTIFS('Jan12'!F4:F60,=Implementation,'Jan12'!H4:H60,=DATA,'Jan12'!G4:G60,=Completed)+COUNTIFS('Jan12'!F4:F60,=Training,'Jan12'!H4:H60,=DATA,'Jan12'!G4:G60,=Completed)+COUNTIFS('Jan12'!F4:F60,=Information,'Jan12'!H4:H60,=DATA,'Jan12'!G4:G60,=Completed)
** **
** **
Thanks
Santosh Singh
7702004100
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups MS
EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Need help
Type or copy and paste the below formula in A4 cell. A4 cell =A$3/5 Drag the A4 cell formula upto A8 cell and drag it to the right if required. --- Ms.Exl.Learner --- On Wed, Aug 22, 2012 at 3:41 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: i need the formula witch divide randomly A3, in cell A4 to A8 when i add A4 to A8 it show 200.. On Wed, Aug 22, 2012 at 3:01 PM, Pravin Gunjal isk1...@gmail.com wrote: *Dear Mr. Manoj* * * *Type the value first to get the total.* * * *With regards, **Pravin Gunjal** * On Wed, Aug 22, 2012 at 2:50 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Hi, Please help me to collect data in attached file.. Regard Manoj -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't
Re: $$Excel-Macros$$ Need help
For Macro Solution Try the below code Option Explicit Sub SplitNumber() Dim i As Long, j As Long, EndCol As Long EndCol = Range(A2).CurrentRegion.Rows(1).Cells.Count For i = 1 To EndCol For j = 1 To Cells(2, i).Value Cells(3 + j, i).Value = Cells(3, i).Value / Cells(2, i).Value Next j Next i End Sub --- Ms.Exl.Learner --- On Wed, Aug 22, 2012 at 2:50 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Hi, Please help me to collect data in attached file.. Regard Manoj -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Macro Solution.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Need help
You're Welcome --- Ms.Exl.Learner --- On Wed, Aug 22, 2012 at 4:26 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Many many Thanks for solution... On Wed, Aug 22, 2012 at 4:23 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: For Macro Solution Try the below code Option Explicit Sub SplitNumber() Dim i As Long, j As Long, EndCol As Long EndCol = Range(A2).CurrentRegion.Rows(1).Cells.Count For i = 1 To EndCol For j = 1 To Cells(2, i).Value Cells(3 + j, i).Value = Cells(3, i).Value / Cells(2, i).Value Next j Next i End Sub --- Ms.Exl.Learner --- On Wed, Aug 22, 2012 at 2:50 PM, Manoj Kumar kumarmanoj.11...@gmail.comwrote: Hi, Please help me to collect data in attached file.. Regard Manoj -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting
Re: $$Excel-Macros$$ Re: plz solve problem
Hi Pravesh,
Copy and paste the below formula in C15 cell
=VLOOKUP($D$2,INDIRECT('$D$3'!$A$1:$E$7),MATCH(C$14,INDIRECT('$D$3'!$A$1:$E$1),0),0)
Drag the C15 cell formula to Right upto F15 cell.
Hope that helps!
---
Ms.Exl.Learner
---
On Sat, Aug 18, 2012 at 1:01 PM, PRAVESH KUMAR praveshkash...@gmail.comwrote:
Hi experts
please solve this problem for the same file attached.
Thanks
Pravesh Kumar
On 8/15/12, PRAVESH KUMAR praveshkash...@gmail.com wrote:
Hi All,
please find attached file and solve my problem urgent.
Thanks
Pravesh
--
*Thanks Regards*
*Pravesh Kumar*
--
Thanks Regards
Pravesh Kumar
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
MS EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups MS
EXCEL AND VBA MACROS group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Split text in cell
Another Approach Copy and paste the below formula in 2nd Row of any cell other than A2 cell and drag it below. =IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)))1, TRIM(MID(TRIM(A2), FIND( ,TRIM(A2))+1, (FIND(^,SUBSTITUTE(TRIM(A2), ,^,(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)-1)-FIND( ,TRIM(A2))+1)), ) Refer the attachment file for better understanding. On Wed, Mar 21, 2012 at 2:30 PM, NRao Mynampati ml.narasimha...@gmail.comwrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Split text - Solution File.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ shortcut key .
Alt+D+F+F --- Ms.Exl.Learner --- On Sun, Dec 18, 2011 at 10:41 AM, vijayajith VA vijayajith...@gmail.comwrote: Hello sir Can you tell me what is shortcut key for data clear(filter)? Thanks -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ shortcut key .
The same is applicable for removing filter also. --- Ms.Exl.Learner --- On Sun, Dec 18, 2011 at 12:17 PM, vijayajith VA vijayajith...@gmail.comwrote: Sorry . that is for put filter and remove filter.i need to clear checked items .in filtered coloumn. On Sun, Dec 18, 2011 at 12:10 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Alt+D+F+F --- Ms.Exl.Learner --- On Sun, Dec 18, 2011 at 10:41 AM, vijayajith VA vijayajith...@gmail.comwrote: Hello sir Can you tell me what is shortcut key for data clear(filter)? Thanks -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Cell Formatting
Hi, Just convert it to real time and apply time format as well. =--(LEFT(A1,2):RIGHT(A1,2)) --- Ms.Exl.Learner --- On Tue, Nov 22, 2011 at 5:30 PM, jmothilal gjmothi...@gmail.com wrote: use this =LEFT(A1,2):RIGHT(A1,2) Mothilal.J On Tue, Nov 22, 2011 at 2:57 PM, hemant hemantda...@yahoo.com wrote: Hi all I am supposed to enter data in say 22:11 format in Excel 2007. Is there any method by which i will enter only 2211 and cell shows it as 22:11. Thus it will save my time to enter : (Semicolon) frquently. I am supposed to enter lot of data in this format and thus has to enter : also. Thanks Regards Hemant Dange -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Rounding method
Hi, Assume that your input data is A2 cell and apply the below formula in B2 cell. =A2-MOD(A2,5) Refer the attachment for details. --- Ms.Exl.Learner --- On Wed, Nov 9, 2011 at 2:11 PM, SAJID MEMON sajidwi...@hotmail.com wrote: Dear Experts, I want rounding method in excel 2003 when i typed 124.90 the rounding should come 120 when i typed 125.10 the rounding should come 125 when i typed 129.90 the rounding should come 125 5 digits upward or downward rounding Regards Sajid Memon -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Rounding to related Value Solution.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Rounding Method
Hi Sajid, Refer the reply given to another post for the same subject. --- Ms.Exl.Learner --- On Wed, Nov 9, 2011 at 2:10 PM, Advocate kbj msma@gmail.com wrote: Dear Experts, I want rounding method in excel 2003 when i typed 124.90 the rounding should come 120 when i typed 125.10 the rounding should come 125 when i typed 129.90 the rounding should come 125 5 digits upward or downward rounding Regards Sajid Memon -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Rounding Method
Hi Mothilal, Yours is the exact and simplified solution. In fact, I forgot that function and I was unable to recollect it while replying to the OP's question. But, your reply helped me to recall it. Thanks Dude... --- Ms.Exl.Learner --- On Wed, Nov 9, 2011 at 5:36 PM, jmothilal gjmothi...@gmail.com wrote: use this command =floor(a1,5) Mothilal.J[image: image.png] On Wed, Nov 9, 2011 at 2:10 PM, Advocate kbj msma@gmail.com wrote: Dear Experts, I want rounding method in excel 2003 when i typed 124.90 the rounding should come 120 when i typed 125.10 the rounding should come 125 when i typed 129.90 the rounding should come 125 5 digits upward or downward rounding Regards Sajid Memon -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- J.Mohilal Universal Computer Systems # 16, Brindavan Complex Otteri, Vellore-2 -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com image.png
Re: $$Excel-Macros$$ formulas to get the week in the month and week of the year
Hi Sara, Refer the below thread for feasibility. http://www.excelforum.com/excel-worksheet-functions/639013-number-of-the-week-in-month.html --- Ms.Exl.Learner --- On Mon, Oct 31, 2011 at 6:49 AM, Sara Lee lee.sar...@gmail.com wrote: hi i have a column in excel with the following date format-- ship date 20110801 20110823 20110903 I need another column adjacent to it which gives me the following results for every respective date above-- basically calcualate month and the week in that particular month in below format... Also the week column should give the week no in the year . for example 20110801 might represent 32 nd week in the year . so i need to fill up 2 columns week-monthweek 08-1 week 08- 4 week 09 -1 week what formulas should i use to generate both the columns -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Formula needed to extract the text from string
Hi Anil, I Assume that your data is in Column A and your first row is having the column header. So your data will start from 2nd row of Column A (i.e. from A2 cell) A1 Data A2 /ENTRY-10 OCT TRF/REF 6004ABS6834230 /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247 ACCOUNTNO 019481 A3 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011 A4 /ENTRY-10 OCTTRF/REF 6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/20110295 TRANSACTIEDATUM 10-10-2011 Copy and paste the below formula in any of the 2nd row cell (i.e. B2 cell) =TRIM(MID(SUBSTITUTE(/A2REPT(/,6),/,REPT(CHAR(32),255)),5*255,255)) Drag the B2 cell formula below for the remaining cells of B column. Hope that helps! --- Ms.Exl.Learner -- On Wed, Oct 12, 2011 at 1:20 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Expert, ** ** I needed the formula which can extract the specific content from a Text, below is some sample excel cells ** ** To simplify this there is “/” sign after each content, and I need exact data which *start after 4th “/” and till 5th “/”* (which is mention below in Red). ** ** Can anybody tell me the formula. ** ** ** ** ** ** /ENTRY-10 OCT TRF/REF 6004ABS6834230 /ORD/* Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM DE MEERN */BNF/ INVOICENO 201100247 ACCOU NTNO019481 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD */BNF/ 20110279 TRANSACTIEDATUM 10-10-2011 /ENTRY-10 OCT TRF/REF 6004ABS68300025023 /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD** */BNF/ 20110295 TRANSACTIEDATUM 10-10-2011 ** ** ** ** ** ** ** ** ** ** ** ** *Anil Bhange* *Assistant Manager* Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai – 400 001, India ** ** ' Desk : + 91 22 6659 2320 | IP Phone : 51 2320 | Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.com ** ** ** ** -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Formula needed to extract the text from string
Hi Anil, Thanks for your reply and you are welcome! But I am a part time visitor of this group and you can find my replies only for the questions which is interesting to work with it. From my point of view many of the extra-ordinary excel forums / discussion groups are available and this is one of it, since the Legendary Excel MVP Don Guillett also getting joined in this group. --- Ms.Exl.Learner -- On Wed, Oct 12, 2011 at 4:13 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Amazing… Ms-Exl-learner… ** ** This is exactly what I wanted… I was pretty sure this forum only can help me with solution… ** ** Thanks once again.. this saves my lot of time… ** ** Regards,Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Ms-Exl-Learner . *Sent:* Wednesday, October 12, 2011 03:34 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Formula needed to extract the text from string ** ** Hi Anil, ** ** I Assume that your data is in Column A and your first row is having the column header. ** ** So your data will start from 2nd row of Column A (i.e. from A2 cell) ** ** A1 Data A2 /ENTRY-10 OCT TRF/REF 6004ABS6834230 /ORD/ Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PMDE MEERN /BNF/ INVOICENO 201100247 ACCOUNTNO019481 A3 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/20110279 TRANSACTIEDATUM 10-10-2011 A4 /ENTRY-10 OCTTRF/REF 6004ABS68300025023/ORD/ U-WISS CONSULTANCY OUDAEN 26 LELYSTAD /BNF/20110295 TRANSACTIEDATUM 10-10-2011 ** ** Copy and paste the below formula in any of the 2nd row cell (i.e. B2 cell) =TRIM(MID(SUBSTITUTE(/A2REPT(/,6),/,REPT(CHAR(32),255)),5*255,255)) ** ** Drag the B2 cell formula below for the remaining cells of B column. ** ** Hope that helps! ** ** --- Ms.Exl.Learner -- ** ** On Wed, Oct 12, 2011 at 1:20 PM, Anil Bhange anil.bha...@tatacommunications.com wrote: Hi Expert, I needed the formula which can extract the specific content from a Text, below is some sample excel cells To simplify this there is “/” sign after each content, and I need exact data which *start after 4th “/” and till 5th “/”* (which is mention below in Red). Can anybody tell me the formula. /ENTRY-10 OCT TRF/REF 6004ABS6834230 /ORD/* Q-FAST TELECOM BV STRIJKVIERTEL 26 A 3454 PM DE MEERN */BNF/ INVOICENO 201100247 ACCOU NTNO019481 /ENTRY-10 OCT TRF/REF 6004ABS68300014073 /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD */BNF/ 20110279 TRANSACTIEDATUM 10-10-2011 /ENTRY-10 OCT TRF/REF 6004ABS68300025023 /ORD/* U-WISS CONSULTANCY OUDAEN 26 LELYSTAD** */BNF/ 20110295 TRANSACTIEDATUM 10-10-2011 *Anil Bhange* *Assistant Manager* Financial Reporting Compliance, TATA Communications Ltd. VSB, Fort, Mumbai – 400 001, India ' Desk : + 91 22 6659 2320 | IP Phone : 51 2320 | Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.com -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
Re: $$Excel-Macros$$ Need a help -----very urgent
Hi B.N Chetan kumar, Try this... =--SUBSTITUTE(A3, ,-) Or =IF(ISERR(--SUBSTITUTE(A3, ,-)),,--SUBSTITUTE(A3, ,-)) Hope that helps! --- Ms.Exl.Learner --- On Thu, Oct 6, 2011 at 10:20 AM, B.N.Chethan kumar chetankumar1...@gmail.com wrote: Hi All, I have attached file where i get some data in text format. I need convert to date and time format. Currently using text to coloumn method which is manually pocess. As sheet count is high in a workbook, these is very painful work to do in month end. Kindly request your help to very painful work. -- Regards B.N Chetan kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ examine three columns return the one that matches criteria
Hi, Something like this.. =IF(ISNUMBER(SEARCH(FL,IR3)),IR3,IF(ISNUMBER(SEARCH(FL,IS3)),IS3,IF(ISNUMBER(SEARCH(FL,IT3)),IT3,IF(ISNUMBER(SEARCH(FL,IU3)),IU3, The above formula check from Cell IR3 to IU3, if any match is found from the starting cell then it stops the the remaining conditions and get the cell value. Change the cell references to your desired cell, if required. If you want to show NO when there is no matches found in the specified cells then instead of blank change it as No like the below. =IF(ISNUMBER(SEARCH(FL,IR3)),IR3,IF(ISNUMBER(SEARCH(FL,IS3)),IS3,IF(ISNUMBER(SEARCH(FL,IT3)),IT3,IF(ISNUMBER(SEARCH(FL,IU3)),IU3,NO Hope that helps! --- Ms.Exl.Learner --- On Tue, Sep 27, 2011 at 11:11 PM, plus766 pluss...@gmail.com wrote: I have a spreadsheet where I've done an index match to return criteria. I have to match three or four columns to get the information that I need, so I have three or 4 columns that either have the information I need or a NO from the index match. I need a formula to look at the columns and say, if the value is in this column, return it, otherwise go to the next column, etc. also I have the formula IF(ISERROR(SEARCH(FL:,IR3)),,IR3) but where the FL: is, may be FL:ABC or FL:XYZ, with many combinations. so I need to match the 3 or 4 columns, and return the value that contains FL: with something else after it. I hope this makes sense. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Find the month of sales cross the target
Hi Deepak, Copy and paste the below formula in Second Row after Column H. =IF(B2100,B$1,IF(SUM(B2:C2)100,C$1,IF(SUM(B2:D2)100,D$1,IF(SUM(B2:E2)100,E$1,IF(SUM(B2:F2)100,F$1,IF(SUM(B2:G2)100,G$1,IF(SUM(B2:H2)100,H$1,Not Qualified))) I have attached an example file for your better understanding. Hope that helps! --- Ms.Exl.Learner --- On Fri, Sep 23, 2011 at 3:04 PM, Deepak Rawat deepakexce...@gmail.comwrote: Hil All I have attached the file of data. My problem is i have to find the qualifying month of sales by a formula. if it does not qualify then it should reflect as Not Qualify Pls Help!! Regards, Deepak -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- --- Ms.Exl.Learner --- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Sale Cross Querry - Solution.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Message on Cell
Hi Ankit, Go through the below links to have a better understanding about Validation in Excel. http://www.contextures.com/xlDataVal06.html http://www.contextures.com/xlDataVal04.html http://www.contextures.com/xlDataVal07.html Hope that helps! --- Ms.Exl.Learner --- On Fri, Sep 23, 2011 at 10:42 AM, Ankit Agrawal ankit.agrawal...@gmail.comwrote: Dear All, I want to show a messge when we enter value which is more than 50k.As we type 6 at that time a message show approval need. Could it possible? Regards, Ankit -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Converting date with time to date withOUT time
Hi Mich Mac, It's better if you provide some sample data in excel file, so that we can suggest you the exact formula to your expectation. --- Ms.Exl.Learner --- On Fri, Sep 16, 2011 at 1:39 AM, Mich Mac michelle.maccrac...@nmss.orgwrote: I am exporting a file from online which automatically includes dates. This interferes with my Pivot tables. How do I convert an entire column of dates with the time to dates without the time. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Problem with Sumifs formula
Hi Kalyan, Try the below formula. =SUMPRODUCT((TRIM(A2:A37)=TRIM(F11))*(TRIM(B2:B37)=TRIM(G7))*(LEFT(TRIM(C2:C37),3)=LEFT(TRIM(G8),3))*(--MID(TRIM(C2:C37),FIND( ,TRIM(C2:C37))+1,255)=--MID(TRIM(G8),FIND( ,TRIM(G8))+1,255))*(--MID(TRIM(C2:C37),FIND( ,TRIM(C2:C37))+1,255)=--MID(TRIM(G9),FIND( ,TRIM(G9))+1,255))*D2:D37) Refer the attached file for further details. --- Ms.Exl.Learner --- On Fri, Sep 16, 2011 at 4:11 PM, Kal xcel kalx...@gmail.com wrote: Dear Experts, I am facing a problem when using sumifs formula, not getting desired result. Plase help me to rectify the problem or better solution. Thanks in advance -- *Kalyan Chattopadhyay* *Executive Sales Coordinator* *R. S. H. Pvt. Ltd.* -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Problem with Sumifs Formula-Resolved.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Two columns and three criteria
Hi ArtySin, Refer the attachment file for details. Hope That Helps! --- Ms.Exl.Learner --- On Thu, Sep 15, 2011 at 9:39 PM, ArtySin kenstrain...@gmail.com wrote: Hi I have two columns as below using Excel 2000 (unfortunately, corporate no spend policy): These columns have been exported from a database and appear as the example below. A B Status Date Run Passed 22/08/2011 Failed 22/08/2011 Failed 23/08/2011 Passed 22/08/2011 Passed 23/08/2011 N/A 22/08/2011 N/A 22/08/2011 Passed 22/08/2011 N/A 23/08/2011 Failed 23/08/2011 What I want to do is add up the number of times either passed or failed appears against a date but do not want to include the N/A. For example to count up the total of passed or failed for the 22/08/2011 would give a result of 4 I've tried =SUMPRODUCT(--($A$2:$A$11=A2)--($A$2:$A$11=A3)*--($B$2:$B $11=B2)) and various countifs with AND statements but I can't get this to work. Many thanks ArtySin -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Sumproduct Solution (15-Sep-2011).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Vlookup with more than one condition
Hi Mr. Shrinivas,
Correcting a mistake won't be treated as hurting and if you feel like that
then it's your way of thought and I don't want to comment about it. Real
experts never like to see wrong answers getting delivered to the questioners
and that was I did. If I am providing a wrong solution, then anyone can
criticize my solution and I am always welcome to accept and learn from them.
Debate on excel will bring new ideas / techniques and which will yield
better results rather than redirecting and providing wrong answer to the
questioner.
At the same time I am not responsible for your feelings and I don't want to
waste my time in discussing and giving reply for your own feelings which is
not related to excel.
Better you can find any mistakes in my excel solutions and criticize it here
which will be good for us and others. If you want to criticize something
not related to excel then please don't include me and my name in your post
and this is not the place to discuss.
Hope that make sense!
---
Ms.Exl.Learner
---
On Sat, Sep 3, 2011 at 1:03 PM, Shrinivas Shevde shrinivas...@gmail.comwrote:
Dear All
I am a member of this group for last more than 2 years.
I can proudly say that u will find the solutions(not solution) for all u r
problem.
By reading the above mail (from Noorani and Excel Learner) I got some
different feeling.So my suggestion is dont critise any one
Dont write any mail which will hurt someone
Shrinivas
On Sat, Sep 3, 2011 at 6:07 AM, Ms-Exl-Learner . ms.exl.lear...@gmail.com
wrote:
I am tired...
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 11:48 PM, NOORAIN ANSARI noorain.ans...@gmail.com
wrote:
Dear MS.EXL.Learner,
Please see attached sheet...
**
Dear i better understand work and Concept of below mentioned
functionsi never denied to differencition between each
functions.
but at a time we can use a function for multiple purpose.
* *
*[vlookup, Index-Match, Offset-Match]* and *[Sumproduct, Sumifs, Dsum]*.
Thanks for your suggestion..
--
*Thanks regards,*
*Noorain Ansari*
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 11:11 PM, Ms-Exl-Learner .
ms.exl.lear...@gmail.com wrote:
Hi Noorain Ansari,
Case-1
I have attached the same file for your reference and I have not changed
anything on it just created a duplicate data.
Now look at the excel file and see what are all the results your formula
is deriving. Whether all the results are same? Take my previous mail and
read it once again which is elaborated clearly without the help of the
above
attachment about the differences *[vlookup, Index-Match, Offset-Match]*
and *[Sumproduct, Sumifs, Dsum]*.
Case-II
I know sumproduct very well and the method how we are using the function
will differentiate the sumproduct from other functions. But you have used
the sumproduct for a single cell, which can be done using the simple IF
function and there is no need to go for sumproduct. I just wanted to
highlight it to you, and that was I did.
Never create and suggest formula's based on the articles you read in
internet or book, understand the concept how the functions are working and
it's concept then only you can able to give exact solution.
Let me know if you need any further clarification.
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 10:38 PM, NOORAIN ANSARI
noorain.ans...@gmail.com wrote:
Dear Ms-Exl-Learner,
Thanks for your valuable suggestion..
Case -I, In case of duplicay all formulas are successfull working
except vlookup(vlookup) example.
You can see fresh attachement..
Case -II, You can't compare Sumproduct with If function both are
different..
Correct Syntex of Formula Should be..
=SUMPRODUCT((C3:C8=G3)*(D3:D8=H3)*E3:E8) .instead of
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
another way :
*{=LOOKUP(2,1/(C3:C8=G3)*(D3:D8=H3),E3:E8)}*
Keep Enjoy, Cheers..
--
Thanks regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 9:51 PM, Ms-Exl-Learner .
ms.exl.lear...@gmail.com wrote:
Hi Noorain Ansari,
It might be better if you might have constructed your example data
with some duplicates, since the questioner can able to understand the
difference between the *[vlookup, Index-Match, Offset-Match]* and
*[Sumproduct,
Sumifs, Dsum]*.
The *First set of formula's / Functions* just get the first match as
result and never consider the next matches. But the *Second
functions* consider all the matches and derive the result by adding
the values of the all matches.
Please clarify me why the below formula should not be written
as =IF((F3=J3)*(G3=K3),H3,0) or =IF(AND((F3=J3),(G3=K3)),H3,0)???
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
You have provided
Re: $$Excel-Macros$$ Vlookup with more than one condition
Hi Noorain Ansari,
It might be better if you might have constructed your example data with some
duplicates, since the questioner can able to understand the difference
between the *[vlookup, Index-Match, Offset-Match]* and *[Sumproduct, Sumifs,
Dsum]*.
The *First set of formula's / Functions* just get the first match as result
and never consider the next matches. But the *Second functions* consider
all the matches and derive the result by adding the values of the all
matches.
Please clarify me why the below formula should not be written
as =IF((F3=J3)*(G3=K3),H3,0) or =IF(AND((F3=J3),(G3=K3)),H3,0)???
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
You have provided the below formula to avoid the K3 cell criteria to be
automated by the formula
=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)
But,
It won't work fine when the K3 cell is having the 31-Aug as criteria and the
G5 cell have the 31-Aug.
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 8:51 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear Haytham,
Please try below formula :
*=VLOOKUP(J3K3,$E$3:$H$3,4,0)*
*=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)*
other Alternative...
*=SUMPRODUCT((F3=J3)*(G3=K3)*H3)*
*{=INDEX($H$3:$H$8,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0))}*
*{=OFFSET($H$2,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0),0)}
=SUMIFS(H3:H8,F3:F8,J3,G3:G8,K3)*
*=DSUM($F$2:$H$8,H2,J2:K3)*
See attached sheet..
--
Thanks regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 5:02 PM, Haytham Zoromba
haythamzoro...@gmail.comwrote:
Dear all,
I have tried to search about using vlookup with more than one condition.
Is their any one know a formula for that?
BRegards,
Haytham Zoromba
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ difference time calculation
Hi Noorain Ansari, But the text function will result text value instead of real value. It's better to format the cell as per your desired format by using custom format. --- Ms.Exl.Learner --- On Fri, Sep 2, 2011 at 5:11 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Waheed, Text Function are used to convert time differences as Hour:Minute:Second Format. On Fri, Sep 2, 2011 at 5:02 PM, Shaik Waheed waheedb...@gmail.com wrote: Hi Noorain, Can u plz tell me y we r using TExt Function here. On Fri, Aug 19, 2011 at 2:45 PM, NOORAIN ANSARI noorain.ans...@gmail.com wrote: Dear Jayendra, Please use below formula see attached sheet.. =TEXT((C2+D2)-(A2+B2),[H]:mm:ss) -- Thanks regards, Noorain Ansari http://noorain-ansari.blogspot.com/ On Fri, Aug 19, 2011 at 1:28 PM, jayendra gaurav jayendra.gau...@gmail.com wrote: Dear team, Kindly help me in this regards -- J.Gaurav Operation Manager -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks regards, Noorain Ansari *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Vlookup with more than one condition
Hi Noorain Ansari,
Case-1
I have attached the same file for your reference and I have not changed
anything on it just created a duplicate data.
Now look at the excel file and see what are all the results your formula is
deriving. Whether all the results are same? Take my previous mail and read
it once again which is elaborated clearly without the help of the above
attachment about the differences *[vlookup, Index-Match, Offset-Match]* and
*[Sumproduct, Sumifs, Dsum]*.
Case-II
I know sumproduct very well and the method how we are using the function
will differentiate the sumproduct from other functions. But you have used
the sumproduct for a single cell, which can be done using the simple IF
function and there is no need to go for sumproduct. I just wanted to
highlight it to you, and that was I did.
Never create and suggest formula's based on the articles you read in
internet or book, understand the concept how the functions are working and
it's concept then only you can able to give exact solution.
Let me know if you need any further clarification.
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 10:38 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear Ms-Exl-Learner,
Thanks for your valuable suggestion..
Case -I, In case of duplicay all formulas are successfull working except
vlookup(vlookup) example.
You can see fresh attachement..
Case -II, You can't compare Sumproduct with If function both are
different..
Correct Syntex of Formula Should be..
=SUMPRODUCT((C3:C8=G3)*(D3:D8=H3)*E3:E8) .instead of
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
another way :
*{=LOOKUP(2,1/(C3:C8=G3)*(D3:D8=H3),E3:E8)}*
Keep Enjoy, Cheers..
--
Thanks regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 9:51 PM, Ms-Exl-Learner . ms.exl.lear...@gmail.com
wrote:
Hi Noorain Ansari,
It might be better if you might have constructed your example data with
some duplicates, since the questioner can able to understand the difference
between the *[vlookup, Index-Match, Offset-Match]* and *[Sumproduct,
Sumifs, Dsum]*.
The *First set of formula's / Functions* just get the first match as
result and never consider the next matches. But the *Second
functions*consider all the matches and derive the result by adding the
values of the
all matches.
Please clarify me why the below formula should not be written
as =IF((F3=J3)*(G3=K3),H3,0) or =IF(AND((F3=J3),(G3=K3)),H3,0)???
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
You have provided the below formula to avoid the K3 cell criteria to be
automated by the formula
=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)
But,
It won't work fine when the K3 cell is having the 31-Aug as criteria and
the G5 cell have the 31-Aug.
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 8:51 PM, NOORAIN ANSARI
noorain.ans...@gmail.com wrote:
Dear Haytham,
Please try below formula :
*=VLOOKUP(J3K3,$E$3:$H$3,4,0)*
*=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)*
other Alternative...
*=SUMPRODUCT((F3=J3)*(G3=K3)*H3)*
*{=INDEX($H$3:$H$8,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0))}*
*{=OFFSET($H$2,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0),0)}
=SUMIFS(H3:H8,F3:F8,J3,G3:G8,K3)*
*=DSUM($F$2:$H$8,H2,J2:K3)*
See attached sheet..
--
Thanks regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 5:02 PM, Haytham Zoromba
haythamzoro...@gmail.com wrote:
Dear all,
I have tried to search about using vlookup with more than one condition.
Is their any one know a formula for that?
BRegards,
Haytham Zoromba
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page
Re: $$Excel-Macros$$ Vlookup with more than one condition
I am tired...
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 11:48 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:
Dear MS.EXL.Learner,
Please see attached sheet...
**
Dear i better understand work and Concept of below mentioned
functionsi never denied to differencition between each
functions.
but at a time we can use a function for multiple purpose.
* *
*[vlookup, Index-Match, Offset-Match]* and *[Sumproduct, Sumifs, Dsum]*.
Thanks for your suggestion..
--
*Thanks regards,*
*Noorain Ansari*
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 11:11 PM, Ms-Exl-Learner .
ms.exl.lear...@gmail.com wrote:
Hi Noorain Ansari,
Case-1
I have attached the same file for your reference and I have not changed
anything on it just created a duplicate data.
Now look at the excel file and see what are all the results your formula
is deriving. Whether all the results are same? Take my previous mail and
read it once again which is elaborated clearly without the help of the above
attachment about the differences *[vlookup, Index-Match, Offset-Match]*
and *[Sumproduct, Sumifs, Dsum]*.
Case-II
I know sumproduct very well and the method how we are using the function
will differentiate the sumproduct from other functions. But you have used
the sumproduct for a single cell, which can be done using the simple IF
function and there is no need to go for sumproduct. I just wanted to
highlight it to you, and that was I did.
Never create and suggest formula's based on the articles you read in
internet or book, understand the concept how the functions are working and
it's concept then only you can able to give exact solution.
Let me know if you need any further clarification.
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 10:38 PM, NOORAIN ANSARI
noorain.ans...@gmail.com wrote:
Dear Ms-Exl-Learner,
Thanks for your valuable suggestion..
Case -I, In case of duplicay all formulas are successfull working except
vlookup(vlookup) example.
You can see fresh attachement..
Case -II, You can't compare Sumproduct with If function both are
different..
Correct Syntex of Formula Should be..
=SUMPRODUCT((C3:C8=G3)*(D3:D8=H3)*E3:E8) .instead of
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
another way :
*{=LOOKUP(2,1/(C3:C8=G3)*(D3:D8=H3),E3:E8)}*
Keep Enjoy, Cheers..
--
Thanks regards,
Noorain Ansari
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 9:51 PM, Ms-Exl-Learner .
ms.exl.lear...@gmail.com wrote:
Hi Noorain Ansari,
It might be better if you might have constructed your example data with
some duplicates, since the questioner can able to understand the difference
between the *[vlookup, Index-Match, Offset-Match]* and *[Sumproduct,
Sumifs, Dsum]*.
The *First set of formula's / Functions* just get the first match as
result and never consider the next matches. But the *Second
functions*consider all the matches and derive the result by adding the
values of the
all matches.
Please clarify me why the below formula should not be written
as =IF((F3=J3)*(G3=K3),H3,0) or =IF(AND((F3=J3),(G3=K3)),H3,0)???
=SUMPRODUCT((F3=J3)*(G3=K3)*H3)
You have provided the below formula to avoid the K3 cell criteria to be
automated by the formula
=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)
But,
It won't work fine when the K3 cell is having the 31-Aug as criteria and
the G5 cell have the 31-Aug.
---
Ms.Exl.Learner
---
On Fri, Sep 2, 2011 at 8:51 PM, NOORAIN ANSARI
noorain.ans...@gmail.com wrote:
Dear Haytham,
Please try below formula :
*=VLOOKUP(J3K3,$E$3:$H$3,4,0)*
*=VLOOKUP(VLOOKUP($J$3,$F$3:$G$8,2,0),$G$3:$H$8,2,0)*
other Alternative...
*=SUMPRODUCT((F3=J3)*(G3=K3)*H3)*
*{=INDEX($H$3:$H$8,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0))}*
*{=OFFSET($H$2,MATCH(1,($F$3:$F$8=$J$3)*($G$3:$G$8=$K$3),0),0)}
=SUMIFS(H3:H8,F3:F8,J3,G3:G8,K3)*
*=DSUM($F$2:$H$8,H2,J2:K3)*
See attached sheet..
--
Thanks regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.com/
On Fri, Sep 2, 2011 at 5:02 PM, Haytham Zoromba
haythamzoro...@gmail.com wrote:
Dear all,
I have tried to search about using vlookup with more than one
condition.
Is their any one know a formula for that?
BRegards,
Haytham Zoromba
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http
Re: $$Excel-Macros$$ Help on condition and return value
Try this… =IF(ISNUMBER(FIND( ,TRIM(B2))),LEFT(TRIM(B2),FIND( ,TRIM(B2))),IF(LEN(TRIM(B2))0,B2,)) --- Ms.Exl.Learner --- On Thu, Mar 24, 2011 at 4:20 AM, Jorge Marques leote.w...@gmail.com wrote: Hi guys, is there any way i can do to for example if a cell B2 has the following text phillips LCD 32 it returns philips to cell B1 but without extracting the character on the left, because if it gives me a OEM brand, i have to put the information corresponding by vendor and not brand. thx -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Value Required based on status updation in column
Hi Maulik, I am sure you will get it resolved if you explain your expectation with some more brief about the way of pulling the data with cell reference and how it can be derived. Because you know very well about your data and the way your expected result. But for us the data is a new one and we will be having some more interpretations about your actual query and we wont come to know your exact requirement until unless you explain it in a better and clear way. Hope that makes sense :) --- Ms.Exl.Learner --- On Wed, Feb 23, 2011 at 10:44 PM, maulik desai mauliksde...@gmail.comwrote: Hi ashish sir, thanks u for this ,but can u pls tell me how to use this because when i del any status and enter other in same cell it gives some error and basically i want that when ever any status updated in that column in database rest values should come automatically On 2/23/11, ashish koul koul.ash...@gmail.com wrote: see if that helps On Tue, Feb 22, 2011 at 11:07 PM, maulik desai mauliksde...@gmail.comwrote: In My excel database there are Huge database availble for this eg I have given the unique current status (which can be repeted many times ) what I want is when ever Below status updated in Current status Column Rest colums should give me the value based on status provided In that This the just small part of the database. For more infomation kindly ref the attachment -- Thanks Regards, Maulik Desai 9967363926 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Sent from my mobile device Thanks Regards, Maulik Desai 9967363926 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ One lookup and different corresponding values
Hi Bhushan, Sumproduct can be used when the result is Numeric Value. Have a look in the attached excel for retrieving the text output. --- Ms.Exl.Learner --- On Sat, Feb 5, 2011 at 12:09 PM, Manish pansari.man...@gmail.com wrote: Also Sumproduct is useful for numaric value. Is there any other formula, that will work as SUMPRODUCT for text?? Pls suggest. On Feb 5, 11:05 am, Manish pansari.man...@gmail.com wrote: Is it possible to hide the complete row based on lookup value?? For example in your solution, if i lookup value x than i want to hide row 14 because the lookup result is 0 /Blank. And if i change the value and look the value y i want to hide both row 13 and 14. Simply, I want automtion for hide blank rows. pls suggest the code OR upload the example file for reference. Thanks !! - Manish On Feb 2, 6:41 pm, ashish koul koul.ash...@gmail.com wrote: @bushan mail your file to id excel-macros@googlegroups.com and attach the file On Wed, Feb 2, 2011 at 4:42 PM, Bhushan bsabban...@gmail.com wrote: Hi, I am not finding any link from where i can upload my sample file. Regards. Bhushan On Feb 2, 2:11 pm, Manish pansari.man...@gmail.com wrote: Thanks for valuable post. Is it possible to hide the complete row based on lookup value?? For example in your solution, if i lookup value x than i want to hide row 14 because the lookup result is 0 /Blank. And if i change the value and look the value y i want to hide both row 13 and 14. pls suggest the code OR upload the example file for reference. Thanks !! - Manish On Feb 1, 11:23 pm, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Hi Bhushan, Have a look in the attached files. I never suggest array formula when the same can be done in normal way. HTH :) --- Ms.Exl.Learner --- On Tue, Feb 1, 2011 at 6:22 PM, Bhushan bsabban...@gmail.com wrote: Dear Ayush, Pls assist me for the below formula. I have a huge data from that I have given a sample below for your understanding. I have a data with lookup value with different corresponding values. When I am using the vlookup the vlookup is only taking the one correponding value of that lookup I am using the below formula for the diffrenet corresponding values the problem is when the lookup value changes the formula gives number error. I have manual change the row(4:4) in formula or row(3:3) anything to (1:1) in formula to get the corresponding values of another lookup. Name Item x 1 y 2 x 5 y 8 x 3 OPF No CPO Item Qty Formula x 1 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$7)),ROW(1:1)), 2) x 5 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$8)),ROW(2:2)), 2) x 3 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$9)),ROW(3:3)), 2) y =INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=A13,ROW($A$1:$A$6)),ROW(4:4)),2) there I have to change the row(4:4) to row(1:1) to get the corresponding value of the y lookup manual. Regards Bhushan Sabbani 98208 26012 -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Correction in ur Formula.xls 19KViewDownload My Solution.xls 18KViewDownload- Hide quoted text - - Show quoted text - -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish
Re: $$Excel-Macros$$ One lookup and different corresponding values
Hi Manish, You are welcome!!! and in my previous reply I wrongly mentioned the name as Bhushan instead of your name . --- Ms.Exl.Learner --- On Sun, Feb 6, 2011 at 2:38 PM, Manish pansari.man...@gmail.com wrote: Hey... Gr8... Thanks for reply... You all are just Wow !!! !! Regds, Manish On Feb 6, 2:01 pm, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Hi Bhushan, Sumproduct can be used when the result is Numeric Value. Have a look in the attached excel for retrieving the text output. --- Ms.Exl.Learner --- On Sat, Feb 5, 2011 at 12:09 PM, Manish pansari.man...@gmail.com wrote: Also Sumproduct is useful for numaric value. Is there any other formula, that will work as SUMPRODUCT for text?? Pls suggest. On Feb 5, 11:05 am, Manish pansari.man...@gmail.com wrote: Is it possible to hide the complete row based on lookup value?? For example in your solution, if i lookup value x than i want to hide row 14 because the lookup result is 0 /Blank. And if i change the value and look the value y i want to hide both row 13 and 14. Simply, I want automtion for hide blank rows. pls suggest the code OR upload the example file for reference. Thanks !! - Manish On Feb 2, 6:41 pm, ashish koul koul.ash...@gmail.com wrote: @bushan mail your file to id excel-macros@googlegroups.com and attach the file On Wed, Feb 2, 2011 at 4:42 PM, Bhushan bsabban...@gmail.com wrote: Hi, I am not finding any link from where i can upload my sample file. Regards. Bhushan On Feb 2, 2:11 pm, Manish pansari.man...@gmail.com wrote: Thanks for valuable post. Is it possible to hide the complete row based on lookup value?? For example in your solution, if i lookup value x than i want to hide row 14 because the lookup result is 0 /Blank. And if i change the value and look the value y i want to hide both row 13 and 14. pls suggest the code OR upload the example file for reference. Thanks !! - Manish On Feb 1, 11:23 pm, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote: Hi Bhushan, Have a look in the attached files. I never suggest array formula when the same can be done in normal way. HTH :) --- Ms.Exl.Learner --- On Tue, Feb 1, 2011 at 6:22 PM, Bhushan bsabban...@gmail.com wrote: Dear Ayush, Pls assist me for the below formula. I have a huge data from that I have given a sample below for your understanding. I have a data with lookup value with different corresponding values. When I am using the vlookup the vlookup is only taking the one correponding value of that lookup I am using the below formula for the diffrenet corresponding values the problem is when the lookup value changes the formula gives number error. I have manual change the row(4:4) in formula or row(3:3) anything to (1:1) in formula to get the corresponding values of another lookup. Name Item x 1 y 2 x 5 y 8 x 3 OPF No CPO Item Qty Formula x 1 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$7)),ROW(1:1)), 2) x 5 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$8)),ROW(2:2)), 2) x 3 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$9)),ROW(3:3)), 2) y =INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=A13,ROW($A$1:$A$6)),ROW(4:4)),2) there I have to change the row(4:4) to row(1:1) to get the corresponding value of the y lookup manual. Regards Bhushan Sabbani 98208 26012 -- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp:// exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Correction in ur Formula.xls 19KViewDownload My Solution.xls 18KViewDownload- Hide quoted text - - Show quoted text
Re: $$Excel-Macros$$ How to extract data from an array
Hi, Have a look in the attached file. HTH :) --- Ms.Exl.Learner --- On Tue, Feb 1, 2011 at 12:22 AM, jmccaski jmccask...@gmail.com wrote: I have an arry of over 21,000 rows with data in this format: VarName TimeString VarValue Pos 27.01.2011 12:49:35 32 Pres27.01.2011 12:49:35 -2 Setpt 27.01.2011 12:49:35 100 Gain27.01.2011 12:49:35 1 Int 27.01.2011 12:49:35 10 Pos 27.01.2011 12:49:36 32 Pres27.01.2011 12:49:36 -2 Setpt 27.01.2011 12:49:36 100 Pos 27.01.2011 12:49:37 32 Pres27.01.2011 12:49:37 9 Setpt 27.01.2011 12:49:37 100 Pos 27.01.2011 12:49:38 32 Pres27.01.2011 12:49:38 9 Setpt 27.01.2011 12:49:38 100 Pos 27.01.2011 12:49:39 32 Pres27.01.2011 12:49:39 24 Setpt 27.01.2011 12:49:39 100 Pos 27.01.2011 12:49:40 32 Pres27.01.2011 12:49:40 24 Setpt 27.01.2011 12:49:40 100 Gain27.01.2011 12:49:40 1 Int 27.01.2011 12:49:40 10 I'm trying to get it into a format something like this in order to chart it: TimeString Pos Press Setpt GainInt 27.01.2011 12:49:35 32 -2 100 1 10 27.01.2011 12:49:36 32 -2 100 27.01.2011 12:49:37 32 9 100 27.01.2011 12:49:38 32 9 100 27.01.2011 12:49:39 32 24 100 27.01.2011 12:49:40 32 24 100 1 10 Any help would be greatly appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Pivot Example.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ One lookup and different corresponding values
Hi Bhushan, Have a look in the attached files. I never suggest array formula when the same can be done in normal way. HTH :) --- Ms.Exl.Learner --- On Tue, Feb 1, 2011 at 6:22 PM, Bhushan bsabban...@gmail.com wrote: Dear Ayush, Pls assist me for the below formula. I have a huge data from that I have given a sample below for your understanding. I have a data with lookup value with different corresponding values. When I am using the vlookup the vlookup is only taking the one correponding value of that lookup I am using the below formula for the diffrenet corresponding values the problem is when the lookup value changes the formula gives number error. I have manual change the row(4:4) in formula or row(3:3) anything to (1:1) in formula to get the corresponding values of another lookup. Name Item x 1 y 2 x 5 y 8 x 3 OPF No CPO Item Qty Formula x 1 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$7)),ROW(1:1)), 2) x 5 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$8)),ROW(2:2)), 2) x 3 =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$9)),ROW(3:3)), 2) y =INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=A13,ROW($A$1:$A$6)),ROW(4:4)),2) there I have to change the row(4:4) to row(1:1) to get the corresponding value of the y lookup manual. Regards Bhushan Sabbani 98208 26012 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Correction in ur Formula.xls Description: MS-Excel spreadsheet My Solution.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ query for a solution
Hi Thamu, I don't know what is the need for the comparison of 2003 2007 excel column versions since the query is not very much related to the 2003 column issue. I agree that the column will be the issue in Excel 2003 when the repetition of UNIQUE IDNO goes beyond 127 times then we could not able to get the data of Bill No Amount in the continues columns because of the insufficient column space in Excel 2003, DescriptionRequired Columns Repetition Total Column UNIQUE IDNO -1 Column (Column A) 11 Bill No Amount -2 Columns127 (127 x 2 = 254) 254 -- Total 255 -- I hope the above data has given you the clear picture and have a look in the solution provided to the OP for the query. I request all of you don't take my SUGGESTIONS as CRITICISM I would like to add some more points to your post and make your knowledge get enriched. At the same time anyone find any error or anything better than my solution and I request you to keep me informed, so that I can also get learned from you. Thanks! --- Ms.Exl.Learner --- On Thu, Jan 27, 2011 at 4:59 PM, TG T viper@gmail.com wrote: Dear Ankur Pandey You cannot able to keep it in a single row If you have large amount of data. Because Excel 2003 has only 256 columns and excel 2007 has only 16384 columns. Thanks Regards Thamu On Thu, Jan 27, 2011 at 2:55 PM, ankur ankurpande...@gmail.com wrote: hi excel experts i need one help i need data in format like this unique id all bill no. coloumn wise all their respective amount all bill no. and their respective amount shoul come in single row. the data is attached with this mail..sheet 2 has format needed also urgent need -- Have A Nice Time Enjoy Life Regards: CMA Ankur Pandey (Someone Different) I'm not the best but i'm not like the rest~~ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- --- Ms.Exl.Learner --- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Formula
Hi John, It will work in version 2003 if you refer the cell reference to part of the column range instead of referring it to the whole column. =LOOKUP(2,FIND(A1,Sheet2!A1:A65535),Sheet2!B1:B65535) See the attached file for detail. --- Ms.Exl.Learner --- On Sat, Jan 15, 2011 at 12:59 AM, John A. Smith johnasmit...@gmail.comwrote: Dave, Why am I getting #NUM! with the formula? (See Attached) Thank you. John On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, I would like to submit the following formula as one I like. It performs a case-sensitive Vlookup, and is non-array. *=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B)* A1 contains the lookup value Sheet2!A:A is the lookup column Sheet2!B:B is the return column I hasten to add that this formula is not my own, but is a very neat solution to a common problem. Regards - Dave. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Daves Lookup Formula-1-Revision.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Formula of the week - share your best formula
Hi All, I suggest all of you don't say this function I like and then this one like that. Just sharing the function name won't help others to gain some knowledge. All the functions which is available in excel is having the same set of qualities unless it is used in perfect combination. So give brief about the function and its different type of usages which you came across in your past. Hope this makes sense. --- Ms.Exl.Learner --- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Excel Daily Tip : Convert month in text to number
I hope you need to go through each and every post before using / declaring the below words to the group! --- Ms.Exl.Learner --- On Sat, Jan 8, 2011 at 3:28 PM, Ayush Jain jainayus...@gmail.com wrote: Ayush Jain has sent you a link to a blog: Dear Group, I have published a new tip to convert month in text to number. This is the result of latest puzzle posted in group. The best solution was provided by Manoj Kumar. Best Regards, Ayush Jain Blog: Excel Daily Tip Post: Convert month in text to number Link: http://exceldailytip.blogspot.com/2011/01/convert-month-in-text-to-number.html -- Powered by Blogger http://www.blogger.com/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi All, Have a look in the attached excel and you can find the solution. The formula is given below for your reference. Adapt the Cell Range A3:A8 to your desired Range. =IF(COUNTIF(A3:A8,LARGE(A3:A8,1))=COUNTIF(A3:A8,MODE(A3:A8)),LARGE(A3:A8,1),MODE(A3:A8)) *Suggestions For All* * * 1) Always try to avoid the usage of the helper column(s). 2) Never go for array formula when the same can be achieved by normal one. 3) Please don't go for VBA (Macro) solution for simple things when excel holds lot of functions to do the work as expected (Like the above). --- Ms.Exl.Learner --- On Thu, Jan 6, 2011 at 6:17 AM, Kim anast...@gmail.com wrote: Hi, I have a list of numbers and I need to find the highest of the most frequent occurrances. For example: List 1 1 3 3 4 4 Should return the value 4 because its the highest of the most frequent occurrances of any number. List 2 1 3 3 3 4 4 Should return 3 because it's the most frequent occurrance outright. I've tried using the mode command but it returns the first of the most frequent occurances but I want the highest of the most frequent occurrances. Any help would be greatly appreciated. Thanks Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Solution (9-Jan-2011).xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Nested IF functions?
Yes it's very clear now. Try any one of the below formula which will do the trick. =IF(ISNUMBER(A3),IF(A31000,2.5,IF(A3=500,3,IF(A3500,3.5,))),) OR =IF(ISNUMBER(A3),IF(A3500,3.5,IF(AND(A3=500,A3=1000),3,IF(A31000,2.5,))),) At the same time herewith I have attached a sample file for your reference. --- Ms.Exl.Learner --- On Wed, Dec 29, 2010 at 11:13 AM, J D springer...@gmail.com wrote: Thanks. I have the Syntax correct now but how do I end it. I want to write a formula that would return the right answer for the below. if A3 500 value will be 3.5 if 500A31000 then value will be 3 if A31000 then value will be 2.5 I entered =IF(A3500,3.5,IF(500A31000,3,IF(A31000,2.5 and got False for an answer. I want to type into A3 a s.f. number and have the formula kick out one of the 3 or more possibilities so I can use the correct value in the next equation. Hope this is clearer. Thanks On Dec 28, 4:29 am, hanumant shinde arsfan2...@yahoo.co.in wrote: i dont understand what u mean exactly but i will jus tell u abt nested if in excel. let me know if you wanna know nesed if for Macro. it is if else function. so write if and give some value if is its true and in place of else write another if. if you wanna know the syntax refer help in excel. just type in IF and you will get everything there. e.g. =IF(A3=3,Equal,IF(A33,Less,IF(A33,Greater))) explaination if A3 = 3 value wil be Equal if A33 then value will be Less if A33 then value will be Greater - Original Message From: J D springer...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, 28 December, 2010 7:36:57 AM Subject: $$Excel-Macros$$ Nested IF functions? I am a concrete estimator who is trying to create some cut and paste Estimate line items I can just grab from one spread sheet and paste in to my estimate. I was refining one and tried to use the IF function to pick the number of labor hours based on sq footage. More s.f. and we are more productive therefore less hours per s.f. I was trying to say if less that 500 s.f. then .035 hrs/s.f. 500 to 1,000 then .0275 hrs/s.f. 1,000 s.f. then .0225 hours/s.f. I could get one working but wanted to know how to get more going choises out of one formula. Attached is the assembly and the formula below it. Form, place and finish ~ (__) s.f. of 4 broom swept finish concrete sidewalk. Concrete to be 3,500 psi and reinforced with 6 x 6 , w1.4/ w1.4 wwf. $4,410.00 Area990 s.f.Perimeter114 l.f. Concrete9900.33 1.03336.50112.463 13145 1885 Labor 130.45.2 5.240 208 wwf99040 24.75 257175 2 SB 9900.26257.4 260 0.49127.4 Labor990 0.0032.97 340 120 Poly9901800 0.55 0.68551 Labor 9900.0021.98 240 80 Expansion9900.25 247.51002.475 2.531.64 79.1 Labor 2500.012.5 2.540 100 Curing640350 1.82857142950.365714286 0.455 22 Labor 9900.0032.97 340 120 2 x 411416 7.125 85.8146.48 Labor 1140.3337.62 0.27.5247.6 40304 Finish990 1009.92.75 27.22527.340 1092 4409.98 =IF(B16500,3.5,2.75) -- -- - Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall...-Hide quoted text - - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to
Re: $$Excel-Macros$$ Formula required to find the highest lowest values in each month
Hi, In addition to the above post I would like to remove the helper column and we make the formula to do the work. Have a look in the attached file. --- Ms.Exl.Learner --- On Sun, Dec 26, 2010 at 6:29 PM, ashish koul koul.ash...@gmail.com wrote: check the attachment On Sun, Dec 26, 2010 at 3:15 PM, Rajesh K R rajeshkainikk...@gmail.comwrote: Hi experts Wish you happy new year I have worksheet like this Column A - Date Column B - Amt Column C - Name of Month Column D - For highest value of each month Column E - For lowest value of each month Pls check the attached file tell me what is the formula for finding the highest lowest value. Regards Rajesh Kainikkara -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts higest__lowest-Avoid helper Column.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Help on Advance Formula
Hi Chandra Shekar, Have a look in the attached file. Hope it's clear to you and this is what you are looking for. --- Ms.Exl.Learner --- On Fri, Dec 24, 2010 at 8:09 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hello Team, How to get unique value based on two columns in the attached file Regards, Chandra Shekar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Unique Value - Solution File.xls Description: MS-Excel spreadsheet
Fwd: $$Excel-Macros$$ Help on Advance Formula
I have given this solution in the morning itself but the moderator of this group is not allowing me to post my replies and my replies always getting moderated and due to this most of my messages are getting reflected to the OP after a day time. It will be helpful if the moderator take some action for this. --- Ms.Exl.Learner --- -- Forwarded message -- From: Ms-Exl-Learner . ms.exl.lear...@gmail.com Date: Sat, Dec 25, 2010 at 8:39 AM Subject: Re: $$Excel-Macros$$ Help on Advance Formula To: excel-macros@googlegroups.com Hi Chandra Shekar, Have a look in the attached file. Hope it's clear to you and this is what you are looking for. --- Ms.Exl.Learner --- On Fri, Dec 24, 2010 at 8:09 PM, Chandra Shekar chandrashekarb@gmail.com wrote: Hello Team, How to get unique value based on two columns in the attached file Regards, Chandra Shekar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Unique Value - Solution File.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Need to be able to find duplicates before a space
In addition to the below the cursor (Activecell) should be in B2 cell. --- Ms.Exl.Learner --- On 12/23/10, siti Vi villager.g...@gmail.com wrote: select the range of your Numbers for example: B2:B200 you can apply a conditional formatting on that range the condition FORMULA is =COUNTIF($B$2:$B$200;B2)1 On Thu, Dec 23, 2010 at 2:04 AM, Superkermit jasonmr...@gmail.com wrote: I have a excel spreadsheet that has model numbers on the start of a column and then a space and a description, what I need to do is be able to have excel find all the duplicate model numbers and highlight them, how should I go about doing this? -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ help required for large range of data filteration
Excel 2003:- You can see only 1000 records in the Autofilter Dropdown. Not more than that. Excel 2007/2010:- You can see 1 records in the Autofilter Dropdown. Not more than that. So try to use some helper column based on your data criteria use some formula in helper column like Istext, Isnumber, Isblank, which will make the output as boolean and the result will be True or False and if you apply the auto filter for that then your drop down will show only 2 records True / False and you will be having another (Excel 2003-998 in your hand to show in Autofilter Dropdown) (Excel 2007/2010-9998 in your hand to show in Autofilter Dropdown) and you can try len function also. So it's purely based on the data and your requirement and how you use the functions. Hope it's clear and helpful to you! --- Ms.Exl.Learner --- On Wed, Dec 22, 2010 at 8:58 PM, renuka chari jva.ch...@gmail.com wrote: hi good evening to all experts currentely i am trubling in large range data with filtaration my company data having nearly 4lac of rows in one file i need to sort that data using auto filter Option then it was not showing all filterd data i don't know how to filter that large rang of data can u help me any one if possible give me example thanks in advance -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ problem in sumif
Hi Rajesh,
You are welcome!
---
Ms.Exl.Learner
---
On Sat, Dec 18, 2010 at 4:56 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
hi
Thanks for ur answer, i checked the formula , it is working well
Regards Thanks
Rajesh Kainikkara
On 12/18/10, Ms-Exl-Learner . ms.exl.lear...@gmail.com wrote:
Copy and paste the below formula:-
=SUMIF($R$8:$R$96,$A12,($X$8:$X$96))
Drag the formula cell below to the remaining cells and the criteria cell
will be getting changed and the Range and Sum Range will remains same.
But past the above formula other than R8:R96, X8:X96 range and A12 cell.
Otherwise you will get circular reference error.
Hope it's clear to you!
---
Ms.Exl.Learner
---
On Thu, Dec 16, 2010 at 4:08 PM, Rajesh K R
rajeshkainikk...@gmail.comwrote:
hi
Thanks 4 ur reply,it was a sample what i required, in the actual data
its around 300 items in it
the data will never in the order.Formula given under is giving a
correct answer in my actual database, but it have auto filling problem
=SUMIF(R8:R96,a12,(X8:X96))
=SUMIF(R8:R96,a13,(X8:X96))
=SUMIF(R8:R96,a14,(X8:X96))
i want fill down the formula in this order how can i do, in this only
criteria have to change, but the range is same for all cells
Regards
Rajesh kainikkara
On 12/15/10, Daniel dcolarde...@free.fr wrote:
Hi,
1. Formula should be (in H3 :
=SUMIF('STOCK SUMMERY'!A3:A5,REPORT!A3,DAMAGE!B2:B5)
2. The drinks in column A should be in the same order on each sheet.
Regards.
Daniel
-Message d'origine-
De : excel-macros@googlegroups.com
[mailto:excel-mac...@googlegroups.com]
De
la part de Rajesh K R
Envoyé : mercredi 15 décembre 2010 13:02
À : excel-macros
Objet : $$Excel-Macros$$ problem in sumif
Hi Experts
Pls find the attachment , tell me whats wrong in the formula
STOCK SUMMERY - 1 st sheet
OPENING PURCHASESALEBALANCE
COLA 1000 250 700 550
SODA500 100 300 300
WATER 400150 100 450
DAMAGE STOCK - 2 nd sheet
SODA 20
COLA 5
WATER 4
STOCK SUMMERY ADJUSTED- 3 rd sheet
OPENING PURCHASESALEGOODDAMAGE
BALANCE
SODA 1000250 700 530
20550
COLA 500 100 300 295
5 300
WATER 400 150 100 446
4 450
SUMIF('STOCK SUMMERY'!A3:A5,REPORT!A3:A5,(DAMAGE!B2:B5))
I tried the above formula in the column damage in 3 rd sheet , but it
shown
only wrong values, kindly tell me how correct the formula.
Regards
Rajesh kainikkara
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip 2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips
and
Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Missing Entries
Hi Vebhav, Have a look in the attached file. Hope it helps! --- Ms.Exl.Learner --- On Thu, Dec 16, 2010 at 11:06 PM, vebhav jain vebhav.j...@gmail.com wrote: Hi All, Please help me in finding the missing entries from the below data. ListAListB 1 1 2 3 3 5 4 5 Regards Vebhav Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts Find Missing Entries.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ problem in sumif
Copy and paste the below formula:-
=SUMIF($R$8:$R$96,$A12,($X$8:$X$96))
Drag the formula cell below to the remaining cells and the criteria cell
will be getting changed and the Range and Sum Range will remains same.
But past the above formula other than R8:R96, X8:X96 range and A12 cell.
Otherwise you will get circular reference error.
Hope it's clear to you!
---
Ms.Exl.Learner
---
On Thu, Dec 16, 2010 at 4:08 PM, Rajesh K R rajeshkainikk...@gmail.comwrote:
hi
Thanks 4 ur reply,it was a sample what i required, in the actual data
its around 300 items in it
the data will never in the order.Formula given under is giving a
correct answer in my actual database, but it have auto filling problem
=SUMIF(R8:R96,a12,(X8:X96))
=SUMIF(R8:R96,a13,(X8:X96))
=SUMIF(R8:R96,a14,(X8:X96))
i want fill down the formula in this order how can i do, in this only
criteria have to change, but the range is same for all cells
Regards
Rajesh kainikkara
On 12/15/10, Daniel dcolarde...@free.fr wrote:
Hi,
1. Formula should be (in H3 :
=SUMIF('STOCK SUMMERY'!A3:A5,REPORT!A3,DAMAGE!B2:B5)
2. The drinks in column A should be in the same order on each sheet.
Regards.
Daniel
-Message d'origine-
De : excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com]
De
la part de Rajesh K R
Envoyé : mercredi 15 décembre 2010 13:02
À : excel-macros
Objet : $$Excel-Macros$$ problem in sumif
Hi Experts
Pls find the attachment , tell me whats wrong in the formula
STOCK SUMMERY - 1 st sheet
OPENING PURCHASESALEBALANCE
COLA 1000 250 700 550
SODA500 100 300 300
WATER 400150 100 450
DAMAGE STOCK - 2 nd sheet
SODA 20
COLA 5
WATER 4
STOCK SUMMERY ADJUSTED- 3 rd sheet
OPENING PURCHASESALEGOODDAMAGE
BALANCE
SODA 1000250 700 530
20550
COLA 500 100 300 295
5 300
WATER 400 150 100 446
4 450
SUMIF('STOCK SUMMERY'!A3:A5,REPORT!A3:A5,(DAMAGE!B2:B5))
I tried the above formula in the column damage in 3 rd sheet , but it
shown
only wrong values, kindly tell me how correct the formula.
Regards
Rajesh kainikkara
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip 2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips
and
Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
--
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to
Re: $$Excel-Macros$$ Re: Reqd : Add in to convert Number to text in Excel 2007
You can use substitute in front of the UDF to replace the word to your desired local currency right. Another Method:- Follow the below link and download the MoreFuction Addin from that and run the setup. http://download.cnet.com/Morefunc/3000-2077_4-10423159.html After doing that open excel and goto ToolsAdd-InsCheck the Morefunc(add-in Function)Ok Then go to InsertFunctionsSelect a CategorySelect MorefunSelect a Funcion:Select NBTEXTOk You can find more functions in that addin. --- Ms.Exl.Learner --- On Tue, Dec 7, 2010 at 8:15 PM, Radhe Sham radhe2...@gmail.com wrote: HI Ashish , Sorry to trouble you again Spell Numbers convers the number into words in the same location while what is requires is the numbers are to be translated into words in a different location. I have attached a file with the format in which the numbers are requires to be mentioned in words Also, Spell Numbers converts the values in Dollars and Cents which probably would not help in India as we would require the amount in Local currency Thanks for the personal support being extended Best regards Radhe On Tue, Dec 7, 2010 at 6:51 PM, OpenExcel.com ashishj...@openexcel.comwrote: Hi Radhe, 1. Download Open Excel from http://www.openexcel.com 2. Install it 3. Open your Workbook. 4. Go to 'Open XL' tab. 5. Go to 'Number Utils' 6. Click on 'Spell Numbers' and you're done. It does exactly what you mentioned and have around 100 more useful utilities, you probably would be using daily. Check it out ! Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Dec 7, 5:22 pm, Radhe Sham radhe2...@gmail.com wrote: Hi Ashish, Many thanks for the kind help in providing valuable information about Openexcel. Probably the communication from my side was not clear The help i was seeking was to convert the value to be mentioned in words For example if a cell has 523.25; this value should be displayed in words as Rupees Five Hundred Twenty Three and Paise Twenty Five Only Earlier a group member has provided an addin Num2text.xla which helped in getting the desired result. This addin is not working in Excel 2007 Looking forward to your kind help please Many thanks and best regards Radhe On Tue, Dec 7, 2010 at 3:57 PM, OpenExcel.com ashishj...@openexcel.com wrote: Hi Radhe, It seems you don't know about 'Open Excel'. Download and you can do many things other than this. Thanks Regards Ashish Jain (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com On Dec 7, 2:13 pm, Radhe Sham radhe2...@gmail.com wrote: Hi Group , The addin for converting a number to text which was working in Excel 2003 does not seem to work in Excel 2007 Can anyone kindly help with an add in for Excel 2007 for converting number to text please Thanks and regards Radhe -- --- --- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall;. .. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at
Re: $$Excel-Macros$$ Re: Ctrl + Tilde
Hi Sreedhar, Check whether the buttons are working properly (Cntrl Tilde buttons) --- Ms.Exl.Learner --- On Fri, Dec 3, 2010 at 10:28 PM, Shreedar Pandurangaiah shreedar.panduranga...@gmail.com wrote: Any help on this would really great. Thank You, Shreedar On Thu, Dec 2, 2010 at 7:01 PM, Shreedar Pandurangaiah shreedar.panduranga...@gmail.com wrote: Hi Group, While I am able to use the Ctrl + Tilde, to see the hidden formulas in the spreadsheet. My colleague is not able to do that...I mean to say can't use the Ctrl + Tilde. What should I do to enable that? Thank You, Shreedar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
Re: $$Excel-Macros$$ Zooming of Excel Sheet.
Check whether your cntrl button is working. Ms-Exl-Learner On Mon, Nov 22, 2010 at 10:52 AM, C.G.Kumar kumar.bemlmum...@gmail.comwrote: Dear All, When i am using scroll wheel of mouse my excel sheet gets Zoom In (Moving Forward) Zoom out ( (Moving Backward). Earlier this function worked when i press ctrl+scroll of mouse. I prefer earlier control and intend to scroll with my mouse the excel sheet instead of ZOOM option. Can any body tell me how to rectify. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts -- --- Ms.Exl.Learner --- -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
