$$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.
Re: $$Excel-Macros$$ Query related to Salary binding up
Hello Ankit, Attached is a one way. This is not a best one, some one will have better options. I have added two columns. One is Dpmt% 2nd one is No: of Dpmt, which is return through a formula. No:of Dpmt column, MUST NOT be empty or zero. Better to use dynamic range or convert your range to Table. Table format will adjust the No:of Dpmt column when add new entries. Hope this helps Haseeb -- 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. Query related to salary.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Suproduct not equal range
Hello Chandru, Fiirst of all, I am not sure where is these conditions in the sample, you provided. (a1:a5=1)*(a1:a5=4). I only can see two columns in the sample. ColA: 10;20;30;40;50. ColB 100;200;300;400;500. Anyway, you can use ISNA(MATCH or COUNTIF to check LIST 2 is NOT contains in List. =SUMPRODUCT(ISNA(MATCH(A1:A5,A8:A9,0))+0,B1:B5) Or, =SUMPRODUCT((COUNTIF(A8:A9,A1:A5)=0)+0,B1:B5) Hope this helps, Haseeb -- 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: Sumif Based on First Number of a Date Range Help Needed
Hello Matty, FYI; The above formula required Product entered in Sheet2, must be grouped in Sheet1. Otherwise wouldn't give accurate answers. Here is a Non-Volatile Non-Array formula, In B6, =SUMPRODUCT((TEXT(Sheet1!$A$3:$A$122,mmm;;)=TEXT($A61,mmm;;))*(LEFT( Sheet1!$B$2:$Q$2,LEN(B$4))=B$4),Sheet1!$B$3:$Q$122) Copy down across. Haseeb -- 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: Extracting a word from the cell.......
One common way to extract *n*th word. Assuming word separator is 'space' =TRIM(MID(SUBSTITUTE( TRIM(A1), ,REPT( ,250)),n*250,250)) Eg: If we want to extract 3rd word, use it like; =TRIM(MID(SUBSTITUTE( TRIM(A1), ,REPT( ,250)),3*250,250)) If a cell doesn't have specified nth word, will return 'blank' Regards, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Re: Unique Name Query
Hello Karan, In C2, with CTRL+SHIFT+ENTER, and copy down. =IFERROR(INDEX(A$2:A$100,MATCH(1,IF(A$2:A$100,IF(ISNA(MATCH(A$2:A$100,C$1:C1,0)),1)),0)),) If you need Unique Names in *Ascending Order*, In C2, with CTRL+SHIFT+ENTER, and copy down. =IFERROR(INDEX(A$2:A$100,MATCH(0,IF(A$2:A$100,COUNTIF(A$2:A$100,A$2:A$100),)-SUM(COUNTIF(A$2:A$100,C$1:C1)),0)),) Note: You must have something for IF's false argument ie. , any text or a Big number like 2^21, 9E+300 etc.. If you need Descending Order, change to These formulas will work even if you have blank cells in the range. See the sample file. Regards, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Unique Name query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: VLOOKUP OR SUMPRODUCT FORMULA HELP
Hello Amit, Try this in G15 and copy down across. =SUMPRODUCT(ISNUMBER(MATCH($F15|$F$5:$F$9,$A$5:$A$21|$B$5:$B$21,0))+0,G$5:G$9) Regards, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Lookup and return 2nd occurrance
Hello Hilary, If you want to return the exact 2nd occurrance value, you can use VLOOKUP like this, =VLOOKUP(A14,INDEX(A:A,MATCH(A14,A:A,0)+1):INDEX(B:B,65536),2,0) Regards, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ How to distribute one value into different cells proportionately based on some conditions
Hello Amit, Keep PO Bill Party Bill on different tab, so can easily update it. See the attached. Addition: If you use Table format, formula will automatically insert, when you enter a new data. Or use dynamic range to limit calculation range. If you wish, you can also create a validation list of Party Bill's then apply those in PO Bills column, this way can avoid unnecessary bill numbers. Hope this helps, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Distribute_Values.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Automatic token numbers
Hello Deba, FYI: If you use LD000 as constant this will add with each number, means when gets 10-99, this will become, LD00010, when gets 100-999 will become LD000100. If you want to keep the format, LD, 1-9 - LD0001, 10-99 - LD0010, 100-999 - LD0100, 1000- - LD1000, here is one way. A3, then copy down. =IF(B3,TEXT(COUNTA(B$3:B3),\L\D),) Hope this helps, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Change in one Cell should lead to change in all related cells
Hello Satish, One way is VLOOKUP E5, then copy down across. =IFERROR(VLOOKUP(E$4,$M$4:$S$10,MATCH($D5,$M$3:$S$3,0),0),) Regards, 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Re: Getting last column value via Vlookup in excel
Hello Shekhar, If the values always be numbers, here is one way. =LOOKUP(9E300,INDEX(Sheet2!$1:$65536,MATCH(A2,Sheet2!$A:$A,0),0)) Where A2 = name_in_Sheet1 Regards, Haseeb A -- 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: Defining Same Name For ranges available in multiple tabs
Hello Shekhar,
If I am following correctly, you can use SUMPRODUCT with SUMIF/INDIRECT
Enter the sheet names in D1:D10 (or how many you have. Do not include empty
cells in the range) of the summary sheet. then use
=SUMPRODUCT(SUMIF(INDIRECT('$D$1:$D$10'!A:A),A1,INDIRECT('$D$1:$D$10'!B:B)))
Regards,
Haseeb A
--
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: formula required for count or count if
Hello Lee, This should work with just ENTER. in AC2, then drag down. =SUMPRODUCT((A$2:A$23=AB2)*(B$2:Z$23)/COUNTIF(B$2:Z$23,B$2:Z$23)) Adjust the ranges. See the attached. Regards, 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 Count_Unique.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Happy Birth Day Ayush.
*Happy Birthday Ayush* 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: Count intial #NA in a row using excel function
Hello Divaker,
Consider data is in A1:Z1.
If you are looking to find first #N/A in the range count #N/A after that
occur consecutively, here is a one way:
Only work with XL2007 or later:
=IFERROR(COUNTIF(OFFSET(A1,,MATCH(TRUE,INDEX(ISNA(A1:Z1),0),0)-1,,MATCH(1,INDEX(1-ISNA(OFFSET(A1,,MATCH(TRUE,INDEX(ISNA(A1:Z1),0),0)-1,):Z1),0),0)),#N/A),0)
This will work in all excel versions:
=LOOKUP(9E300,IF({1,0},0,COUNTIF(OFFSET(A1,,MATCH(TRUE,INDEX(ISNA(A1:Z1),0),0)-1,,MATCH(1,INDEX(1-ISNA(OFFSET(A1,,MATCH(TRUE,INDEX(ISNA(A1:Z1),0),0)-1,):Z1),0),0)),#N/A)))
If you want to check always from first cell eg; A1, you can simplify to:
=COUNTIF(A1:INDEX(A1:Z1,MATCH(1,INDEX(1-ISNA(A1:Z1),0),0)),#N/A)
Adjust the ranges accordingly.
Regards,
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$$ Re: Quarter Classification
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,)
Then in C2
=TEXT((1B2)+99,)
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
Now answer is right ,
i.e. Date 01/05/2010 ( DD/MM/ ) Format ,
Currently it's showing Curr.Qtr = April ; Next Qtr = July ..it is ok
After July , Now I want Next Qtr = October ; Next Qtr = January
Nikhil
--
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_Quarter.xls
Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Group Total
Hello Aamir, Assume always will have 'TOTAL' in the bottom of each group, If so you can use VLOOKUP. See the attached. For the sorting is an Array Formula, so must be with CSE. I have added some dummy data for various groups. Use dynamic range, if you have more data. ___ 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_Group_query.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Stock position - Reg
Hello Mothilal, See the attached. if you are on XL 2007 or later use SUMIFS ___ 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 Stock.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Most helpful Member - March 12- Don Guillett
Congratulation Don. Very happy to see your name on this thread. Regard, 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$$ Stock position - Reg
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={P,S}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
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: Quarter Classification
Hello Nikhil,
This will give the current quarter.
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10})*29,)
This will give the Next Quarter
=TEXT(LOOKUP(MONTH(A1)+3,{1,4,7,10,13})*29,)
___
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$$ Re: Quarter Classification
Nikhil, I got answer July instead of April. There were two formulas in my last reply. First one for Current Quarter, 2nd one for Next Quarter. 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 Quarter.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Sorting cells
Hello Kenrock,
Assume you will have header in row 1 data starts on A2 onwards. So the
below formula will sort the data alphabetically without duplicates.
In B2 with CTRL+SHIFT+ENTER, rather than just ENTER. then copy down as
necessary.
If you are on XL2007 or later,
=IFERROR(INDEX(A$2:A$100,MATCH(0,IF(A$2:A$100,COUNTIF(A$2:A$100,A$2:A$100)-SUM(COUNTIF(C$1:C1,A$2:A$100)),),0)),)
If you are on XL2003 or prior,
=LOOKUP(REPT(z,250),IF({1,0},,INDEX(A$2:A$100,MATCH(0,IF(A$2:A$100,COUNTIF(A$2:A$100,A$2:A$100)-SUM(COUNTIF(C$1:C1,A$2:A$100)),),0
Adjust the ranges.
___
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$$ Unique count for a date between range
Amit, If you are looking to count unique Vehicles in a date a period. here is one way: For a single date: =SUM(IF(FREQUENCY(IF(TEXT(H$4:H$9,md)=TEXT(CabOutDate,md),IF(C$4:C$9,MATCH(C$4:C$9,C$4:C$9,0))),ROW(C$4:C$9)-ROW(C$4)+1),1)) Change cabOutDate to your reference where is the lookup date entered. For the period: =SUM(IF(FREQUENCY(IF(H$4:H$9=StartDate,IF(H$4:H$9EndDate+1,IF(C$4:C$9,MATCH(C$4:C$9,C$4:C$9,0,ROW(C$4:C$9)-ROW(C$4)+1),1)) Change StartDate EndDate with your cell reference where is the lookup dates entered. Both these are Array Formulas, so must hit *CTRL+SHIFT+ENTER*, rather than just ENTER. Also better to use TEXT function, if you have text in column H like N/A, NONE or something. INT will give you an error if there is any text. 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 Unique_Cab_Count.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Unique count for a date between range
Hello Amit, This will count the unique numbers in A:A =SUM(SIGN(FREQUENCY(A:A,A:A))) with just ENTER. ___ 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: pls help with sum if formula
Hello Lee,
One way:
=SUM(SUMIF(G3:G409,{350,449},$D$3:$D$409)*{1,-1})
Will sum 351-449. if you want to include 450, change 449 to 450
___
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: How to use subtotal in sumif formulae
Hello Arslan, One way; =SUMPRODUCT(SUBTOTAL(109,OFFSET(AB11,ROW(AB11:AB249)-ROW(AB11),)),--(AB11:AB2490)) If you have more condition just add it. ___ 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$$ Need assistance in finding formula to substitute the values to dates.
Good One Maries :) As you know OFFSET is volatile. A non volatile formula, =IFERROR(INDEX($B$4:$E$4,MATCH(B$9,INDEX($B$5:$E$7,MATCH($A10,$A$5:$A$7,0),0),0)),) ___ 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: Extract desired data
Hello Shahzad,
Also If you doesn't have always 6 digit this will extract the available
numbers (=6 digits) If any,
=IFERROR(LOOKUP(99,--RIGHT(C2,{1,2,3,4,5,6})),)
___
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$$ Re: Exact names Find
Hello Asa,
Thank you for your explanation.
Deba, also hope this website also help you.
http://www.excelfunctions.net/ExcelCellReferences.html
If that works for you, you can avoid LEFT function,
=INDEX(C$6:C$47,MAX(IFERROR(MATCH(*MID(TRIM(D6),2,{1,2,3,4,5,6,7,8,9})*,SUBSTITUTE(C$6:C$47,
,),0),)))
___
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: Exact names Find
Hello Deba,
Try this in E6 with *CTRL+SHIFT+ENTER*, rather than just ENTER
=INDEX(C$6:C$47,MAX(IFERROR(MATCH(*LEFT(MID(TRIM(D6),2,10),{1,2,3,4,5,6,7,8,9,10})*,SUBSTITUTE(C$6:C$47,
,),0),)))
Then drag down
___
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: Count formula
Hello Shrini,
=COUNTIF(D:D,=StartDate)-COUNTIF(D:D,=EndDate+1)
Or,
=SUMPRODUCT(COUNTIF(D:D,=IF({1,0},StartDate,EndDate+1))*{1,-1})
this way able to use whole column reference in SUMPRODUCT
Change StartDate EndDate to cell reference
___
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: Format Conversation Question
Hi, In a an adjustent cell try to multiply with 24 round by 3 =ROUND(A1*24,3) Change A1 to TOI cell. ___ 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: CONDITIONAL SUM
FYI, If you have blank cells or zero in a date column corresponding column has numbers MONTH function will sum those numbers if the Month in A1 is 1. because in excel blank zeros consider as Jan (in month case) To avoid this this need to use a another condition like (Sheet1!A2:A200) or use TEXT function like, =SUMPRODUCT((TEXT(Sheet1!A$2:A$200,m;;;)=A1)+0,Sheet1!B$2:B$200) Where A1 is month like 1, 2, 3, 4, 5, etc,... If you want to use month like Jan, Feb, Mar in A1, change m;;; to mmm;;; (3 m's) If you want to use full month like January, February. use 4 m's ___ 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: format a cell
Hello Pawel, Another one. =TEXT(A1*IF(ISNUMBER(FIND(.,A1)),100,1),00\:00) If you want to enter 2 for 2'O clock enter as 200 or cell format as text then enter like 2.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$$ == Need help on the sheet
Hello Sreejith, As Seema said, not sure about how do you want to compare due to duplicate trade ID's. If you are looking to total the headings based on TradeID's use SUMIF. In C11, then copy down. Assume Trade Id always be in column A in detailed sheet * =IFERROR(SUMIF(Detailed!A:A,C$8,INDEX(Detailed!A:K,0,MATCH(B11,Detailed!A$2:K$2,0))),0) * In F11, then copy down. Assume Trade ID's always be in column B in summary sheet * =IFERROR(SUMIF(Summary!B:B,C$8,INDEX(Summary!A:K,0,MATCH(E11,Summary!A$2:K$2,0))),0) * If the Trade ID heading also may have in different places, use like this, In C11, then copy down. * =IFERROR(SUMIF(INDEX(Detailed!A:K,0,MATCH(B$8,Detailed!A$2:K$2,0)),C$8,INDEX(Detailed!A:K,0,MATCH(B11,Detailed!A$2:K$2,0))),0) * In F11, then copy down. * =IFERROR(SUMIF(INDEX(Summary!A:K,0,MATCH(B$8,Summary!A$2:K$2,0)),C$8,INDEX(Summary!A:K,0,MATCH(E11,Summary!A$2:K$2,0))),0) * ___ 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: Formula for Unique Values with Condition
Hello Kiran, In G4 with CTRL+SHIFT+ENTER, rather than just ENTER =IFERROR(INDEX(D$2:D$498,MATCH(1,IF(ISNA(MATCH(D$2:D$498,G$3:G3,0)),IF($C$2:$C$498=Agent,1)),0)),) Then copy across down. if you have huge list, this will slow down the sheet performance. ___ 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: 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
$$Excel-Macros$$ Re: Split text in cell
Hello, try also, =TRIM(SUBSTITUTE(MID(A2,FIND( ,TRIM(A2) ),LEN(A2)),TRIM(RIGHT(SUBSTITUTE(TRIM(A2), ,REPT( ,99)),99)),)) On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: words split from a cell - with formulas only Not for VBA code
This is a duplicate thread. There are couple of replies to your last thread. Check out that please. ___ HTH, Haseeb On Thursday, March 22, 2012 8:22:08 AM UTC+3, nike wrote: Please find the attachment of my criteria. Please refer column A and i want result in Column 'B what I need like that. Thanks in advance Simha 970-309-1552 -- 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
