I think any member can add a record in the table by:
choosing a name,
choosing a year
choosing a criterion
inputting a value
Then the database is ready for analysis by PT (after 'refresh').
Since the worksheet has 2^20 rows it will take some time before it is running out.
Now, I can already hear the exasperation, but hear me out. We have a "master list" on a worksheet consisting of data in columns A through D as you have them, but the data in that table is populated from an annual worksheet, much like in the initial test workbook I uploaded.
I'm just trying to envision adding records and it seems easier to have a name listed in column A and the various criteria in Row 1 so data can be filled in systematically per name as opposed to selecting name/year/criteria and then inputting the value. Does that seem reasonable?
It doesn't.
Did you add a new row and data in the Table ?
So, I add a row to the table located in columns A-D. I have a drop-down selection for name, criterion, year and then fill in Column D with the number of times said name accomplished said criteria.
In your sample workbook, I added row 92 with the data consisting of "Jerry West, 2022, 2, 1" in columns A - D, respectively. Upon refreshing the PT, the newly added data populated and the calculations updated. GREAT!
My issue is let's say Jerry West, in the year 2022, accomplished 17 of the 25 criteria. I now have to, 17 times, add a row, click his name, select the year, select the criteria, and input the value. Am I totally missing a simpler way to do this?
On a new worksheet, I simply go to the row with his name in column A and fill in the 17 cells with a value and done. Once all the data for 2022 for all the names is entered, take that data and append/add it to the bottom of the table on the "master sheet" (simple enough with VBA I'd assume) and refresh the PT.
I apologize if this sounds inconsiderate, because I truly appreciate the new perspective on this project and is no way intended to sound any way but appreciative.
Excel has a built in (crude) data entry form that can handle a List
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
It's all minor.
See the atttachment.
You only have to enter a new value in column D.
See what happens.
Posting Permissions
Reply With Quote
