Back end of Excel tools guidance

[achafetz]

Need to brain storm topics to discuss during the tool builder and broader Excel user meeting May 5 about what components are needed, how to structure tables/pivots, what should be included etc.

After meeting need to write up the "best practices" for ICPI.

[shapaklyak]

• use of pivot tables vs. formulas, drop-downs and what works best and where
• ease of updating tools in terms of set up

maybe can talk about things Backend fails?

• set up of static tables that are based on multiple pivot tables per tab
• no documentation

[achafetz]

• common elements?
• common tabs?
• should tab/cells be hidden/protected?
• should calculation tables all occur in one tab or should it be close to the viewable table?
• does the structure need to match the MER Structured Dataset?
• pre-processing? and when should calculations be pre-processed or calculated within a workbook?
• what should a sheet's extent be?
• what kind of documentation should exist?
• if there are common slicers, do they need to be connected?
• dealing with changing input data frame length, eg adding an additional column for each quarter
• should files ever be capped at a certain size?
• inclusion of VBA?

[shapaklyak]

Additional notes from 5/03 meeting.

Backend of tools Notes_2018 05 03.docx

[shapaklyak]

can we compile this into a small resource to go with the PVA course? was there any more traction on this besides what's here?

[randyyee]

Closing due to age