Handling non-numeric named cell values from excel
Import non-numeric named cell values into Expert Estimation.
When linking Global Defines with named cells in spreadsheets, Expert Estimation gives you the ability to choose between importing all named cells from an Excel spreadsheet or only named cells with a numeric value.
Let’s have a look at an example spreadsheet for Global Defines values we want to link to. We want to import the value for:
- Labour Productivity
- Project Duration
- Time Lost In Hours
- Overtime Allowance.
First we need to check that the cells with values are named so they can be seen in the Spreadsheets window in Expert Estimation. We have named the cells LabProd, ProjectDuration, Wastage, TimeLostHRS, and OvertimeAllowance.
In Expert Estimation, to get to the Spreadsheets window, in the HOME menu tab, select Spreadsheets from the ribbon tool bar, or go to Resource Explorer, then select Spreadsheets from the ribbon menu.
To import a spreadsheet:
Click on the ‘add spreadsheets’ icon in the ribbon menu, then upload your Excel file from the location where the spreadsheet has been saved. You can upload XLSX and XLSM files. CSV files do not allow named cells so they cannot be imported.
To see a summary of all the named cells, including those with non-numeric values, in the Expert Estimation Spreadsheets window, make sure the ‘Import Numeric Named Cells Only’ box is not ticked, then highlight the imported spreadsheet document, and click on ‘View Spreadsheet Defines’. You will see a summary of all the named cell values. Named cells that are not able to be converted to a numeric value, for example if the cell is empty or has text in the cell, it will be imported and synchronised with a value of zero.
These cells and values can be synchronised with Global Defines and Resource Rates. This is useful when you need to calculate a value in Excel and be able to then use that value in calculations throughout your project, or use it as the Base Rate of a Resource.
When you import a spreadsheet, a copy of the spreadsheet is added to the Expert Estimation database. The original remains unchanged in the location where it was saved.
To instead see only named cells with a numeric value, check the tick box in the “Import Named Cells only” column on the imported spreadsheet row, and click ‘View Spreadsheet Defines’ again.
You can see this time, only the named cells with a value have been listed. Time Lost Hours, which had a no value in the named cell, and Overtime Allowance, which had text in the named cell, are no longer shown.
And that is how to handle importing numeric and non-numeric named cell values from Excel spreadsheets into Expert Estimation.
WANT MORE TIPS & TRICKS? VIEW HERE