Search results

Hi Ananthram, Try this, Press Alt+HLN keys then select use formula to formate and put the below formula: =COUNTIF(A:A,A1)>1 and then select color to highlight the cells. This will highlight the duplicate values in column A. P.S. I have done this in office 2007 Thanks & Regards...

Many Thanks Deb, I got it. Regards, Anupam Tiwari

Hi Deb, You are genius, thanks a lot for such a nice formula. Regards, Anupam

Hi Kris, Thanks

Hi Suresh, You can also try below: Just select your column A and Press Alt+HLN key combination and select option "Use formula to format cell". write any one of the below formula: =ISTEXT(A1)=True , and select color to format. Or =Type(A1)=2 , and select color to format. The above...

Hi Praveen, You can try below formula which is working perfectly for me: =IF(MIN(xyz)>0,MIN(xyz),SMALL(xyz,2)) Thanks & Regards, Anupam Tiwari

Hi Praveen, Just tweak your formula like below: =IF(MIN(xyz)>0,MIN(xyz),"Min value is 0 only") Hope this will work for you. Thanks & Regards, Anupam Tiwari

Hi Kaushik, Luke's formula looks more dynamic however you can also try the below formula. =INDIRECT(CHAR(62+ROW(A$2)+COLUMN(A2))&ROW(A2)) Thanks & Regards, Anupam Tiwari

Hi ahhhmed, If I am not wrong, you can use "COUNTA()" formula to know the count of non empty cells in a row. =COUNTA(1:1) Put the above formula in Cell A2 that will give you count of non empty cells in 1st row. However, the below formula will give you the count of empty cells in a row...

Hi jskushawah, I forgot to mention in above reply that when you put the above formula in conditional formating, first select the Column A and then use above formula in conditional formating. Thanks & Regards, Anupam Tiwari

Hi jskushawah Please follow below step which can probably solve your problem: 1. If your data has header row then in J2 cell insert the sum formula "=SUM(B2:I2)" and drag it below to the last row of your data. 2. Now goto the condtional formating option by pressing Ctrl+NHL for 2007 and...

Hi PeterH, To go to "Workbook close event", follow the below spteps: 1. Press Alt+F11 2. Press Ctrl+R if project window is not visible. 3. In Project window, you will see Microsoft Excel Object which will have worksheets name inside your workbook and last as ThisWorkbook. 4. Double click...

Hi PeterH, I have myself prepared a small vba code for you to fullfil your requirement however this is working fine for single formula cell. Copy the below code to your module and just run the defineKey code only. Sub defineKey() Application.OnKey "{F9}&#34...

Hi Chintan, You can try the below code that probably could work for you, it will change the cell reference to relative reference, absolute reference vice versa as per your need, then you can paste your formulae to other cells. Sub Change_Cells_To_Absolute_Relative() Dim RdoRange As Range...

Hi Hui, Thank you Regards, Anupam Tiwari

Hi Luke, Thanks a lot for this valuable information. I will try my best and will be active on this forum as I love this forum a lot. This is the only forum where I spend most of my time reading others posts and also try to provide the answer where I feel myself confident. Its my pleasure...

Good Evening to All, Could anyone please advise me how to become an Excel Ninja and what is the eligibity to become an Excel Ninja? Thanks for your time. Thanks & Regards, Anupam Tiwari

Hi Luke, Thanks a lot for your explaination. Your formula is working nice and giving me last MAX value in a particular column. However,I will give time to understand each part of your formula as its complicated for me to understand it at once. If I find any problem, I will get back to...

Hi Narayan, I have tried your above Array Formula "=ADDRESS(MAX((B1:D9=D10)*ROW(B1:D9)),MAX((B1:D9=D10)*COLUMN(B1:D9)))", its not showing me the correct address of the cell if the range has more then one MAX value e.g B6 = 100 and C2 = 100, in this case it gives the output as $C$6 but the...

Hi Oscar, Unable to clearly understand your query. Could you please share your data sheet so accordingly I can try to write code for you or other Excel experts can help you out. Regards, Anupam Tiwari

Welcome aparvez007 Regards, Anupam

Hi aparvez007, Remove double quotes from the numbers e.g "1" should be like 1, then your above formula will work correctly. Let me know if still you face problem. Thanks & Regards, Anupam Tiwari

Hi Oscar, As per my understanding of your query, I have written below code for you: Sub Consolidate_Range() ThisWorkbook.Activate Dim sht As Worksheet i = 0 For Each sht In Worksheets If sht.Name <> "consolidate" Then sht.Activate lc = Cells(1, 1).End(xlToRight).Column lr =...

Hi Jayadev, I have tried in many ways by putting AND/OR in SUMIF() but could not find result. I think AND/OR can not come in SUMIF() however will wait for our Excel Gurus to clear our doubts on this situation. Thanks Anupam Tiwari

Hi Karyn, I have put your formula in my excel sheet and checked the conditions as per below: In Cell(C28) i have added list which has values 10, 20 ,30 and in Cell(C37) added a list which has values "no reinstatement", "1 x Reinstatement" and "2 x Reinstatement". Keeping the C28 value to 10...