Hello Everybody,
Could you help me in my question ,please?
I want to consolidate data from multiple sheets and put them in "Report" Sheet.
See "Report " sheet for the results.
Please Use Formulas not VBA.
Office 365.
See the attachment
Hello Everybody,
Could you help me in my question ,please?
I want to consolidate data from multiple sheets and put them in "Report" Sheet.
See "Report " sheet for the results.
Please Use Formulas not VBA.
Office 365.
See the attachment
Please try at
A4
=DATE(2021,1,MATCH(SEQUENCE(COUNTA('1:31'!$A$4:$A$100))-1,MMULT(N(SEQUENCE(31)>SEQUENCE(,31)),SUBTOTAL(3,INDIRECT("'"&SEQUENCE(31)&"'!A4:A100")))))
B4:L4
=IFERROR(FILTER(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",0,'1:31'!A$4:A$100)&"</m></x>","//m"),1-ISERR(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",0,'1:31'!$A$4:$A$100)&"</m></x>","//m"))),"")
Thanks Bo_Ry for your solution.
But This formula doesnot work with big data more than 150 row in every sheet.
I`am sorry I didn`t mention that my data can exceeds that limit 150 row.
Is there any alternative?
For more data better use Power Query or VBA
Power Query
Change file path in red
Please Login or Register to view this content.
VBA
Please Login or Register to view this content.
Thanks again Bo_Ry.
But I prefer using formulas.
Why can`t you use formulas?Will the file be slowser?
How many rows maximum per sheet.
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
Glen,
Maybe 5000 rows Maximum
Hahaha. Formulae just won't work with that data size. Forget it.
Go with PQ or VBA.
What is your formula size limit?
We're NOT going down that way again. You wouldn't believe me about INDIRECT alternatives, but I was right.
FORGET IT. It won't work.
Do you mean that your Report sheet might be up to 5000 rows, or that an individual daily sheet might contain that number of rows? If the former, does that mean that your daily sheets are not likely to exceed 200 or so rows?
Pete
I could create "indirect" formula to get data from other sheets but it is not dynamic,I should drag it down manually.
How to make it dynamic?Please Login or Register to view this content.
When I put "#" sign to a4#, it shows me value error.
Please help me
see the attachment sheet"Report(2)"
Please try at B4
=INDIRECT("'"&DAY($A4)&"'!R"&COUNTIF($A$4:$A4,$A4)+3&"C[-1]",)
Thanks Bo_Ry for your solution but this formula is not dynamic,it takes a lot of time calculating.
If it is dynamic,it will take less time calculating.
Not sure you are using the term 'dynamic' correctly - what do you mean by it?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I mean Dynamic formula like filter-sort-unique.
see post #14
try at B4
=LET(i,SEQUENCE(COUNTA('1:31'!$A$4:$A$1000))-1,a,MMULT(N(SEQUENCE(31)>SEQUENCE(,31)),SUBTOTAL(3,INDIRECT("'"&SEQUENCE(31)&"'!A4:A1000"))),d,MATCH(i,a),r,i-LOOKUP(i,a)+4,CELL("contents",INDIRECT("'"&d&"'!R"&r&"C"&SEQUENCE(,11),)))
Thanks,well done
solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks