User Profile
JMB17
Bronze Contributor
Joined May 20, 2020
User Widgets
Recent Discussions
Re: VBA code for copying from sheets and append it in a new work sheet or another workbook.
Jyothi1984 Perhaps something like this? This code just copies the worksheets used ranges, but if you want to limit it to only A2:C8, then just change this piece: wksht.UsedRange.Copy to wksht.Range("A2:C8").Copy Sub test() Dim destWksht As Worksheet Dim wksht As Worksheet Set destWksht = Workbooks.Add(xlWBATWorksheet).Worksheets(1) For Each wksht In ThisWorkbook.Worksheets wksht.UsedRange.Copy destWksht.Cells(destWksht.Rows.Count, 1).End(xlUp).Offset(2, 1) Next wksht End SubRe: Using Sumwith sumifs for multiple critiera
Karim_Sakhi I believe sumifs will group your criteria like this: (Utility AND Asia AND Baden) OR (Game AND Europe AND WenCal) But, are you actually trying to do this? (Utility OR Game) AND (Asia OR Europe) AND (Baden OR WenCal) Perhaps try: =LET(criteria1, BYROW(--(A2:A28=K2:L2), LAMBDA(rw, SUM(rw))), criteria2, BYROW(--(B2:B28=K3:L3), LAMBDA(rw, SUM(rw))), criteria3, BYROW(--(C2:C28=K4:L4), LAMBDA(rw, SUM(rw))), SUM(D2:D28*criteria1*criteria2*criteria3))Re: Cell not populating correctly based on formula
Mickalopagus An oversight on my part. It also needs to verify I15 is not N/A (the #value is likely because I15 is N/A and it's trying to add 182). Try this: =IF(J16="completed", "Done", IF(I16<>"", "Pending", IF(AND(I15<>"", ISNUMBER(I15)), I15+182, IF(I15="N/A", H$3+60, ""))))Re: Excel formula
Neda1978 If your cell is showing 00:00, then you need to change the format from a time format (hh:mm) to a number format. In Excel, the integer part of a number is days and the decimal is time. An hh:mm format will only display the decimal part, which is zero, so you need to change the format to see the integer.Re: Converting Formulas
If it has to be index/match, then you could also try: =INDEX($P$4:$R$12, MATCH(J3, $O$4:$O$12), IFNA(MATCH(B3, {"u","g"}, 0), 3)) The only caveat is that if is possible someone might insert a column in between columns P and R, then will it still return the desired result (because your column reference is based on position and your columns could get shifted over). If you want the return value to be unaffected by inserting columns, then you could try (note two commas before IFNA function): =INDEX(($P$4:$P$12, $Q$4:$Q$12, $R$4:$R$12), MATCH(J3, $O$4:$O$12), , IFNA(MATCH(B3, {"u","g"}, 0), 3))Re: Cell not populating correctly based on formula
Mickalopagus Yes, it is testing I15 for "n/a" and then processing the true condition for that IF statement (which is to test H3 for blank and either return blank or H3+60). This is your logic for your outermost IF statement. The rest of the formula (after ELSE) is only applicable when I15 is not "n/a". =IF(I15="n/a", THEN IF(H3="","", H3+60), ELSE IF(J16="Completed", "DONE", IF(NOT(J16="completed"), IF(I16<>0, "PENDING", IF(I15="","",I15+182))))) It's hard to tell what someone is trying to do from a formula that does not work. Perhaps explain more what you're trying to do and someone can assist with how to write the formula. Also, you might include what version of Excel you're using.
