BTW, I just made the two changes you identified, and it ran fine for me.
BTW, I just made the two changes you identified, and it ran fine for me.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
They added two columns: Age of buyer and Annual Income (not sure why)
Yeah, I noticed after posting that you said column J.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
It's all good now. Thank you so much
They no longer added the columns
As I said Jazz, when I added the columns and made the changes you suggested, it worked fine.
I have an update that makes changes like that more manageable, I added the Enums I mentioned, and also configured it so that you can change where the layout starts (the row number). The business should drive the IT, niot the other way around.
Do you want that version ?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hmmm it's okay
Quick question, though. I am trying to use your way of coding in this particular question.
This time I am trying to create a table showing whether they are P or F instead of the numbers.
The formula I am using is
This works if I type it manually in a cell.=LOOKUP(2,1/(Monthly!A:A=Table!B9)/(Monthly!D:D=Table!C10)/(Monthly!J:J=Table!G8),(Monthly!L:L))
And my attempt at translating it is:
But I am getting an error here:Const FORMULA_SUB_CATEGORY As String = _ "=LOOKUP(2,1/(<period>!C1=<level>)/(<period>!C4=Table!C3)/(<period>!C10=Table!R8C),(<period>!C12)"
It says application defined or object defined error. What am I doing wrong?.Cells(targetrow, "T").Resize(1, 2).FormulaR1C1 = Replace(Replace(FORMULA_SUB_CATEGORY, "<period>", "Monthly"), "<level>", """" & category & """")
Hi, okay so I solved it. I just forgot the other ) at the end
But it's super slow like it's taking over 5 minutes for that table alone -_-
Try using the version I posted in #48, as I said it was much quicker.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Yes that's what I am using but it's so slow.. Here's my new sample workbook. I made adjustments to it based on what they requested to change.
Maybe you could change the other table..
I ran it five times, and these were the timings
Elapsed time: 7.84765625
Elapsed time: 4.46484375
Elapsed time: 2.8515625
Elapsed time: 2.64453125
Elapsed time: 2.93359375
Even 7.8 seconds, whilst slower than I was getting originally, is not unacceptable IMO, 3 secs certainly isn't.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Uhmm my raw data actually has 7620 rows.. and 15 columns
But it's okay now we used INDEX MATCH instead using the helper columns created during runtime.
wrong thread.
Another question, though. How do I change the formula for Category rows (monthly and weekly) if for example, I filter the owner to a specific owner and then I filter the group?
Like in the picture below: I filtered the owner Bob and then I filtered just group 2 under group column. the resulting number for category row for monthly and weekly should show either 100%, 50% or 0% only since there are only 2 sub categories under group 2
a.jpg
I don't see the problem.
If you use VBA instead of Excel formulae the results appear in less than 1 msec.
That is just so simple to adapt.
@ snb,
the problem is that this not just a Report designed by a management committee, this is a Data Base to be used as a Report designed by a management committee and the OP must hack the Data Base directly for each Report, rather than using the Data Base as a source of Data for any Reports.
Further confounding the issue is that the Data Base Fields contain two Types of data, either Percent or Int/Long. AFAIK even the same Named Record could use different Types on different Dates
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
@Sam
Can you show me where in my file the results differ from the desired results ?
?
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ