Search results

With current example on file, pls refer to sheet 1, J16 cell it has 8.597,77 which is "text" and not "number", because of "," in it. in case you remove comma, it will reduce the decimals to 2 positions and value is changed to 8.60 to retain 8.59777, increase the decimals in formatting...

Hi, I could not understand interval column, and it would be easier if you put the expected output Available time for some of the rows/agents manually, and upload the file. regards, Prasad DN

So, are you expecting the answer in B4 to be in % format? can you share some example file, please.

Hi, Will this work for you? formula in b4: =B2+(B3*B2) Regards, Prasad DN

BTW, are you hard on formulas or a VBA macro solution is fine??

Hi, Question is quite clear and simple but solution is not that simple. I am still thinking for one. I just posted to say good and clear explanation of requirements. Regards, Prasad DN

Hi, Pls share your code after amending the changes suggested by me and then with incorporating changes suggested by Luke. Regards, Prasad DN

Hi, It would mean the person who enters the info , will not have option to edit the same! or you want to save, once the user enters info and saves the sheet, the above rules should apply? Regards, Prasad DN

Hi JD, one option I think of is list all your 20 column names in one column in a separate sheet. Or even make an array, And use a for loop or while loop to search each of the word from that column. something like: Sub CopyColumnByTitle() Dim SearchCols(20) As String SearchCols(0) = "Entity"...

Hi Mudassar, In cell you have formula:INDIRECT(ADDRESS($A2,I$1,,,$A$1)) Try: =Hyperlink("[filename.xlsx]" & "sheetname!" & INDIRECT(ADDRESS($A2,I$1,,,$A$1)),"Go") Note: The file should be saved, File name should be in double quotes "". The sheetname also can be made dynamic with little tweeqs...

Hi, I am not sure if I could get to your requirement. The best I could was to get the required data in one table (PT), but the format is slightly different. the columns gets split in two rows, Status like Before Time will have sub group of 3 criticalities, Delay will have 3 columns of...

The OP wants to remove d&t, I think its just a mere confusion, it should be left(A1,23). :)

Hi, Here is one more alternate: 1. Use Find and Replace window, by calling Ctrl+H 2. Type "Daily production report *" in Find what field 3. Type "Daily production report" in Replace with field 4. Click Replace All button. 5. Say.. Wohoo!! it is done, :) Regards, Prasad DN

Sorry, the suggested solution from me doesnt work properly, when you show the next set of data, no doubt the checkbox updates points to next set of data in tblSOURCE but the previous set of other set of data would result in #N/A. :( Regards, Prasad DN

Hi, The checkbox form control linkedcell cannot be dynamic. it would not take indirect, address or offset functions. At the same time you can still use it dynamically. Lets say the checkbox are linked to column L2:L6, as in the screen shot. The formula in tblSource, Cell D2, should be...

Hi, Ctrl+[ would work even if it is referred from other sheet, atleast for cases I tested. To address your question, can you explain what would be in your $A2 and I$1 cells? Regards, Prasad DN

Hi Wk, You can try the below CSE formula: =VLOOKUP(A2&B2,CHOOSE({1,2},'Table 1'!A:A&'Table 1'!B:B,'Table 1'!D:D),2,0) on pressing Cntr+Shift+ Enter, excel will automatically, add {} braces. You can get the detailed expanation in the following article...

Hi, Many congratulations Narayan!! I got amazed with this site for the first time when I saw Chandoo's dashboard of 10,000th comment. And particularly the small info of analysis which says which dates there were 0 comments. And thats when I became fan of this site. Now, I think it is...

Hi Neeraj, When you link two or more excel files, and insert rows in original file, the linked file will get the new location automatically updated, but it would not change the position in itself. Example: Book1.xlsx (sheet1 A1 cell) is linked to Book2 (sheet1 A1 cell). The formula in book1...

Hi, Very nice formula Asheesh! Just replace Trim with substitute function to get the commas as well. example: =IF($E10="","",SUBSTITUTE("("&G10&" PS No "&I10&", "&J10&", "&K10&", "&L10&", "&M10&", "&N10&", "&O10&", "&P10&")",", , ","")) Regards Prasads DN

Hi, In continuation to SM's formula, here is another alternate: =IFERROR(VLOOKUP(C8,$O$3:$P$7,2,0),50) Regards, Prasad DN

I was going thru your file and got one more question in mind, what if the related ID is also not have any cluster.... like related ID = 4. Regards, Prasad DN

Hi, can you help me understand this piece of your requirement.. "Should there be more than one related item, then I should have one per related item in the count ifs or sumproduct." Also, what about those records where there is no cluster nor related ID? Regards, Prasad DN

Hi Khurrum, You have done very good job with spreadsheet. I used your own formula to get you your results. :) Type the below formula in Row C35: =IF(C33>=(LARGE($C$33:$R$33,5)),C33,"") and drag upto whichever column you need. ;) Regards, Prasad DN

Hi Haz, very neat technique... the only limitation is you cannot send cell reference as parameter/arguments. Ex Result is hard coded to F6, and if I need to do similar action for F6 to F10 etc then i Need to define Result1, Result2 etc... Do you think there is any improvement for this...