solved
Relevant answer
Krupin Dharaiya Put the formula accordingly:
• in K4: =I3 • in L4: =K4+9 • in N4: =L4+1 • in O4: =N4+9 • in Q4: =O4+1 • in R4: =EOMONTH(I3,0)
• in H6: =SEQUENCE(COUNTA(I6:I100)) • in I6 =FILTER(B6:B21,A6:A21<=EOMONTH(I3,0)) • in L6: =SUMIFS($C:$C,$B:$B,$I6,$E:$E,">="&K$4,$E:$E,"<="&L$4) • in O6: =SUMIFS($C:$C,$B:$B,$I6,$E:$E,">="&N$4,$E:$E,"<="&O$4) • in R6: =SUMIFS($C:$C,$B:$B,$I6,$E:$E,">="&Q$4,$E:$E,"<="&R$4)
Relevant answer
Syirazi Kama What is the problem with Upper Cell -1000 then drag downward
Relevant answer
Formula is correct Because in half sheet it is working correctly
In the other half part of sheet I thi k there is problam
Relevant answer
K2=iferror(index($f$2:$f$27,match(k$1&$j2,$a2:a$27&$d$2:$d$27,0)),"")
Press ctrl+shift+enter to run
Relevant answer
The matter has been solved by Diplab Das ...the image is the correct code
Relevant answer
Use SUMPRODUCT
=RANK(J2,$J$2:$J$12)+SUMPRODUCT(--($J$2:$J$12=J2),--(I2<$I$2:$I$12))+SUMPRODUCT(--($J$2:$J$12=J2),--($I$2:$I$12=I2),--(G2<$G$2:$G$12))
Relevant answer
Renato Bolaños i believed you used condi formatting on that icon? can you show what value is in that icons (✓ ❌!)?
Relevant answer
Nikhil Jain this is already solved, kindly search the group. Here are two links, of the solution. Grouping is basically based on Empty rows using Power Query, most flexible dynamic, scalable and robust approach.
• https://www.facebook.com/groups/659225117560345/permalink/2463275697155269/
• https://www.facebook.com/groups/659225117560345/permalink/2461383564011149/
Relevant answer
OK, so first principles... Your input sheet should be a sheet with all your info in. Then another sheet should have your summary or report or calculation in. You shouldn't be deleting out old stuff, just give it a date and move on to your next row in your data sheet. This means you don't delete input rows and you can use formula to give the answer you want.
With regards to finding last row, find * tends to be the most consistent I have found. So you would Dim LstRw as long and set lstrw LstRow = 1 On error resume next Lstrw = thisworkbook.sheets("nameofthesheet").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row On error goto 0
The reason you set to 1 is because if the spreadsheet is blank, it gives the value as 0, and a last row as zero would cause an error so you set to 1 to avoid that.
I know you are new to it, but sometimes it's better to learn something properly rather than quickly as it will cause problems later on!
Relevant answer
Hi Biplab, the filter function will fetch results from BOM but it wont insert new rows and also the number of lines are more in process orders tab. Will power Query or as you mentioned Advance filter copy can help?
Relevant answer
Thank you for the approval. I have managed to sort the above out, and must say very chuffed with myself.
Hot network questions
