$$Excel-Macros$$ Re: Projects Pipeline sheet updation
OK Option Explicit Sub getallSAS() Dim i As Long Dim dlr As Long Dim cr As Double Dim slr As Long Sheets(all up).Select Application.ScreenUpdating = False ActiveSheet.UsedRange.Offset(1).Clear Columns.ColumnWidth = 2 Rows.RowHeight = 4 On Error Resume Next For i = 2 To Sheets.Count With Sheets(i) slr = .Cells.SpecialCells(xlLastCell).Row dlr = Cells(Rows.Count, 2).End(xlUp).Row + 1 cr = Application.Match(contact, .Columns(1), 0) .UsedRange.Offset(cr).Copy Cells(dlr, 2) Cells(dlr, 1).Resize(slr).Value = .Name End With Next i Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Application.ScreenUpdating = True Columns.ColumnWidth = 200 Rows.RowHeight = 40 Columns.AutoFit Rows.AutoFit End Sub On Monday, October 29, 2012 6:22:14 AM UTC-5, Pooja S wrote: Hi Team, Attached is the sheet where in i need some formula to get the below result. 1.) Create links so that if data is entered under an individual tab(eg for Steve, Srivats, Anders, etc.) it is reflected in the All Up tab. We want the All up tab to be the one place where you, me and anyone who needs the info can always go to see where our all up bus dev pipeline stands. 2.)Once links are created, can someone please ensure that all of the data from the individual tabs is populated in the All Up tab. 3.)Create a pivot/drop down for each of the column heads w/associated data(eg 20%, 40%, 60%, 80%, 100% for probability). -- 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. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ 'Useless subject' was: Need Macro or formula to work on attached file
agree!!! On Monday, October 29, 2012 11:36:55 PM UTC-5, David Grugeon wrote: I am not going to spend my time downloading your file untill I know what it is you want. Please do us the courtesy of copying the steps from the file and putting them in your post. Also put a more relevant subject heading. Regards David Grugeon On 30 October 2012 03:15, ankur kochar ankurk...@gmail.com javascript: wrote: Hi All, Can someone help me on the attached file to get the desired result. I am very much required the solution and i am sure the expert in this group can help me to get my solution... Thanks for all your help in advance. -- Forwarded message -- From: ankur kochar ankurk...@gmail.com javascript: Date: Mon, Oct 29, 2012 at 2:17 PM Subject: $$Excel-Macros$$ Need Macro or formula to work on attached file To: excel-...@googlegroups.com javascript: Hi All, I need your expert help to create a macro or formula to exectute the following steps as mentioned in attached excel file. I need the result in output tab with the desired result as mentioned in the file. Let me know if any additional information or clarification required to explain my need. Thanks for all your help in advance. -- Regards, Ankur -- 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-...@googlegroups.comjavascript: . 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. -- Regards, Ankur -- 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-...@googlegroups.comjavascript: . 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. -- 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$$ Re: Projects Pipeline sheet updation
Why not make your life easier and use ONE sheet for all wtih an extra column for the name and then use filter to see the individuals as desired On Monday, October 29, 2012 6:22:14 AM UTC-5, Pooja S wrote: Hi Team, Attached is the sheet where in i need some formula to get the below result. 1.) Create links so that if data is entered under an individual tab(eg for Steve, Srivats, Anders, etc.) it is reflected in the All Up tab. We want the All up tab to be the one place where you, me and anyone who needs the info can always go to see where our all up bus dev pipeline stands. 2.)Once links are created, can someone please ensure that all of the data from the individual tabs is populated in the All Up tab. 3.)Create a pivot/drop down for each of the column heads w/associated data(eg 20%, 40%, 60%, 80%, 100% for probability). -- 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. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Projects Pipeline sheet updation
Should do it in .xlsM workbook. Sub getallSAS() Dim i As Long Dim cr As Double Application.ScreenUpdating = False ActiveSheet.UsedRange.Offset(1).Clear On Error Resume Next For i = 2 To Sheets.Count With Sheets(i) cr = Application.Match(contact, .Columns(1), 0) .UsedRange.Offset(cr).Copy Cells(Rows.Count, 1).End(xlUp)(2) End With Next i Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Columns.ColumnWidth = 60 Columns.AutoFit Application.ScreenUpdating = True End Sub On Monday, October 29, 2012 6:22:14 AM UTC-5, Pooja S wrote: Hi Team, Attached is the sheet where in i need some formula to get the below result. 1.) Create links so that if data is entered under an individual tab(eg for Steve, Srivats, Anders, etc.) it is reflected in the All Up tab. We want the All up tab to be the one place where you, me and anyone who needs the info can always go to see where our all up bus dev pipeline stands. 2.)Once links are created, can someone please ensure that all of the data from the individual tabs is populated in the All Up tab. 3.)Create a pivot/drop down for each of the column heads w/associated data(eg 20%, 40%, 60%, 80%, 100% for probability). -- 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. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Macro for Find
You would want to use some autofilter or advanced filter with a series of input boxes or a user form. On Saturday, October 27, 2012 3:14:51 AM UTC-5, shrini wrote: Dear all I WANT TO WRITE A MACRO WHICH WILL ASK USER TO DEFINE HIS SEARCH Example .-: user want to find details of H.then macro should ask the name and only that details will be paste in sheet 2 Other search may be user want details of all the people whose age is in between 23 to 27 -- 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. 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.
$$Excel-Macros$$ Re: Need Help in allocation table
Always show a before/after and the logic On Thursday, October 4, 2012 1:33:22 AM UTC-5, Vishwanath wrote: Need help Dear All, I need your help in automatic some calculations in spreadsheet. I have data in excel as below; It consists of 2 parts. first part is Expense statement and second part is allocation required. The expense statement has data period for each type of expenditure. The same needs to be allocated monthwise columns on prorata basis. I am not familiar with Macro. Is there any formulat which can help the allocation? The file containing data is attached. Thanks in advance for help. Data Available Allocation required Satement of Allocation of expenses - MonthwiseItem Period Amount Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12From ToExpenditure A 1-Apr-12 30-May-12 15000 7500 7500 Expenditure B 8-Apr-12 7-Jun-12 1 3,667 5,000 333Expenditure C 15-May-12 10-Sep-12 5 0 6,780 12,712 12,712 12,712 5,085 Total' 75000 11167 19280 14045 12712 12712 5085 -- 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.
$$Excel-Macros$$ Re: Macro to allow copy paste in Drop-Down list with validation intact
First. A worksheet event macro must be in the SHEET module (or ThisWorkbook for all shts). Try this simpler approach using all vba . '=== option explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim mf As Range If Target.Column 1 Then Exit Sub 'If Target.Address Range(c1).Address Then Exit Sub' NOT used Set mf = Range(a1:a4).Find(What:=Target, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If mf Is Nothing Then Application.EnableEvents = False Target = Application.EnableEvents = True MsgBox Invalid Entry End If End Sub '=== On Friday, October 5, 2012 7:17:02 AM UTC-5, Sourabh wrote: Hi, I am using Excel 2003 and have drop down cells with validations. I want to restrict the copy-paste functionality for these drop-down cells. i have written a macro for that purpose but it disables the copy-paste for entire worksheet. Appreciate if you could help me with a macro that checks for the values in the drop-down list and if the value matches with that the user is going to paste then it allows to paste else it gives an error message Not a valid entry For example in the attachment(See attached file) there are 4 entries in the drop-down list. If the user tries to paste any random value say abcd or 1234 then an error message should be shown and the user must not be able to paste BUT if user pastes a value(Unclassified or any of the 4 items) that matches the drop-down list values the paste should be allowed. Can you please help me with this, I have written a macro but its not working can you please check or advice what am i missing or doing wrong. I am attaching the macro enable Excel sheet for your reference so that you can have look at the macro that i have written. Thanks Sourabh -- 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.
$$Excel-Macros$$ Re: Help on Macro
Provide a file with this to dguillett1 @gmail.com 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 -- 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. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Help on Macro
I have re read your post several times and looked at your file and I have NO idea what you want. 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 -- 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. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Request for support for a looping macro
Methinks you are making this much more difficult than necessary. Either fully explain what you want with before/after example If all else fails, send file to dguillett1 @gmail.com On Tuesday, October 2, 2012 4:33:57 PM UTC-5, Diamond Dave wrote: Attached is a file which contains a Macro (Shift Data). There is a variable number of data sets (all the same configuration) defined in Cell A1 with the formula A1=Counta(B:B)-2 which defines the actual number of data sets. The macro (below) defines what needs to be done to the first and Second data set 'Data Set 1 Range(E4).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut ActiveCell.Offset(0, -1).Select ActiveSheet.Paste ActiveCell.Offset(3, 0).Select Selection.EntireRow.Insert ActiveCell.FormulaR1C1 = variance ActiveCell.Offset(4, 1).Select 'Data Set 2 Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut ActiveCell.Offset(0, -1).Select ActiveSheet.Paste ActiveCell.Offset(3, 0).Select Selection.EntireRow.Insert ActiveCell.FormulaR1C1 = variance ActiveCell.Offset(4, 1).Select How would I write a loop which will perform the function for “Data SET 2” the number of times shown in cell A1? Your Support is greatly appreciated David Pehrson -- 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.
$$Excel-Macros$$ Re: Query
I LOOKED at the provided file and gave proper answers including the homework question about sumproduct. On Friday, September 28, 2012 7:24:58 AM UTC-5, Aamir Shahzad wrote: Dear Group, When I mannualy enter the C7 into the following formula, it has no problem, =SUMPRODUCT(($H$2:$H$9=$H13)*(*C7*)) but when I am trying to generate C7, it's given me #value error, however address function is creating the C7 ok seperaterly. =SUMPRODUCT(($H$2:$H$9=$H12)*(ADDRESS(MATCH($H12,$H$2:$H$9,0)+1,COLUMN(C2 Sample file is also attached, Please suggest. Regards, Aamir Shahzad -- 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.
$$Excel-Macros$$ Re: Query
what is wrong with what I posted much earlier INDEX(C:C,MATCH($H12,$H:$H)) instead of this =INDEX(C2,MATCH($H12,$H$2:$H$9,0)) if you insist on sumproduct, this will do just fine. =SUMPRODUCT(($H$2:$H$9=$H12)*C$2:C$9) On Friday, September 28, 2012 7:24:58 AM UTC-5, Aamir Shahzad wrote: Dear Group, When I mannualy enter the C7 into the following formula, it has no problem, =SUMPRODUCT(($H$2:$H$9=$H13)*(*C7*)) but when I am trying to generate C7, it's given me #value error, however address function is creating the C7 ok seperaterly. =SUMPRODUCT(($H$2:$H$9=$H12)*(ADDRESS(MATCH($H12,$H$2:$H$9,0)+1,COLUMN(C2 Sample file is also attached, Please suggest. Regards, Aamir Shahzad -- 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.
$$Excel-Macros$$ Re: Help on Macro
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 -- 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. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Query
Why bother when I gave you the PROPER formula. On Friday, September 28, 2012 7:24:58 AM UTC-5, Aamir Shahzad wrote: Dear Group, When I mannualy enter the C7 into the following formula, it has no problem, =SUMPRODUCT(($H$2:$H$9=$H13)*(*C7*)) but when I am trying to generate C7, it's given me #value error, however address function is creating the C7 ok seperaterly. =SUMPRODUCT(($H$2:$H$9=$H12)*(ADDRESS(MATCH($H12,$H$2:$H$9,0)+1,COLUMN(C2 Sample file is also attached, Please suggest. Regards, Aamir Shahzad -- 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.
$$Excel-Macros$$ Re: selecting text and marking with status against it
I don't quite understand your need. Please explain excluded. give examples On Friday, September 21, 2012 10:37:34 AM UTC-5, rekha siri wrote: hi experts, please help me in this concern, i have data spread from A to AB columns from one of the column i want to apply filter select one region in column e against that region i want to place as excluded in another column AS. can any one help me with vba code in this. i am attaching sample excel sheet. Thanks, Rekha -- 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.
$$Excel-Macros$$ Re: selecting text and marking with status against it
Your sample does not result in a hit in col M for roy or rod. On Friday, September 21, 2012 10:37:34 AM UTC-5, rekha siri wrote: hi experts, please help me in this concern, i have data spread from A to AB columns from one of the column i want to apply filter select one region in column e against that region i want to place as excluded in another column AS. can any one help me with vba code in this. i am attaching sample excel sheet. Thanks, Rekha -- 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$$ Re: Help with Formula
You can use this part of the formula for the simple vlookup COLUMN'=MATCH(TEXT($J$3,MM/yy),'2011 Actual'!$A$1:$X$1,0) On Aug 20, 6:43 am, Steve Weaver steveweave...@comcast.net wrote: Sam, thank you so much . . . the formula works GREAT!!! Steve On 8/20/2011 1:05 AM, GoldenLance wrote: Use =INDIRECT('2011 Forecast'!ADDRESS(28,MATCH(TEXT(J3,),'2011 Forecast'!$A$2:$X $2,0))) in Monthly IS, range F9 Steve, sure you can replicate this for all your cells. Regards, Sam Mathai Chacko (GL) On Aug 19, 9:53 pm, Steve Weaversteveweave...@comcast.net wrote: Hi, Can some one help me to create a formula in excel. Using the attached file, please refer to the Monthly IS tab, column D. I need help writing a formula that will: 1. retrieve the appropriate value from the 2011 Actual tab based on the month shown in Monthly IS tab, cell J3. 2. retrieve the appropriate value from the 2011 Forecast tab based on the month shown in Monthly IS tab, cell J3. Thank you for your help. Steve IS Example.XLS 640KViewDownload- 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 excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel