Search results

Hi Ashish, You can do this using SUMIF easily. =SUMIF(A:A,"<="&D1,B:B)-SUMIF(A:A,"<="&D1-14,B:B) Assumptions Column A Contains Week Commencing Dates Cell D1 Contains Current Week Start Date Column B contains the figures that you want to add up. ~VijaySharma

Guity, What you have done is to Password protect the VBA code only. This is not the same as protecting your worksheet. Follow these steps. 1. Go to the sheet you want to protect. 2. Assuming Cell A1 and A2 contain your dropdown options, you want the end user to be able to change them...

Guity, This will not happen, what you are trying to achieve is to replace the value of the cell by 0 by using conditional formatting, however this is not the purpose of the feature. It can help you to identify which cells contains error and when the condition (is there any error) is met you...

Not yet, I will look into this today evening and revert.

Vijender, Welcome to Chandoo.Org Forums. It will help if you can define your requirement clearly.... We can only help if we understand what you need. ~VijaySharma

Shasi, your requirement is not clear... when North is selected from the dropdown, do you want only 1 city to be repeating OR do you need a dropdown in B1:B9999 to allow you to select. If the second option have a look at http://www.contextures.com/xlDataVal02.html ~VijaySharma

Sara, Welcome to Chandoo.Org Forums. Is it possible for you to upload your file for us to look at, see the sticky note on the top of Forums on how to upload your doc. ~VijaySharma

You may consolidate all raw data sheets into one single rawdata sheet and then use Pivot table to get the desired output. http://chandoo.org/wp/2010/02/19/excel-consolidate-data/ ~VijaySharma

Or else you may try using Pivot tables as well... ~VijaySharma

dldunn, Welcome to Chandoo.Org Forums. This is not directly possible within Excel provided ActiveX controls however you may use Third Party controls such as Flash Player Control to be able to do so. Here is one article which will allow you to embed Youtube videos on your excel sheet...

Hi Shajan, As stated above, you need to repeat the process. goto VB Editor and then locate the Sheet2 on project explorer. Click on the same and then Press F4 to get the properties window, and then set the Visible property to xlVisible. HTH ~VijaySharma

Guity, Open the VB Editor by pressing ALT+F11 from the keyboard or go to the Developer Tab and then click on VB Editor. If not already visible, go to the View menu and Click on Project Explorer. Within the project explorer locate the Excel sheet that you want to hide... Click on the...

Felix, Have a look at the article on Microsoft's site on this... though not using VLOOKUP but will help you in getting the output as you require.. http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx...

Thanks John... I can recommend visiting the ExcelIsFun page on Youtube, I dont know the name of the author; however there are pretty good videos on using complex and easy formula's there. http://www.youtube.com/user/ExcelIsFun Do have a look.. ~VijaySharma

Are you able to upload your work in progress somewhere for us to look at... it will be a lot easier having a look and suggesting the best course of action... ~VijaySharma

smc, You can do this by using Named Ranges... Assuming your raw data is in a sheet called as RawData, Define names using Offset taking into account the entire data range. =OFFSET(RawData!$A$1,0,,COUNTA(RawData!$A:$A)-1,COUNTA(RawData!$1:$1)) Now on your consolidation sheet, you can...

You can use the below to get this done... =SUM(INDIRECT(E2&"$C$2:$C$30000")) Cell E2 contains the text that you want to replace, and should be a sheet name else you will get error in the formula. ~VijaySharma

dahshans, Have a look at the below link... http://www.mrexcel.com/forum/showthread.php?t=399784 Contains the UDF for search and replace using VBA code.. SUBSTITUTE can be nested only 8 times ~VijaySharma

Hi darshans, Welcome to Chandoo.Org Forums. The below formula will get rid of the SPACE and PERIOD. =SUBSTITUTE(SUBSTITUTE(B1," ",""),".","") ~VijaySharma

Hi John, Yes, TRANSPOSE works on a array... here is the copy paste from Excel Online help on the same... The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula (array formula: A formula that...

In excel 2007, right click inside your Pivot table and then click on PivotTable Options... Go to the Display Tab make a check mark on Classic PivotTable layout (enables dragging of fields in the grid) Try this out and let us know if this is what you wanted. ~VijaySharma

Arun, Go to the Home tab on the ribbon. Then look for the Editing group on the right hand side. Click on Find and Select drop down, and Click on Selection Pane. ~VijaySharma

Yalezi, Welcome to Chandoo.Org forums. Do have a look at http://www.cpearson.com/excel/VBAArrays.htm HTH ~VijaySharma

Guity, If you have the categories created, you need a simple VLOOKUP to complete this. ~VijaySharma

Hi Nilesh, Welcome to Chandoo.Org Forums. You may double click on the data cells and the pivot table will generate the raw data in a separate sheet. This however is dependant on few settings for the pivot tables. I would recommend double clicking on the grand total row OR columns HTH...