Search results

3 Replies within a minute :cool::)

Hi, You can track the cells having circular reference by going to Formula Tab > Formula Auditing Menu > Error Checking Drop down > Circular Reference. If the status bar is showing only Circular Reference and no cell address, it means your active sheet does not contain any circular references...

Hi G, Thanks for the kind words and good wishes for your tomorrow's blog. I am sure you've found it by now :) Regards,

You are welcome dear. Keep Exceling.

It is cell border with white color :) Go to Format Cell > Border tab > Select a Thick Border and set the color to white. Regards,

Hi, Try the following steps: Go to Data Validation > Input Message Tab > Input Message (type your message here) > Check mark "Show input message when cell is selected". Regards,

Hi G, Awesome. Thanks for sharing. Regards,

Hi, Power Query Solution: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Typed = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"REPT", Int64.Type}}), Repeated = Table.TransformColumns(Typed, {"REPT", each List.Numbers(_,_,0), type list}), Expanded =...

Many Thanks Sachin for the the remembrance. Currently I am not getting much time for the forum due to busy schedule in Ramadan. Missing the forum. Regards,

Hi, Please try this: =IFERROR(INDEX(DATA!$C$2:$C$14,MATCH(FORMULA!A2&FORMULA!B2,INDEX(DATA!$A$2:$A$14&DATA!$B$2:$B$14,),0)),"") Regards,

Hi, Can you please post a sample file with some data and expected result? Regards,

Hi, Or you can use same logic in AGGREGATE function with just Enter: =AGGREGATE(15,6,1/(($A$2:$A$16=$A2)/($B$2:$K$16<>""))*$B$2:$K$16,1) Regards,

Hi, Yes it was just a wild guess as I haven't seen your data. If you can post a sample file, chances are high you may get more targeted answers. Regards,

Hi, Not sure, this is just a stab in the dark! =IFERROR(1/(1/IF(ROWS(BH$2:BH2)<='Test Chart'!$BD$2,INDEX(INDIRECT(BH$1),SMALL(IF(SingleMulti='Test Chart'!$BC$2,ROW(SingleMulti)-ROW($AU$2)+1),ROWS(BH$2:BH2))),"")),"") Regards,

Hi John, You are great :awesome: Both formulas are astonishing and beyond my understanding. Regards,

Hi Thomas, Glad it helped. Please share example. Regards,

Hello Thomas, Good day... Please try these D2: =IFERROR(LEFT(A2, FIND(" ",A2))+0,"") E2: =TRIM(IF(ISNUMBER(LEFT(A2)+0),MID(A2, FIND(" ",A2),LEN(A2)),"")) F2: =TRIM(IF(ISNUMBER(LEFT(A2)+0),"",A2)) Copy down all... Regards,

Here is the list I found useful and frequently asked: INDEX: An alternative to array (CSE) formulas: https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas/ MAXIF Non Array: https://www.excelforum.com/tips-and-tutorials/817784-maxif-without-using-an-array-formula.htm...

Hi G, Thank you so much for the explanation of these M Functions. PQ is so powerful tool, I will do more research on it. The more I use (PQ / Excel), the more I learn. The more I learn, the more I realize, the less I know. @ all, Please accept my sincere gratitude for your help. Regards,

Hi G, Fantastic, that did the trick much faster. I can see you have added custom column. Can you please explain your steps in Plain English (just for my learning purpose), as I am new to PQ and I am not getting this M language. Thank you so much and have a great day. Regards,

Hi Marc & Chihiro, Both solutions are working great and doing the job in no time. Thank you so much, Have a good day. @Marc, your code is much shorter, and you are on time as always :) Hi Asheesh and Hareesh, Thank you guys. Both codes are working. I appreciate your time and help. Take Care...

Hi to all, Guys you are amazing, that was quick. I will check all posted solutions with source data, and will give my feedback. Regards,

Hi Asheesh, Yes you are right.

Hello Guys, Good day, I am trying to split comma delimited data in column K & L and copy relevant data to new rows. for Example K3 contains 50, 50 and L3 having 997795, 993795, I want to split both cells in 2 rows, so K3 should be 50 and remaining 50 along with all data in row 3 will be copied...

Hi, Please try this: =INDEX(Datasheet!$A$2:$A$7,MATCH(F2&"|"&P2,INDEX(Datasheet!$I$2:$I$7&"|"&Datasheet!$B$2:$B$7,),0)) Regards,