Forum Discussion
Create a new table selecting info from an existing table in Excel vba
The major problem: you changed cmdCancel_Click to cmdCancel_Click3 and cmdOK_Click to cmdOK_Click3. That won't work: Click is the name of the event, you can't just change that.
Other problems: the headers are now in row 1 instead of row 2, and your column offsets for the context and activity were off by 1.
See the attached version.
dfhidromod It is possible to do much of this with worksheet formulas. I have use Excel 365 to put the entire calculation into a single formula in cell F1.
= LET(
\0, "Generate indices",
N, COUNTIF(Project[Working Area],Criterion),
k, SEQUENCE(8*N,1,0),
recordOffset, QUOTIENT(k,8),
fieldOffset, MOD(k,8),
\1, "Filter fields and records to extract relevant data",
headers, FILTER(Project[#Headers],columnFilter),
selectedFields, FILTER(Project, columnFilter),
filteredKey, FILTER(Project[Projeto], Project[Working Area]=Criterion),
filteredTable, FILTER(selectedFields, Project[Working Area]=Criterion),
\2, "Assemble report recombining project key and selected data",
reportKey, INDEX(filteredKey, 1+recordOffset),
reportHeader, IF(fieldOffset, INDEX(headers, 1+fieldOffset), reportKey),
reportData, INDEX(filteredTable, 1+recordOffset, 1+fieldOffset),
report, CHOOSE({1,2}, reportHeader, reportData),
report )Dear Peter,
thanks a lot for your help! I see in your file that you got the exact result I wanted.
However, when I try to redo it, it does not work.
So the pink table should select and filter the projects according to their working area, right?
So for this, you used the formula "= SORT(UNIQUE(Project[Working Area]))" but when I do it, it does not want to link both sheets: the one you named DataTable and Report.
And then on the left side of that same worksheet "Report", you have "N=" in one cell and then "=COUNTIF(Project[Working Area],Criterion)" which does not work either when I try it on my own file.
Do you think you know what is the problem here? and if yes, would you mind explaining it to me?
Thanks a lot for your file again, when it will work, it will simplify and automatize so much my work!! 🙂
Regards
Is your table named 'Project' or is it named as default 'Table1' (or the equivalent in Portuguese).
For the filtering to work you need to be using Excel 365. Otherwise the formulas get very messy.
