http://chandoo.org/forums/forum/ask-excel-questions Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Fri, 24 May 2013 18:42:34 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/delete-rows#post-104629 Fri, 24 May 2013 18:14:33 +0000 brightyoyo 104629@http://chandoo.org/forums/ <p>Hi SirJB7<br /> I was able to get the macro working, but not able to get it to delete the row based on the first letter in the cell. Any help would be appreciated.</p> <p>Thank You </p> http://chandoo.org/forums/topic/using-vlookup-on-a-value-that-changes-each-month#post-104628 Fri, 24 May 2013 17:59:08 +0000 SirJB7 104628@http://chandoo.org/forums/ <p>Hi, greykitten!<br /> If I didn't get it wrongly why don't you use this formula, as you already have the list of names in column G?<br /> =VLOOKUP(G5,$B$5:$G$17,2,FALSE)<br /> And copy it down as needed.<br /> Regards! </p> http://chandoo.org/forums/topic/negative-value-to-make#post-104627 Fri, 24 May 2013 17:53:26 +0000 SirJB7 104627@http://chandoo.org/forums/ <p>Hi, jack999!<br /> Apply this custom format:<br /> #0.00" debit";#0.00" credit"<br /> Regards! </p> http://chandoo.org/forums/topic/negative-value-to-make#post-104626 Fri, 24 May 2013 17:41:29 +0000 jack999 104626@http://chandoo.org/forums/ <p>I am using this (=J4+D56-K4-D57) formula and getting negative value, but I want this negative value to make credit </p> <p>suppose if the answer is -59700.00 I want the value as 59700.00 credit<br /> if the result is 59700.00 then 59700.00 debit<br /> can any one give a solution. </p> http://chandoo.org/forums/topic/populate-cell-with-value-from-dynamically-generated-table-on-a-vba-userform#post-104625 Fri, 24 May 2013 17:39:03 +0000 NARAYANK991 104625@http://chandoo.org/forums/ <p>Hi ,</p> <p>Sure. Do the following :<br /> <pre><code>Private Sub ListBox1_Click() Unload Me End Sub</code></pre> <p>Put the above code in the Userform section.<br /> <pre><code>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) UserForm1.ListBox1.ControlSource = Target.Address(0, 0, xlA1) UserForm1.Show End Sub</code></pre> <p>Put the above code in the Sheet section.</p> <p>Now , when ever you click on a cell , the Userform will pop up ; when you select any row in the form , the BoundColumn property will decide which column value is to be used ; since the ListBox ControlSource is being set to the Activecell , the value will put in that.</p> <p>The ListBox1_Click procedure ensures that when you click , the Userform will disappear.</p> <p><strong>The only problem with this is that you cannot click on a cell which is already filled with data ; you can only click on an empty cell !</strong></p> <p>Narayan </p> http://chandoo.org/forums/topic/using-vlookup-on-a-value-that-changes-each-month#post-104624 Fri, 24 May 2013 17:37:41 +0000 greykitten 104624@http://chandoo.org/forums/ <p>Hi,<br /> Is VLOOKUP capable of returning a number on the contents of a particular cell instead of a particular name? I looked through various articles on Chandoo but couldn't find an answer. My VLOOKUP formula works great when I use it like this:</p> <p>=VLOOKUP("Juno",$B$5:$G$17,2,FALSE) to return the number of customers for Juno. In the file below, that works fine as long as cell G5 keeps Juno as the contents permanently.</p> <p><a href="https://hotfile.com/dl/224302895/9661015/VLOOKUP_Example.xls.html">https://hotfile.com/dl/224302895/9661015/VLOOKUP_Example.xls.html</a></p> <p>But in my work, the name in cell G5 will change each month so including the name won't work. I need the VLOOKUP to retrieve the number of customers on the adjacent cell even when the name in cell G5 changes. Can you use a cell reference in VLOOKUP so that the formula returns the value of whatever name happens to be in that cell that month? Is VLOOKUP even the right formula to address that need? Your help is greatly appreciated. Thank you. </p> <p>Thanks,<br /> greykitten </p> http://chandoo.org/forums/topic/populate-cell-with-value-from-dynamically-generated-table-on-a-vba-userform#post-104623 Fri, 24 May 2013 17:10:23 +0000 dronka 104623@http://chandoo.org/forums/ <p>Wow, that is too cool! So, all of that works great. Now I'm wondering if the control source can be whichever cell was selected before I pulled up the userform. </p> http://chandoo.org/forums/topic/vlookup-multiple-worksheet-question#post-104621 Fri, 24 May 2013 16:55:00 +0000 NARAYANK991 104621@http://chandoo.org/forums/ <p>Hi Prajith ,</p> <p><strong>sheetlist</strong> is a named range i.e. it is a range which has been given a name. In this case , it refers to the range Sheet1!$H$3:$H$6.</p> <p>What this range contains is the names of the sheets which have the data tables in them viz. Sheet2 , Sheet3 , Sheet4 and Sheet5.</p> <p>The range $A$1:$C$4 is a pre-defined range , which contains data ; this is the same on every one of the sheets Sheet2 , Sheet3 ,... Since it is specified within the formula , there is no way that it can vary from one sheet to another ; what you can do is use the largest possible range e.g. $A$1:$C$577. Even if cells in this range are blank , it should not matter. Where ever the value being looked up is present , the corresponding data from that sheet will be returned.</p> <p>Narayan </p> http://chandoo.org/forums/topic/vlookup-multiple-worksheet-question#post-104617 Fri, 24 May 2013 16:44:41 +0000 prajithp 104617@http://chandoo.org/forums/ <p>Hi Narayan</p> <p>I didnt understand how you created the "sheetlist".Also would like to know if data are more in one sheet and less in other sheet. will this formula work.</p> <p>INDIRECT("'"&amp;INDEX(sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&amp;sheetlist&amp;"'!$A$1:$c$4"),A4)&gt;0),0))&amp;"'!$A$1:$c$4") </p> http://chandoo.org/forums/topic/using-vba-to-show-segmented-data-based-on-dynamic-cell-value#post-104613 Fri, 24 May 2013 16:17:41 +0000 SirJB7 104613@http://chandoo.org/forums/ <p>Hi, The Doctor!</p> <p>Give a look at this file:<br /> <a href="https://dl.dropboxusercontent.com/u/60558749/Using%20VBA%20to%20show%20segmented%20data%20based%20on%20dynamic%20cell%20value%20-%20projection%20problem%20%28for%20The%20Doctor%20at%20chandoo.org%29.xlsx" rel="nofollow">https://dl.dropboxusercontent.com/u/60558749/Using%20VBA%20to%20show%20segmented%20data%20based%20on%20dynamic%20cell%20value%20-%20projection%20problem%20%28for%20The%20Doctor%20at%20chandoo.org%29.xlsx</a></p> <p>It's your original one with the addition of the range LittleTable3.</p> <p>Just advise if any issue.</p> <p>Regards! </p> http://chandoo.org/forums/topic/excel-prob#post-104612 Fri, 24 May 2013 16:08:08 +0000 Faseeh 104612@http://chandoo.org/forums/ <p>Hi pjsrkrm,</p> <p>Welcome to the fourms! ...You posted two files to dropbox and then??? :)</p> <p>Faseeh </p> http://chandoo.org/forums/topic/excel-prob#post-104610 Fri, 24 May 2013 16:04:11 +0000 pjsrkrm 104610@http://chandoo.org/forums/ <p>Sir, I have posted in drop box.<br /> File names : </p> <p>1. master.xls<br /> 2. kot-bill1.xls </p> http://chandoo.org/forums/topic/excel-aptitude-test-inside-a-workbook#post-104609 Fri, 24 May 2013 15:56:23 +0000 DaveTurton 104609@http://chandoo.org/forums/ <p>Hi Narayan</p> <p>That is a nice idea, I'll add it in</p> <p>Thanks </p> http://chandoo.org/forums/topic/excel-aptitude-test-inside-a-workbook#post-104608 Fri, 24 May 2013 15:50:37 +0000 NARAYANK991 104608@http://chandoo.org/forums/ <p>Hi Dave ,</p> <p>I have one suggestion , which may be partly because I love problem solving , but also because I believe that the most important quality in an employee is the willingness to try and solve problems , given that time is not a constraint.</p> <p>For a specific example , let us consider question 16 , where you want the candidates to copy and paste ; I would turn this question slightly inside out ; use the copy + paste special multiply facility to put some figures in Table 2 ; ask the candidates to write down how the second table could have been filled.</p> <p>To answer this question , the candidate cannot do anything but think ! Either they know or they have to think.</p> <p>Narayan </p> http://chandoo.org/forums/topic/partial-paste-special#post-104607 Fri, 24 May 2013 15:49:02 +0000 SirJB7 104607@http://chandoo.org/forums/ <p>Hi, Sorabh Gupta!</p> <p>Just as an idea, since I agree with what posted regarding the impossibility (not the extreme difficulty) of performing what you want, there might be an alternative solution but requires that the external link always be open and is the use of the INDIRECT function.</p> <p>Should that be suitable for you?</p> <p>Regards! </p> http://chandoo.org/forums/topic/using-vba-to-show-segmented-data-based-on-dynamic-cell-value#post-104606 Fri, 24 May 2013 15:42:07 +0000 NARAYANK991 104606@http://chandoo.org/forums/ <p>Hi ,</p> <p>Can you check your file here ?</p> <p><a href="https://www.dropbox.com/s/9jjqrtmt6muv7m6/projection%20problem.xlsx" rel="nofollow">https://www.dropbox.com/s/9jjqrtmt6muv7m6/projection%20problem.xlsx</a></p> <p>I have put in the formulae to get the dates , based on some dates prior and some dates after the entered date. You can change the fixed values of 3 and 7 to get the periods prior and after.</p> <p>Once the dates are available , the other two columns can be filled in using a simple VLOOKUP.</p> <p>Narayan </p> http://chandoo.org/forums/topic/calculator-for-website-using-google-spreadsheets#post-104605 Fri, 24 May 2013 15:24:38 +0000 iram 104605@http://chandoo.org/forums/ <p>Hi guys,</p> <p>I've created a very simple spreadsheet to calculate property tax amount. The user needs to input property price. Output is calculated in another column based on a simple formula (property price * city tax rate).</p> <p>Here is the problem.</p> <p>I am trying to put it up on our internal website so that staff/employees can input the value. If I embed the entire spreadsheet, regardless of the permission I give them, they cannot enter values in it.</p> <p>If I publish it as a list, then there are too many steps involved to get the output:<br /> 1. Edit<br /> 2. Enter value<br /> 3. Submit<br /> 4. Refresh</p> <p>We use this calc frequently so it needs to be a quick 2-click process. Could you please help me figure out a simple way of doing this?</p> <p>Really appreciate everyone's time and help!</p> <p>Cheers,<br /> Iram </p> http://chandoo.org/forums/topic/using-vba-to-show-segmented-data-based-on-dynamic-cell-value#post-104603 Fri, 24 May 2013 15:13:58 +0000 The Doctor 104603@http://chandoo.org/forums/ <p>I must be using this incorrectly, it returns a #Value! when I paste the code in. </p> http://chandoo.org/forums/topic/populate-cell-with-value-from-dynamically-generated-table-on-a-vba-userform#post-104602 Fri, 24 May 2013 15:12:53 +0000 NARAYANK991 104602@http://chandoo.org/forums/ <p>Hi ,</p> <p>Let us assume the following have been set in the ListBox properties page :</p> <p>1. RowSource : Sheet1!A2:C6</p> <p>2. ColumnCount : 3</p> <p>3. ControlSource : K1</p> <p>4. BoundColumn : 2</p> <p>The ListBox will now display 5 rows and 3 columns of data ; suppose you select the third row , then the ControlSource , which is cell K1 on the worksheet , will display the contents of cell B4 , since B4 is the second column in the displayed range ( BoundColumn = 2 ) , and 4 is the third row in the displayed range.</p> <p>Narayan </p> http://chandoo.org/forums/topic/conditional-formatting-cf-and-pivot-tables-pt#post-104601 Fri, 24 May 2013 15:05:17 +0000 bobhc 104601@http://chandoo.org/forums/ <p>Good day polarisking</p> <p>Have a look at this link, if it does not help perhaps you could upload your work book.</p> <p><a href="http://www.ablebits.com/office-addins-blog/2011/05/23/excel-conditional-formatting-pivottables/" rel="nofollow">http://www.ablebits.com/office-addins-blog/2011/05/23/excel-conditional-formatting-pivottables/</a> </p> http://chandoo.org/forums/topic/lock-object-in-excel#post-104600 Fri, 24 May 2013 14:41:08 +0000 Hui 104600@http://chandoo.org/forums/ <p>Nipendra </p> <p>With all objects in excel including cells, ranges, sheets, charts, and inserted objects<br /> Right click on the object,<br /> Properties or Format<br /> Protection<br /> Lock Cell/Object</p> <p>Ten goto the Review tab<br /> Protect Sheet<br /> Assign the level of protection and passwords if required. </p> http://chandoo.org/forums/topic/excel-aptitude-test-inside-a-workbook#post-104599 Fri, 24 May 2013 14:26:25 +0000 DaveTurton 104599@http://chandoo.org/forums/ <p>I've updated it <a href="https://docs.google.com/file/d/0ByiZqUlQMjxhQjV3aXQ5U3g3OXc/edit?usp=sharing" rel="nofollow">https://docs.google.com/file/d/0ByiZqUlQMjxhQjV3aXQ5U3g3OXc/edit?usp=sharing</a></p> <p>Now had 17 questions which I think just about cover what I need to gauge a basic understanding of what I need from them. Very simple stuff but at least it shows whether or not they have used excel for more than just putting pretty colours in cells. </p> http://chandoo.org/forums/topic/conditional-formatting-cf-and-pivot-tables-pt#post-104596 Fri, 24 May 2013 14:17:32 +0000 polarisking 104596@http://chandoo.org/forums/ <p>I'm using MS Office Professional Plus. I'd like to invoke the Apply Rule to . . . option when CF a PT. But no matter how I try, I never get the option to CF based on<br /> - Selected Cells<br /> - All Cells showing "XXX" values<br /> - All Cells showing "XXX" values for "YY" </p> http://chandoo.org/forums/topic/excel-aptitude-test-inside-a-workbook#post-104595 Fri, 24 May 2013 14:15:57 +0000 Indian 104595@http://chandoo.org/forums/ <p>Kudos...I loved solving them (Don't know why) </p> http://chandoo.org/forums/topic/create-a-dynamic-range-name-without-offset-that-includes-rows-inserted-in-betwee#post-104590 Fri, 24 May 2013 13:49:54 +0000 krislopmar 104590@http://chandoo.org/forums/ <p>Please, delete this post. I dont know why but now its working perfectly. I think when adding the range of the name without $, it all got messed up. </p> <p>Thanks for the forum, great help always! </p> http://chandoo.org/forums/topic/create-a-dynamic-range-name-without-offset-that-includes-rows-inserted-in-betwee#post-104589 Fri, 24 May 2013 13:37:01 +0000 krislopmar 104589@http://chandoo.org/forums/ <p>Hello, I have the following set of data:<br /> 1 AGENCIES<br /> 2 German Agency 1<br /> 3 German Agency 2<br /> 4 German Agency 3<br /> ..<br /> 10 Total Germany<br /> 11 Swiss Agency 1<br /> 12 Swiss Agency 2<br /> ...<br /> 15 Total Switzerland<br /> ETC</p> <p>In order to create a list in another sheet that allows me to choose any from the German agencies I have created a dynamic range $A$2:$A10, but I have to add another agency (for example between number 2 and 3) the dynamic range doesnt become $A$2:$A11.<br /> The problem is that there is a long list of agencies with subtotals per country and for each one of the countries I need a different range name set up. The only way I modify this long list is adding agencies by inserting rows where it corresponds. I dont know how to fix this. Any help will be welcome and very much appreciated!! Im using Excel 2007. Many thanks! </p> http://chandoo.org/forums/topic/populate-cell-with-value-from-dynamically-generated-table-on-a-vba-userform#post-104588 Fri, 24 May 2013 13:22:35 +0000 dronka 104588@http://chandoo.org/forums/ <p>Great! I've been able to create a userform that displays multiple rows and columns of data from Sheet 1.</p> <p>Now I'm wondering how to select one of the rows in the user form and transfer a data element in that row into a cell on Sheet 2. Narayan mentioned the BoundColumn property. I did some searches on that and found a lot of complex code. Is there an "easy" way to do this? </p> http://chandoo.org/forums/topic/excel-aptitude-test-inside-a-workbook#post-104585 Fri, 24 May 2013 12:35:17 +0000 DaveTurton 104585@http://chandoo.org/forums/ <p>I have made a start, this is the sort of thing I am thinking of <a href="https://docs.google.com/file/d/0ByiZqUlQMjxhaW96bXBzU01Wa1E/edit?usp=sharing" rel="nofollow">https://docs.google.com/file/d/0ByiZqUlQMjxhaW96bXBzU01Wa1E/edit?usp=sharing</a> </p> http://chandoo.org/forums/topic/how-to-find-and-display-one-numerical-value-in-a-row-spanning-multiple-columns#post-104582 Fri, 24 May 2013 12:17:29 +0000 shrivallabha 104582@http://chandoo.org/forums/ <p>flywhiz,</p> <p>There are some formulas which will also work just for your info.<br /> <strong>=HLOOKUP(MAX(Budget!C46:Z46),Budget!C46:Z47,2,0)</strong><br /> and ubiquitous INDEX/MATCH<br /> <strong>=INDEX(Budget!C47:Z47,1,MATCH(MAX(Budget!C46:Z46),Budget!C46:Z46,0))</strong> </p> http://chandoo.org/forums/topic/price-breaks-based-on-volume-and-a-threshold#post-104581 Fri, 24 May 2013 12:07:50 +0000 sallan 104581@http://chandoo.org/forums/ <p>Thanks for this I will take a look over the weekend</p> <p>Regards</p> <p>sam </p>