$$Excel-Macros$$ Re: Indirect Function Not Working on Dates Please Help
Hello Dileep, In Summary!B5 =SUMIF(INDIRECT(TEXT($A5,'dd-mm-')!C:C),B$3,INDIRECT(TEXT($A5,'dd-mm-')!F:F)) Then copy down other Amount columns. Haseeb On Mon, Jul 21, 2014 at 2:42 PM, Dileep Kumar deepuk...@gmail.com wrote: Dear Friends, Please find attached Daily Reporting Formate of Excel in the summary sheet I am unable to get the vales using indirect function. Please help me friends. -- Regards, Dileep Kumar Kaza -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! 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 unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Help to find position of a particular number in a given data set
Hello Atul, try this Assume B2:B11 is %, in C2 then copy down. =SUMPRODUCT((ISNUMBER(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,RANK(B2,B$2:B$11))),INDEX(RANK(B$2:B$11,B$2:B$11),0),0))+0)) ___ HTH, Haseeb On Wednesday, March 21, 2012 12:24:21 AM UTC+3, Atul wrote: Hi, I need help to find position of a particular number in a given data set. Please refer to the table below. *Fin. Inst.* *Rates in %* A 10 B 11 C 12 D 11 E 6 F 9 G 8 H 8 I 9 J 7 In the above example, Rates are given for each financial institution. In descending order,the rate 12% is at the 1st position,11% is 2nd highest and so on. While using function LARGE or SMALL, we need to assign kth position to get the required result. I need to find (kth) position of given rate from the list, ignoring the duplicate items. Going back to the above example, 11% will be the second highest rate, 10% being the third highest rate and so on. Please let me know the function or combination of functions. Thank you for your help and time. Regards, *Atul Patel* H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! *Atul Patel* H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! -- 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
Re: $$Excel-Macros$$ Sumproduct accross multiple sheets
Hi, Assume the sheet name always be in dd-mmm-yy format. I have define a name CurrPeriod, you need to define it on your file. Just change Start End Date reference in this formula. I also change some Locations for SALES in multiple sheets to make sure it works :-) See the attached. ___ HTH, Haseeb -- 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 Sumproduct_Across_multiple_sheets.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: indirect function help
If you have space in the sheet name will require an apostrophe before 7
after the sheet name. When ever you use INDIRECT use the apostrophe. excel
will ignore, If doesn't require it. So use like this,
*=SUM(INDIRECT('A6'!G:G))*
___
HTH, Haseeb
--
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
$$Excel-Macros$$ Re: calculate from multiple sheets calculate from multiple sheets with conditions
Hello Sundarvelan, Assume your sheet name always be in dd-mmm-yy format, if so see the attached. In Summary B1 B2, enter the Start End Date, So this will calculate results between these sheets. ___ HTH, Haseeb -- 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 Copy_PROJECT.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Urgent help needed please
Hello Arpita,
A little confusion in the part =1/-1
If you want to SUM all 0 values, you can use
*
=SUMIF(A1:A16,0,A1:A16)*
If you are looking to SUM between =-1 to 1 (minus 1 to positive 1), use
*
=SUM(SUMIF(A1:A16,{=**-**1,1},A1:A16)*{1,-1})*
HTH
Haseeb
On Monday, February 27, 2012 3:32:19 PM UTC+3, Arpita kapoor wrote:
Hi,
Can anyone please help me with a simple formula for the below data set.
All I need is to a sum of all the figures (+/-) except for those which are
=1/-1
0.47 0.40 0.26 1.05 0.21 2.50 0.05 -0.03 -0.07 -0.08 -0.31
-0.48 -0.65 -0.66 -0.80 -11.26
I don't seem to get it, I am sorry if it sounds very trivial, but any help
will be appreciated,
Thanks alot
--
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
$$Excel-Macros$$ Re: ifError not woking with excel 2003 version
Hi Chandru, In a cell eg: A1 use this formula to count total count, where E1 contains in Data!E3:E640 =SUMPRODUCT(ISNUMBER(SEARCH(E1,Data!E3:E640))*(E1)) Then in A3 with CTRL+SHIFT+ENTER, rather than just ENTER =IF(ROWS(A$3:A3)=A$1,INDEX(Data!E:E,SMALL(IF(ISNUMBER(SEARCH(E$1,Data!E$3:E$640)),ROW(Data!E$3:E$640)),ROWS(A$3:A3))),) Drag down as necessary. HTH Haseeb -- 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
$$Excel-Macros$$ Re: date format
Hi Lee, try For the Week, =WEEKNUM(TEXT(A1,\-00\-00)) For the month, =TEXT(TEXT(A1,\-00\-00),) HTH Haseeb -- 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
Re: $$Excel-Macros$$ $$Excel-Macros$$ Validate text and number in PAN
Hi Sam,
I think this part ISNUMBER(--MID(A3,6,4)) will allow entry with 3 spaces
before the fourth digit :-) , like
ABCDE 1D
Hi Vijayendra,
This is my try. Firstly define 3 names to avoid INDIRECT function.
Name: *Alpha*
On refers to:
={A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z}
Name: *Row*
Refers to: ={1;2;3;4;5}
Name: *Col*
Refers to: ={6,7,8,9}
Then use this formula in Data Validation Custom. Make sure you have
unselect *Ignore blank*
=SUMPRODUCT(ISNUMBER(MATCH(MID(C2,Row,1),Alpha,0))*ISNUMBER(MID(C2,Col,1)+0)*ISNUMBER(MATCH(MID(C2,10,250),Alpha,0)))=20
HTH
Haseeb
--
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
Validate_text_and_number_in_PAN.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Excel - Cell color actions
Hello Amar, As you said colors are changing through CF, check is the conditions TRUE or FALSE, then you can assign the values, if it is true. If you can share a dummy file or your CF condition to the group, it would be helpful. ___ HTH, Haseeb -- 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$$ To Extract a text with Conditions
Try this *Array Formula*, =INDEX(MID(A6,ROW(A$1:A$50)*4-3,3),MAX(IFERROR(MATCH(MID(A6,ROW(A$1:A$50)*4-3,3),IF(MID(A6,ROW(A$1:A$50)*4-3,3)=,A,MID(A6,ROW(A$1:A$50)*4-3,3)),0),))) Copy down.. ___ HTH, Haseeb -- 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$$dividing my data set in 70-30 Ratio
Hello Vikash, Assume the values are in A2:J2. In A3, copy across. =INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2))) This may give you duplicate values. if you only want the unique values. use these. A3, =INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2))) B3, *Array Formula must hit CTRL+SHIFT+ENTER rather than just ENTER* =INDEX($A$2:$J$2,MATCH(1,IF(ISNA(MATCH($A$2:$J$2,$A3:A3,0)),IF(RANDBETWEEN(1,COUNTA($A$2:$J$2)),1)),0)) copy across. -- 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$$ Re: Need a Macro To Generate Tables with Statistical Calculations
Or, Use a validation list of all priorities in A1 just select the priorities from the list. If you are using list, instead of LOOKUP you can link them directly as =$A$1, for the numbers as =B$1 -- 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$$ I can’t understand why I m unable to do sum
Also, select col_E press Ctrl+H to activate Find/Replace, from the keyboard NUMERIC side (right side of the keyboard, not the top row) Press Alt+0160 in Find What box: and click Replace All. Now you can do a simple SUM. Or use this Array Formula, =SUM(IFERROR(--SUBSTITUTE(E2:E18,CHAR(160),),0)) ___ HTH, Haseeb -- 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$$ How to Extract the Second Biggest number from a list of numbers
Hello Asa R, Assume your numbers are in A:A, in B1 enter *Unique_Numbers*, B2 copy down... =SMALL(A:A,1+COUNTIF(A:A,=MAX(B1,*-*9E300))) The above will give the unique numbers in Ascending Order, =LARGE(A:A,1+COUNTIF(A:A,=MIN(B1,9E300))) This above will give the numbers in Descending Order. ___ HTH, Haseeb -- 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$$ How to Extract the Second Biggest number from a list of numbers
You can also use SMALL function like, =SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1)) will give the 2nd largest number, even if you have duplicate or not. ___ HTH, Haseeb -- 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$$ need correction in the formula
Let me explain my best. I hope this help.
9E300;
When you use it in Excel it will changed to 9E+300. It is a scientific
notation of a big number. 300 zeros after 9, so it is like
9000…… This will give a big number. As you know LOOKUP will
always look for the number =Lookup_Value. So here is
=900…..
SEARCH({Sa,Su,Sha},B4);
This will look for the each array in B4, “Sa”,’Su”,”Sha” will give the
position (*How many characters after*). For B4 will get like
{1,#VALUE!,#VALUE!}
So here “Sa” contains in starting of B4, rest of the doesn’t. LOOKUP will
ignore the error values.
In these 3 arrays, position of the Lookup_value is 1 (=9E300, =1) Lookup
will give the corresponding value in result_array, which is from
{Hero,Boy,Shaan} = “Hero”
Finally Lookup will like,
LOOKUP(9E+300,{1,#VALUE!,#VALUE!},{Hero,Boy,Shaan}) here position of
lookup value is 1, so 1st value from result_vector = Hero
For B5,
LOOKUP(9E+300,{#VALUE!,1,#VALUE!},{Hero,Boy,Shaan}) here position of
lookup value is 2, so 2nd value from result_vector = Boy
For B6,
LOOKUP(9E+300,{#VALUE!,#VALUE!,1},{Hero,Boy,Shaan}) here position of
lookup value is 3, so 3rd value from result_vector = Shaan
__
Haseeb
--
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$$ need correction in the formula
Hello Santosh,
Another one.
=IFERROR(LOOKUP(9E300,SEARCH({Sa,Su,Sha},B4),{Hero,Boy,Shaan}),)
__
HTH
Haseeb
--
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$$ Help Required.xlsx
Hello Noorie Sam; Good ones, but as you know, If a site has LARGE date more than 1 (*not sure does the OP have this situation or not*), this wouldn't work as expected. I think the attached would work. __ HTH Haseeb -- 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 Help_Required_Copy.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Help on Indirect Formula
Hello JP, Please do not post multiple thread for the same query.
In Summary B4, copy down across.
=LOOKUP(9E+300,INDIRECT('$A4'!B1:ADDRESS(2^20,COLUMNS($A4:B4
__
HTH
Haseeb
--
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$$ remove a certain text from every sentence
Hello Smitha; If you need a formula use, =REPLACE(A1,1,FIND(:,A1),) Or Select your data, press CTRL+H; Find What: **:* Click Replace All Hope this helps Haseeb -- 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$$ Excel-marco- need u r help -- $unil
Hello Sunil; Also, you can use SUMIF, =SUMIF(A2:A11,=TODAY(),B2:B11) _ HTH Haseeb -- 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
$$Excel-Macros$$ Re: Advanced Filter
Akhilesh, Please attach a dummy sheet with your desired result. So the members can look on it. __ Haseeb -- 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 to count Fridays in a month mis-reports February
Or, another one; =INT((WEEKDAY(B1-DAY(B1)+1-6)+EOMONTH(B1,0)-(B1-DAY(B1)+1))/7) Even if B1 is not the first day of the month, it will work. If you are using XL2003 or prior versions, EOMONTH function require Analysis ToolPak Addins to be installed activated. __ HTH Haseeb -- 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$$ Help
Mahreen, If you have multiple ratio values define a name for them each, like Table1, Table2 choose which table to use. See the attached. HTH Haseeb -- 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 Formula_in_Validation_List.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Removing specific characters from a string of text
For the actual text, rather than SUBSTITUTE, one way is, C7 copy down. =IF(ISNA(remark LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A7,0),ROW(CurrPeriod))),,A7) Haseeb -- 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$$ Help
Hello Sam;
A nice solution with Validation.
Hello Mahreen;
If you use 3, 2, 1 (number inside ) this will return the number as
text. When you do calculation excel is considering these as text. Use it
without or you can simplify the formula to;
=IFERROR(LOOKUP($E$2,{-9E+300,3;3,1;4,0;5,-3}),0)
and use it as Sam shows.
Another way is to ignore all error values when summing;
=SUMIF(A:A,9E300)
___
HTH
Haseeb
--
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$$ Most Helpful Member Oct'11 - Sam Mathai Chacko
Congratulations Sam -- 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$$
Hello Hanna;
Another one.
=SUMPRODUCT(LOOKUP(DAY(EOMONTH(ROW(INDIRECT(B3:C3)),0)),{28,29,30,31},A3/{28,29,30,31}))
--
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
$$Excel-Macros$$ Re: formulas to get the week in the month and week of the year
Hello Lee, See the attached. If you are using XL 2003 or prior WEEKNUM function need Analysis ToolPak Addin to be installed activated. Other formulas will work with all versions. Hope this help you. Haseeb -- 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 Week_Number.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Simple Excel Calendar
Hello Siti; Simple a nice formula. -- 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$$ Need your support (Re-arrange_the_products)
Hello XLS S; Let me try to explain my best :) In sheet2 B1, used a formula to get all items in sheet1, which is =COUNTA(Source!A3:Z65536) B2, used too get total columns used, which is =COUNTA(Source!A2:Z2) Based on the attached, there are 3 columns 300 rows. So, first 3 rows need the data from row 1 in sheet1, but different columns. Next 3 rows from Row2 Row1_ColA Row1_ColB Row1_ColC Row2_ColA Row2_ColB Row2_ColC So, the logic is after 3 rows Row # needs to increase 1, Column # needs to add 1 on each row, after 3 rows it needs to reset to 1 INT will rounddown a value to Integral. say, 2.9 will round to 2, 0.0001 to 0, 3.5 to 3 etc A5 formula =INDEX(Source!$A$3:$Z$65536,INT((ROWS(A$5:A5)-1)/$B$2)+1,MOD(ROWS(A$5:A5)-1,$B$2)+1) (ROWS($A5:A5)-1) *| *How may rows in A5:A5 = 1, 1-1 = 0 (ROWS($A5:A5)-1)/$B$2 *|* 0/3 = 0 INT((ROWS(A$5:A5)-1)/$B$2)+1 *|* INT(0)+1, 0+1 = 1 MOD function will returns the remainder after number is divided by divisor. eg: =MOD(3,6), multiplication of 6 where falls =3, here it returns 1. If number is less than the divisor, MOD will return the same number. =MOD(20,6), multiplication of 6 where falls =20, here it falls on 18 (3*6), So the remaining value is 2. MOD will return 2 =MOD(25,5) multiplication of 5 where falls =25, here it falls on 25 (5*5), So the remaining value is 0. MOD will return 0 In the attached, MOD(ROWS(A$5:A5)-1,$B$2)+1 ROWS($A5:A5)-1 *| *How may rows in A5:A5 = 1, 1-1 = 0 MOD(0,3)+1 *|* 0+1 = 1 So the INDEX will become, INDEX(Source!$A$3:$Z$65536,1,1) A15, formula will be like =INDEX(Source!$A$3:$Z$65536,INT((ROWS(A$5:A15)-1)/$B$2)+1,MOD(ROWS(A$5:A15)-1,$B$2)+1) INT((ROWS(A$5:A15)-1)/$B$2)+1 *|* INT(10/3)+1, INT(3.33)+1, INT(3)+1, 3+1 = 4 MOD(ROWS(A$5:A15)-1,$B$2)+1 *|* MOD(10,3)+1, MOD(1)+1, 1+1 = 2 So the INDEX will become, INDEX(Source!$A$3:$Z$65536,4,2) If you use Formula Evaluator tool, you can see the calculation easily step by step than my longest explanation :) HTH Haseeb -- 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
$$Excel-Macros$$ Re: Advanced Filter
Hello Akhiles, Use Advanced Filter. In Sheet2, insert 3 blank rows above the heading, then use this formula in A2 =ISNA(MATCH($A5,Sheet1!$A:$A,0)) Then select Sheet2 data, use Advanced Filter. In Advanced Filter, Select Copy to another location List Range: Select Sheet2 data. Criteria Range: Select A1:A2 Copy To: Select a cell where do you need the result. If you need unique list, tick Unique Records Only. Click OK See the attached HTH Haseeb -- 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 Copy_of_Example.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Find last number in a column with numbers
Hello Ken; Also, =MATCH(9E300,A:A) will give the last row number contains number. so you can define name range with this as length See the attached. Same data as Sam posted. HTH Haseeb -- 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 Copy of Dynamic+Chart+Source+With+Rows+Blank.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Find last number in a column with numbers
Hello Ken; Also, =MATCH(9E300,$B:$B) will give the last row number contains number. so you can define name range with this as length See the attached. Same data as Sam posted. HTH Haseeb -- 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 Copy of Dynamic+Chart+Source+With+Rows+Blank.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Auto Reference
Also, Another one to avoid OFFSET volatile, In E2 copy down. =MAX(B2:INDEX(2:2,MATCH(z,$1:$1)-1)) HTH Haseeb -- 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$$ Removing specific characters from strings of texts
Hello Zeunasc, You can also do it through Find/Replace command. Select the column, press Ctrl+H to bring Find/Replace command. Find What : *(hitcnt** Replace All with blank. Again; Find What : *0x** Replace All with blank. Note: There is a * after each Find item. HTH Haseeb -- -- 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$$ Removing specific characters from a string of text
Hello Zeunasc;
I am not good in to explain something. Here is an attempt. Hope this would
help you.
A2 = Start Date, here 10/20/2011
A4 = End Date, here 10/25/2011
Basically 10/20/2011 is 40836 (40836 days after 1/1/1900)
10/25/2011 is 40841 (40841 days after 1/1/1900). If you change the cell
format to General you can see this number.
Defined a name “*CurrPeriod*” with Start End date.
*=INDEX(Sheet1!$A:$A,Sheet1!$A$2):INDEX(Sheet1!$A:$A,Sheet1!$A$4)*
If you use INDEX before or after *:* it will give the actual cell reference,
rather than it’s value. So here it become,
INDEX(Sheet1!$A:$A,40836):INDEX(Sheet1!$A:$A,40841), which is
A40836:A40841
Note: you can also use INDIRECT here, =INDIRECT(Sheet1!$A$2:Sheet1!$A$4)
INDIRECT is volatile function. This will slow up the calculation.
Take A8 formula as an example.
=LOOKUP(z,CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),remark
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/),)))
ROW(CurrPeriod) *|* This will give the array of the start end dates,
here, ROW(A40836:A40841), which is {40836;40837;40838;40839;40840;40841}
TEXT(ROW(CurrPeriod),mm/dd/) *|* Here TEXT function will convert
the all the values to mm/dd/ format, which is
{10/20/2011;10/21/2011;10/22/2011;10/23/2011;10/24/2011;10/25/2011}
*TEXT(ROW(CurrPeriod),mm/dd/)* *|* * will help to look for
partial match. Array will become
{*10/20/2011*;*10/21/2011*;*10/22/2011*;*10/23/2011*;*10/24/2011*;*10/25/2011*}
MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0) *|* If any of the
above array contains in A8, will give 1 otherwise #N/A. So here will become
{1;#N/A;#N/A;#N/A;#N/A;#N/A}. This means first value (10/20/2011) is
contains in A8 rest of them doesn’t.
LOOKUP is always looking for approximate value. Since we looking on a single
cell MATCH always will give 1 or #N/A.
LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod))
| So this part will become
LOOKUP(2,{1;#N/A;#N/A;#N/A;#N/A;#N/A},{40836;40837;40838;40839;40840;40841})
| so here LOOKUP will give 40836 which is 10/20/2011
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/)
*|* So here this will become
TEXT(40836,”mm/dd/”) which is 10/20/2011
remark
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/)
*|* will become here, remark 10/20/2011
TRIM(A8) *| *This will avoid all unnecessay spaces If A8 contains.
SUBSTITUTE(TRIM(A8),remark
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/),)
This will become here, *SUBSTITUTE(TRIM(A8),remark 10/20/2011,) *which
will replace remark 10/20/2011 to “”
CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),remark
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/),))
This will give array of A8 Substituted value. If any of the number doesn’t
contain in A8 LOOKUP will give #N/A error, so will give a array of A8 #N/A
=LOOKUP(z,CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),remark
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/),)))
This will look for the text which is in last. If #N/A is in last will show
the A8 value otherwise the substituted text.
If you are using XL2007 or later, you can use IFERROR; like
=IFERROR(SUBSTITUTE(TRIM(A8),remark
TEXT(LOOKUP(2,MATCH(*TEXT(ROW(CurrPeriod),mm/dd/)*,A8,0),ROW(CurrPeriod)),mm/dd/),),)
*LOOKUP function*;
http://office.microsoft.com/en-us/excel-help/lookup-HP005209163.aspx
*Volatile Functions*;
http://www.decisionmodels.com/calcsecretsi.htm
Hope this helps;
Haseeb.
--
--
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$$ querry
Hello Nemi; You can also use simple HLOOKUP. =HLOOKUP(C2,I$3:T$105,MATCH(A2,H$3:H$105,0)+B2,0) C2, here column heading B2 is the row values located in Col_H HTH Haseeb -- -- 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$$ Extract from date and time
Hello Arslan; If you have real dates time, doesn't need MOD function. A1 = some date time; eg: 4/11/2011 08:39:20 PM Use in B1 for date, format as date =INT(A1) Use in C1 for time; format as time =A1-B1 HTH Haseeb -- -- 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$$ Removing specific characters from a string of text
Hello Zeunasc; Try the attached. Enter a start date in A2, End date in A4. HTH Haseeb -- -- 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 Parse_Text.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Help
Hello Surya; If your data is sorted by Country in Ascending order, use the file sorted_by_Country. The other one will work with sorted or unsorted data. Also used dynamic range. HTH Haseeb -- -- 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 Sorted_by_Country.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet Unsorted_Data.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Formula needed to extract the text from string
Hello Anil, A short version to Ms.Exl.Learner =TRIM(MID(SUBSTITUTE(A2,/,REPT( ,255)),4*255,255)) HTH Haseeb For help tips visit; http://www.excelfox.com/forum/forum.php -- -- 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
$$Excel-Macros$$ Re: Dates In Excel
Another one;
=INT((WEEKDAY(A1-*1*)+B1-A1)/7)
Note: There are 2 opening brackets before WEEKDAY.
A1 = Start_Date
B1 = End_Date
*1* = Sun, *2* = Mon, *3* = Tue, *4* = Wed, *5* = Thu, *6* = Fri, *7* = Sat
If you want to count multiple days between 2 dates use then inside an Array
separated by comma,
For Eg: If you want to count Sun, Tue, Sat, use like this,
=SUM(INT((WEEKDAY(A1-{*1*,*3*,*7*})+B1-A1)/7))
Change the appropriate weekday numbers, whichever day(s) you want to count.
HTH
Haseeb
For free MS product help tips, visit;
http://www.excelfox.com/forum/
--
--
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 to LOOKUP a value in a table and return the table header value
Hi John, Try these, K3, copy down. =LOOKUP(LOOKUP(9E+300,C3:J3),N3:X3,N$2:X$2) L3, copy down. =LOOKUP(LOOKUP(9E+300,C3:J3),Z3:AJ3,Z$2:AJ$2) This will look the last entered value. HTH Haseeb -- -- 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$$ Extract 6 7 Digit Number
Hi, Karan,
Not a good one, but try this Array Formula,
If you are using XL 2003 or prior,
=MAX(IF(ISNUMBER(MID(SUBSTITUTE(A2,
,),ROW(INDIRECT(1:LEN(A2))),{6,7})+0),MID(SUBSTITUTE(A2,
,),ROW(INDIRECT(1:LEN(A2))),{6,7})+0))
If you are on XL 2007 or later,
=MAX(IFERROR(MID(SUBSTITUTE(A2,
,),ROW(INDIRECT(1:LEN(A2))),{6,7})+0,0))
Copy down... See the attached.
HTH
Haseeb
For free MS Product help tips, visit;
http://www.excelfox.com/forum/
--
--
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
Extract_6_7_Digit.xls
Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Uniques Values
Hi Sundarvelan, Since you are dealing with valid numbers use SMALL function. in G3 enter, =MIN(B:D) G4, copy down. =IFERROR(SMALL(B:D,COUNTIF(B:D,=G3)+1),) HTH Haseeb For free help visit; http://www.excelfox.com/forum/ -- -- 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
$$Excel-Macros$$ Re: Like pivot table function
Hello Naresh, Use SUMIF with SUMPRODUCT, =SUMPRODUCT(SUMIF(A:A,H2:H4,B:B)) HTH Haseeb For fre help visit; http://www.excelfox.com/forum/forum.php -- -- 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
$$Excel-Macros$$ Re: ***Macro Code Required to delete Zero value cell and its row***
Hello Prabhu, Also try this; Sub DelZeroRows() Dim LR As Long Dim Length As Long Application.ScreenUpdating = False LR = Range(P Rows.Count).End(xlUp).Row Count = Application.WorksheetFunction.CountIf(Range(P2:P LR), 0) For Length = LR To 2 Step -1 '-- Change 2 to the start row If Cells(Length, P).Value = 0 Then Cells(Length, P).EntireRow.Delete End If Next Length Application.ScreenUpdating = True Range(A2).Activate MsgBox Count Row(s) were deleted, vbInformation End Sub -- -- 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
$$Excel-Macros$$ Re: ***Macro Code Required to delete Zero value cell and its row***
Hello Prabhu, Also try this; Sub DelZeroRows() Dim LR As Long Dim Length As Long Application.ScreenUpdating = False LR = Range(P Rows.Count).End(xlUp).Row Count = Application.WorksheetFunction.CountIf(Range(P2:P LR), 0) For Length = LR To 2 Step -1 '-- Change 2 to the start row If Cells(Length, P).Value = 0 Then Cells(Length, P).EntireRow.Delete End If Next Length Application.ScreenUpdating = True Range(A2).Activate MsgBox Count Row(s) were deleted, vbInformation End Sub HTH Haseeb For free help visit; http://www.excelfox.com/forum/forum.php -- -- 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
$$Excel-Macros$$ Re: Can you please help regd. lists
Hello Naresh, See the attached. You can Add/Delete New/Old entries. HTH Haseeb For free help, visit, http://www.excelfox.com/forum/forum.php -- -- 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 Dependent_List.xls Description: MS-Excel spreadsheet
Re: Fwd: $$Excel-Macros$$ urgent macro or template r eq
Hello Lee, See the attached with Array Formula. HTH Haseeb For free MS product help, visit; http://www.excelfox.com/forum/forum.php -- -- 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 LOOKUP Data.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Subtract the minimum value based on 2 criteria
Hello dewbro, See the attached. Used DMIN to avoid Array Formulas. Insert 3 rows above the heading use as shown in the attached. HTH Haseeb For tips visit; http://www.excelfox.com/forum/forum.php -- -- 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 Subtract_the_minimum_value.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Rajan Verma : Most Helpful Member - July'11
Congratulations Rajan...! Regards, Haseeb -- -- 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$$ Dynamic Chart
Hello Chandru, See the attached. If doesn't have 12 entries it will adjust to available entries. If do have more than 12 week entries will show the last 12 week data. HTH Haseeb For tips visit; http://www.excelfox.com/forum/ -- -- 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 Dynamic_Chart.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ How to Find Last Present Date From Month in One column
Hello Maulik, If you just have single characters in cells like A,P etc... use this, AF2 copy down. =LOOKUP(10,SEARCH(p,B2:AE2),B$1:AE$1) Or, =LOOKUP(2,1/(B2:AE2=p),B$1:AE$1) The first one is more faster than second one. HTH Haseeb For tips visit; http://www.excelfox.com/forum/forum.php -- -- 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$$ Reverse Vlookup
Hello Anil.
You can use VLOOKUP for Reverse Lookup. Consider A1:B20 is the data. E2 is
the Lookup_Value from Col_B. So use this,
=VLOOKUP(E2,CHOOSE({1,2},$B$2:$B$20,$A$2:$A$20),2,0)
CHOOSE({1,2},$B$2:$B$20,$A$2:$A$20) will give the array of Col_B value first
then Col_A. So VLOOKUP will work as usually works.
Or, as the experts shows you can use, INDEX/MATCH, OFFSET, LOOKUP
See the attached.
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/forum
--
--
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
RVLOOKUP.xls
Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ minimum numeric positive value
Hello Sundar, Also, a non array formula with just ENTER. =SMALL(A:A,COUNTIF(A:A,=0)+1) HTH Haseeb For tips visit; http://www.excelfox.com/forum -- -- 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$$ Result not geting using SUMIFS, Please help
Hello Kalyan,
If you just have two customers, you can hard coded as array use 1 SUMIFS
with SUM.
Without checking MOC in B3
=SUM(SUMIFS('Raw data'!D:D,'Raw data'!A:A,$B5,'Raw
data'!C:C,{Distributor,Super stockist}))
With MOC in B3,
=SUM(SUMIFS('Raw data'!D:D,'Raw data'!A:A,$B5,'Raw data'!B:B,B$3,'Raw
data'!C:C,{Distributor,Super stockist}))
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/
--
--
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: RE: $$Excel-Macros$$ help required
Hello Vijay, See the attached. HTH Haseeb For tips visit; http://www.excelfox.com/forum/forum.php -- -- 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 Book1-Updated.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: RE: $$Excel-Macros$$ Please help
Hello Anil, Also, If you have other text or charracters other than #n/a and just need to count the numbers only, use this, =COUNT(INDEX(MID(C2,ROW(INDEX(C:C,1):INDEX(C:C,LEN(C2))),1)+0,0)) Copy down HTH Haseeb For tips visit; http://www.excelfox.com/forum/forum.php -- -- 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
$$Excel-Macros$$ Re: Reverse Sorting Of Rows
Assume you need the entries, Last in First, First in Last. Let's say the entries are in A2:A10. Try this in B2, copy down. =INDEX(A$2:A$10,ROWS(A2:A$10)) HTH Haseeb For Tips visit; http://www.excelfox.com/forum/forum.php -- -- 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$$ Max value from a set of group
Hello Vickey, Use DMAX formula. See the attached. HTH Haseeb -- -- 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 sample - DMAX.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Data Validation - Create a drop down list containing only unique
Hello Venkat, Use a separate sheet to get the unique offices. You can hide this sheet, if you don't want to show. Also use dynamic range name, so it will update automatically when add/delete entries. Assume there are no blank cells in Data Col_A Offices. If there is blank change the formula in Unique!A4 to, =IF(ROWS(A$4:A4)=B$1,INDEX(Data,MATCH(1,IF(INDEX(Data,0,1),IF(ISNA(MATCH(INDEX(Data,0,1),A$3:A3,0)),1)),0),1),) Then copy down. See the attached. HTH Haseeb http://www.excelfox.com/forum/forum.php -- -- 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 Unique Data Validation.xls Description: MS-Excel spreadsheet
Re: RE: $$Excel-Macros$$ adding adjacent (contagious) cells only
Thank you Dave for the last cell point the correction. I didn't notice the last column being Yes. I think Column U don't have to be blank, because we have an additional column (A) without Yes or No, so If we change the formula without including column U, like, =SUMPRODUCT(--(B2:T2=Yes),--(A2:S2Yes)) Or, =COUNTIFS(B2:T2,Yes,A2:S2,Yes) Regards, Haseeb -- -- 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
$$Excel-Macros$$ Re: ***Formula for find and replace***
Hello Prabhu,
Use SUBSTITUTE. or excel's Find/Replace command
SUBSTITUTE is a case sensitive function, so you need convert the cell to
UPPER or LOWER, so use,
=SUBSTITUTE(UPPER(A1),ABC,)
Or,
=SUBSTITUTE(LOWER(A1),abc,)
All the letters in 1st output will be in CAPITAL LETTERS
All the letters in 2nd output will be in small letters.
If you need the results as the same as original, Try this,
=INDEX(SUBSTITUTE(A1,{ABC,abc},),MATCH(MIN(LEN(SUBSTITUTE(A1,{ABC,abc},))),LEN(SUBSTITUTE(A1,{ABC,abc},)),0))
Or use Find/Replace command. select the range, Press Control+F
Find What: abc
leave the Replace with blank, then click Replace All
HTH
Haseeb
--
--
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$$ adding adjacent (contagious) cells only
Hello Azeema, Use this formula, say in V2 copy down. =COUNTIFS(A2:S2,Yes,B2:T2,Yes) This will only work XL2007 or later. If you want to work with XL 2003 or prior use SUMPRODUCT =SUMPRODUCT(--(A2:S2=Yes),--(B2:T2Yes)) See the attached HTH Haseeb -- -- 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 UnpaidWork with COUNTIFS.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: RE: $$Excel-Macros$$ Delayed Days
Hello Nilesh, Another way, K2 copy down. =TEXT(I2,\/00\/00)-TEXT(J2,\/00\/00) If you don't want to see -days use =ABS(TEXT(I2,\/00\/00)-TEXT(J2,\/00\/00)) Or, convert the Dates to actual date, for doing this, Select the whole column I:I, go to, Data Text to Columns Next Next, then from the top left side, select Data from the drop down list select YMD, click Finish. Do the same for Column_J. Then you can simply use in K2, =I2-J2 to avoid -days change to =ABS(I2-J2) -- -- 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$$ Numbering
A2, copy down as many needed =IF(ROWS(A$2:A2)=A$1,ROWS(A$2:A2),) Or, If you are inserting rows between A2 the last rows this will not work as expected, so use this one; =IF(COUNT(A$1:A1)=A$1,COUNT(A$1:A1),) copy down as many needed HTH Haseeb -- -- 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 question
Hi, C2 copy down. =IFERROR(INDEX(D$1:SF$1,MATCH(TRUE,INDEX(ISNUMBER(1/D2:SF2),),0)),Not Started) HTH Haseeb -- -- 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$$ count unique values
Hello AIren,
Consider B1:B6 we have these values;
B1=1
B2=1
B3=Blank
B4=A
B5=Blank
B6=Blank
=SUMPRODUCT((B1:B6)/COUNTIF(B1:B6,B1:B6))
Firstly take (B1:B6)
This will check B2:B30 is blank or not, If it is blank will give FALSE, if
not will give TRUE. So will get like this
{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}
First 2 cells are not blank;3rd one is blank, 4th one is not blank;5th 6th
are blank
COUNTIF(B1:B6,B1:B6)
This will count B1:B6 against the same range B1:B6. So will get the count of
the occurances of each values in the range.
If you are adding * *this will add a non zero length text value to the
every cells. So blank will become a non zero length text value. If you are
not adding this will give you a #DIVO/0! error. Because all the count of
blank cells will count as 0
Without , the array will be,
[2;2;0;1;0;0}
With , the array will be,
{2;2;3;1;3;3}
See, All blank cells is changed to 3, count of all the blank cells in the
range.
First 2 cells counts are 2 (First cell value 2nd cell value should be
same);3rd one is blank;4th ones count is 1;5th 6th cells are blank
So, the Arry in SUMP will become;
SUMPRODUCT({TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}/{2;2;3;1;3;3})
TRUE will converted to 1
FALSE will to 0
So, here;
{1/2;1/2;0/3;1/1;0/3;0/3}
Which is;
{0.5;0.5;0;1;0;0}
{0.5+0.5+0+1+0+0}
=2
See the below link more about SUMPRODUCT;
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
HTH
Haseeb
--
--
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$$ Sumif across multiple worksheets
Hello Susan,
If you just have 3 sheets, Sheet1, Sheet2 Sheet3. use this;
=SUMPRODUCT(SUMIF(INDIRECT('Sheet{1,2,3}'!D:D),C
Wagner,INDIRECT('Sheet{1,2,3}'!E:E)))
Or,
Enter all sheet names in a range, say in J2:J3, then use this;
=SUMPRODUCT(SUMIF(INDIRECT('$J$2:$J$4'!D:D),C
Wagner,INDIRECT('$J$2:$J$4'!E:E)))
HTH
Haseeb
--
--
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
