Hello,
I have attached an excel sheet in which there are drawings of various disciplines and a summary report sheet. I want the summary report sheet to be updated automatically when I make changes / adding new rows for new drawings / update the latest status columns in the overall drawing sheet. Please help.
Thank you in advance
Last edited by sharathnarayanan; 01-01-2021 at 09:40 AM.
Your use of merged cells may look pretty, but in Excel terms, it resembles a dog's breakfast. Instead of two formulae that could be copied down the columns, there are 18 different formulae in use, to cope with the mess caused by your merged cells.
If you want to use Excel effectively... never, ever use merged cells.
The formulae (18 in total for a VERY simple task) are variants of:
=COUNTIFS('Overall Drawings'!$A:$A,$A$6,'Overall Drawings'!$B:$B,$B$6,'Overall Drawings'!$C:$C,$C6,'Overall Drawings'!$O:$O,">0")
Here is a useful www regarding the use of merged cells. Please read it.
https://excel.solutions/2016/10/usin...ctively-excel/
I do not know what is meant to be in the bright yellow shaded columns as you did not explain very much (at all).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hello Glenn,
Thank you very much for your help!. Wish you a very Happy New Year.
One more help please.
In the summary sheet, Column 'D' also to be updated when I add more rows/drawing numbers in "Overall Drawings" sheet , i.e., when Column K, shows any drawing number. Thanks
Hi
I removed the merges cells in your summary, and applied the below formulas:
D6 and down:
E6 and down:
=COUNTIFS('Overall Drawings'!$E$3:$E$62,"<>",'Overall Drawings'!$A$3:$A$62,'Summary Format'!$A6,'Overall Drawings'!$B$3:$B$62,'Summary Format'!$B6,'Overall Drawings'!$C$3:$C$62,'Summary Format'!$C6)
F6 and down:=SUM((LEFT('Overall Drawings'!$P$3:$P$62,3)="476")*('Overall Drawings'!$A$3:$A$62='Summary Format'!$A6)*('Overall Drawings'!$B$3:$B$62='Summary Format'!$B6)*('Overall Drawings'!$C$3:$C$62='Summary Format'!$C6))
=SUM((LEFT('Overall Drawings'!$R$3:$R$62,3)="446")*('Overall Drawings'!$A$3:$A$62='Summary Format'!$A6)*('Overall Drawings'!$B$3:$B$62='Summary Format'!$B6)*('Overall Drawings'!$C$3:$C$62='Summary Format'!$C6))
=SUMPRODUCT(('Overall Drawings'!$A$6:$A$1000=$A$6)*('Overall Drawings'!$B$6:$B$1000=$B$6)*('Overall Drawings'!$C$6:$C$1000=$C6)*('Overall Drawings'!$K$6:$K$1000<>""))
copied down and then the ranges adjusted manually for each mess caused by your merged cells.
But you should NOT use merged cells. Ever. It is a very bad habit to get in to.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Thanks Belinda, Thanks Glenn.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks