Steps to replace Item Master Connection in GENSUM

**MAKE A BACKUP OF FILE BEFORE PRECEDING**

1. Right Click on the worksheet tabs at the bottom (doesn’t matter which one) and select Unhide…
2. In the Unhide dialog, select QryItem and hit OK button. This will unhide the QryItem Worksheet
3. Select the Data Tab in the ribbon, then Get Data > From File > From Excel Workbook.
4. In the Import Data dialog that opens, paste the below path in the File name textbox and hit open.

https://www.dot.state.oh.us/Divisions/ContractAdmin/Contracts/Construction/ItemE23.xlsx

1. 1. if you get an Access Web Content dialog, leave it on anonymous and hit the connect button.

5. Select QryItemE2023 on the navigator dialog, then select the Transform Data Button.
6. In the Power Query Editor dialog click and drag the ILFLG1 column to the right of the ITEM column.
7. Now in the Ribbon, under the Home tab, select the Close & Load button. This will create a data connection and add a new worksheet named QryItemE2023.
8. Now in the excel ribbon under the Formulas tab, select the Name Manager button.
9. Now select the ITEM Name and at the button under the refers to: update the formula from =QryItem[[#ALL],[ITEM]] to =QryItemE2023[[#ALL],[ITEM]] and hit the green checkbox button.
10. Now select the QryItemNamed Name and at the button under the refers to: update the formula from =QryItem[#ALL] to =QryItemE2023[#ALL] and hit the green checkbox button.
11. Hit the close button in the Name Manager dialog.
12. Right click on the QryItem Worksheet tab and select delete, then when prompted to confirm select yes.
13. Now on the QryItemE2023 worksheet, select any cell within the table, then in the ribbon, select the  Table Design Tab, change the Table Name in the ribbon from QryItemE2023 to QryItem.
14. In the Ribbon, select the Data Tab > and hit the Queries & Connections button, this will open the Queries & Connections panel and should show one connection named QryItemE2023. Select this, then right click on this and select properties. In the Query Properties window that opens change the Query Name from QryItemE2023 to QryItem.
15. Right click on QryItemE2023 worksheet tab and select rename, change the worksheet name form QryItemE2023 to QryItem.
16. Select cell J1 in the newly renamed QryItem worksheet and paste in the below formula.

=Lists!CE2

17. Select  cell J2 in the newly renamed QryItem worksheet and paste in the below text

=Lists!BZ2

18. Lastly right click on the newly renamed QryItem worksheet and select Hide and close the Queries & connections Panel.
19. Hit the Refresh Item Master button on the Gensum Summary worksheet to test and validate the connection was updated correctly or not.

Below is a video walking you through the steps.