tag:blogger.com,1999:blog-1971974841508744352.comments2024-02-16T02:27:30.012-05:00Chatting about Excel and Moreexcel-divahttp://www.blogger.com/profile/13780428201816311547noreply@blogger.comBlogger49125tag:blogger.com,1999:blog-1971974841508744352.post-6273785892350726862012-04-11T19:30:00.367-04:002012-04-11T19:30:00.367-04:00I have not run into this however my understanding ...I have not run into this however my understanding is that if you are pivoting data externally from an OLAP data cube that the show page option (report filter) will not work in 2010. What version are you using? Are you pulling data externally? Anyone else run into this problem?excel-divahttps://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-68506351961342946992012-04-11T19:18:59.863-04:002012-04-11T19:18:59.863-04:00Try concatenating month and year and have the year...Try concatenating month and year and have the year first. For example, to compare March 2012 to March 2011 concatenate columns such as 201203 and 201103- that way the columns will be in order.excel-divahttps://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-9020654661310811372012-04-05T16:30:33.632-04:002012-04-05T16:30:33.632-04:00This comment has been removed by the author.Robear852https://www.blogger.com/profile/11217769827971752754noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-51659046953442418922012-04-05T16:10:33.528-04:002012-04-05T16:10:33.528-04:00This comment has been removed by the author.Robear852https://www.blogger.com/profile/11217769827971752754noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-23989342865467672812012-03-16T12:54:33.261-04:002012-03-16T12:54:33.261-04:00I think that your problem may be a simple one - it...I think that your problem may be a simple one - it may be due to the fact that you are saving the file as .xls. If you are using Office 10 teh default is .xlsx.<br />If you are saving it down to .xls anything that the lower version doesn't recognize is de-activated so to speak. There is not setting that specifies the number of levels. <br />I have a couple of blog entries on Match Index - here is one of them<br />http://excel-diva.blogspot.com/2010/09/2-way-lookup-using-index-match.htmlexcel-divahttps://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-39729409625334138602012-03-12T17:01:59.154-04:002012-03-12T17:01:59.154-04:00I have MS office professional 2010. I need to do a...I have MS office professional 2010. I need to do a nested if with 10 levels. I am saving my workbook as .xls and as binary, but it will not allow me to do past the 7 levels. is there a setting somewhere i can change to allow the 10 levels, or how do you use the match index function?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-75076288586585226092011-12-21T10:41:43.401-05:002011-12-21T10:41:43.401-05:00ATTENTION!!!
In Excel help, it is WRONGLY stated:...ATTENTION!!!<br /><br />In Excel help, it is WRONGLY stated:<br />«The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.»<br /><br />Correctly, it would be:<br />«The SUBTOTAL function ignores any rows that are hidden whenever a filter is applied in the worksheet, even if the filter does not affect the rows where SUBTOTAL is reading values.»Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-24017801531595415712011-12-14T11:06:50.900-05:002011-12-14T11:06:50.900-05:00What about using the DATEDIF function to determine...What about using the DATEDIF function to determine the number of days in each of these two dates and then subtract them?Patriciahttp://cpaselfstudy.comnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-10776015594584272382011-12-14T11:02:04.207-05:002011-12-14T11:02:04.207-05:00If you just convert the numbers to text, it does n...If you just convert the numbers to text, it does not put the 0 in front of the zipcode so even if you change it to text 2186 would still display as 2186 instead of 02186. The best alternative would have been to do Text to Columns when you imported the data. LEN is for when you have no control over the importing.Patriciahttp://cpaselfstudy.comnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-72397604359052729582011-12-12T18:15:53.632-05:002011-12-12T18:15:53.632-05:00This is great info! Thanks so much! If I have conv...This is great info! Thanks so much! If I have converted Years of service into that format, but I have time missed in between how can I subtract the time missed from Service Years? I have them as 54y 5m 25d - 5y 11m 23d. Any Suggestions?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-22039097271284360122011-12-05T14:15:39.739-05:002011-12-05T14:15:39.739-05:00You could also format the cells in the Zip Code co...You could also format the cells in the Zip Code column as a zip code. This would eliminate having to generate the formula. Or do you see this being used in another way?<br /><br />RogerRoger Fisherhttps://www.blogger.com/profile/01775747067646837581noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-79462884114426217952011-11-03T16:02:05.191-04:002011-11-03T16:02:05.191-04:00This is amazing software, I download SnagIt 10 fro...This is amazing software, I download SnagIt 10 from <a href="http://andraji.web.id/snagit-10-serial/" rel="nofollow">here</a> and it's very easy to use. I'll download this update too, hope there it will work better.Ardmanhttp://andraji.web.id/noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-25648978840223953672011-09-26T14:35:00.247-04:002011-09-26T14:35:00.247-04:00Hi there,
I just found this and thanks for clarif...Hi there,<br /><br />I just found this and thanks for clarifying that the whole spreadsheet will turn on. I was hoping to find a feature where I could make a single column go to 2 decimals. <br /><br />The example is I'm uploading customer payments that come in a txt file and I'm uploading as a csv. The $$ amount column needs the decimal but not the check/account/invoice number columns. <br /><br />Currently using Office 07, maybe they've added the single column feature in 10 or will in future versions to come.<br /><br />Thanks!<br />MikeAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-768418324850132512011-09-22T12:54:29.693-04:002011-09-22T12:54:29.693-04:00great tip - saves updating all those chart data ra...great tip - saves updating all those chart data ranges. cheersTips For Excelhttp://www.tips-for-excel.comnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-4822026713622492532011-08-12T17:46:54.431-04:002011-08-12T17:46:54.431-04:00If you want to learn a bit more about SUBTOTAL fun...If you want to learn a bit more about SUBTOTAL function you can check this link <br />http://prakashgusain.blogspot.com/2011/04/ms-excel-subtotal-function-and-filter.htmlAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-91631798522693033632011-08-05T06:05:34.310-04:002011-08-05T06:05:34.310-04:00Nice piece of software!
Sample Business Apology Le...Nice piece of software!<br /><a href="http://www.sampleletters.in/sample-business-apology-letter.html/" rel="nofollow">Sample Business Apology Letter</a>Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-66141292339641251062011-07-24T23:05:24.781-04:002011-07-24T23:05:24.781-04:00Sorry- I am not totally clear on what you are aski...Sorry- I am not totally clear on what you are asking as the 2 examples seem the same aside from the number of characters you are retrieving.<br /> Here is a very simple example of 7 nested IFs. =IF(B2=1,1%,IF(B2=2,2%,IF(B2=3,3%,IF(B2=4,4%,IF(B2=5,5%,IF(B2=6,6%,IF(B2=7,7%,"This is the 8th test")))))))<br />Personally, once I get above 3 or 4 IFs, I look for another solution. Can you not do Text to Columns on the name and break them apart by comma as the delimiter and then use an IF on them to retrieve the first 2 characters of each column or am I oversimplifying?Patriciahttp://cpaselfstudy.comnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-65288361232276189292011-07-23T08:26:07.546-04:002011-07-23T08:26:07.546-04:00As per excel 2007 it is 64 levels, I have used onl...As per excel 2007 it is 64 levels, I have used only 3 levels of "ifs" in excel 2007, more than this if statement is not allowing it shows an error "more levels of nesting than are not allowed" how to resolve this problem.<br /><br />Problem is:<br /><br />I am retrieving the first letter of each word in a cell. There are 3 formats<br /><br />example<br /><br />badri, narayana prasad<br />naveen, kumar<br />badri narayana, prasad<br /><br />=IF(LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1=2,CONCATENATE(MID(A5,1,1),", ",RIGHT(MID(A5,1,FIND(",",A5,1)+2))),IF(AND(RIGHT(MID(A5,1,FIND(" ",A5,1)-1))=",",LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1=3),CONCATENATE(MID(A5,1,1),", ",RIGHT(MID(A5,1,FIND(",",A5,1)+2))," ",MID(A5,FIND(" ",A5,FIND(" ",A5)+1),2)),IF(AND(RIGHT(MID(A5,1,FIND(" ",A5,FIND(" ",A5)+1)-1))=",",LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1=3),CONCATENATE(MID(A5,1,1)," ",RIGHT(MID(A5,1,FIND(" ",A5,1)+1)),", ",RIGHT(MID(A5,1,FIND(",",A5,1)+2)))," ")))<br /><br />i have done but now the client wants you see the name mcdonald and stanne means here first letter will not help instead of "mc" and "st" two letters must be retrieved. This also I have done<br />but here it is taking 4 levels more than this nesting problem. where is 7 levels????<br /><br />example<br /><br />McDonald, Robert<br />StAnnes, Thomas<br />McDonald, Robert Thomas<br />StAnnes, Thomas Robert<br /> <br />=IF(AND(LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=2,LEFT(A22,2)="MC"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))),IF(AND(LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=2,LEFT(A22,2)="ST"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))),IF(AND(RIGHT(MID(A22,1,FIND(" ",A22,1)-1))=",",LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=3,LEFT(A22,2)="ST"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))," ",MID(A22,FIND(" ",A22,FIND(" ",A22)+1),2)),IF(AND(RIGHT(MID(A22,1,FIND(" ",A22,1)-1))=",",LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=3,LEFT(A22,2)="MC"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))," ",MID(A22,FIND(" ",A22,FIND(" ",A22)+1),2))," "))))<br /><br />McDonald Robert, Thomas<br />StAnnes Thomas, Robert<br /><br />still this above format is pending now.<br /><br />In first looping more than 3 levels not allowing<br />In second looping more than 4 levels not allowing if use Excel 2007 but you told 64 levels how to resolve this problem.<br /><br />Now how to attach this 3 + 4 loops please give the solution.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-12129424709721202872011-07-18T11:32:49.620-04:002011-07-18T11:32:49.620-04:00There is problem with this method, when we try to ...There is problem with this method, when we try to name any unit containing SPACE it will fill that SPACE with "_". For example "PSG Excel" will be treated as "PSG_Excel". <br /><br />It cause problem while using the validation list. Similarly if the any name ends with a number it will cause the same problem. <br /><br />Do you foresee any solution to this?Prakash Singh Gusainhttp://prakashgusain.blogspot.com/noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-57769929176534386772011-05-11T08:04:23.954-04:002011-05-11T08:04:23.954-04:00So how cool is this? Ball State University mathem...So how cool is this? Ball State University mathematical sciences student class project produces music video on Pivot Tables. Check it out! http://blogs.office.com/b/microsoft-excel/archive/2011/05/06/rockin-out-with-pivottables.aspxAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-90574770865172448142011-04-23T06:10:23.560-04:002011-04-23T06:10:23.560-04:00Heya¡my very first comment on your site. ,I have ...Heya¡my very first comment on your site. ,I have been reading your blog for a while and thought I would completely pop in <br /><br />and drop a friendly note. . It is great stuff indeed. I also wanted to ask..is there a way to subscribe to your site via <br /><br />email?<br /><br /><br /><a href="http://newandusedcateringequipment.com.au/slicers.html" rel="nofollow">Slicers</a>nagohttps://www.blogger.com/profile/07217125072205604302noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-44324621772727146302011-04-23T06:07:25.226-04:002011-04-23T06:07:25.226-04:00Heya¡my very first comment on your site. ,I have ...Heya¡my very first comment on your site. ,I have been reading your blog for a while and thought I would completely pop in <br /><br />and drop a friendly note. . It is great stuff indeed. I also wanted to ask..is there a way to subscribe to your site via <br /><br />email?<br /><br /><br /><a href="http://newandusedcateringequipment.com.au/slicers.html" rel="nofollow">Slicers</a>nagohttps://www.blogger.com/profile/07217125072205604302noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-40706120045115770112011-04-22T13:04:23.611-04:002011-04-22T13:04:23.611-04:00I agree that many nested IF's can get pretty m...I agree that many nested IF's can get pretty messy. I was previously unaware of the "Index Match" function. I will have to look that up and see what type of solution it may offer. <br /><br />Thanks.<br /><br />C.W.Excel Statementhttp://www.oneclickcommissions.com/excel-statement.htmlnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-20993204285469342092011-04-18T14:51:47.851-04:002011-04-18T14:51:47.851-04:00I Have recently upgraded to 2010 and this article ...I Have recently upgraded to 2010 and this article is very useful for learning!Prakash Singh Gusainhttps://www.blogger.com/profile/05142222674787092917noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-78660744277650194752011-03-21T09:23:42.052-04:002011-03-21T09:23:42.052-04:00You can click in the name box and type A1. An alte...You can click in the name box and type A1. An alternative way is to select the square to the left of column A and above row 1. The latter selects every single cell in the spreadsheet. You can then select unhide. <br />Doubleclicking a column divider widens to the column to display the largest number of characters in that column.Patriciahttp://cpaselfstudy.comnoreply@blogger.com