1.7 Range & Dynamic Named Range OFFSET Select from B6 to B 13 Go to Formula Tab Select Define Name Write Name as – – – - Refers to ='Named Range'!$B$6:$B$13 Click OK “Price” To Perform SUM Range in Cell B19 = Sum(Price) Prepare Script List in Cell B61 Go to Data Tab Select Data Validation Select List – – – – – Select Source = A8:A58 (or) Select the Script List Define Name as Go to Data – “Script” – Validation Select List Source =Script - Drop menu will appear. – – Prepare Companies List in Cell B63 - Select the Companies List (B8:B58) Define Name – – as Go to Data Tab Select Data Validation Select List Select Source = “Company” – – – – Company - Drop menu will appear. Prepare Header List in Cell B65 - Select the Headers (A7:K7) Define Name as – “Header” Go to Data Tab Select Data Validation Select List Select Source = Header - Drop – – – – menu will appear. Select all Data points one by one Define Name with its respective Header Prepare Dynamic List - Go to Data Tab Select Data Validation Select List Select – – – Source =INDIRECT($C$65). Drop menu will appear. 1.8 Dynamic Named Range INDEX Prepare Script List in Cell B61 Go to Data Tab Select Data Validation Select List – – – – – Select Source = A8:A58. Drop menu will appear. Prepare Companies List in Cell B63 Go to Data Tab Select Data Validation Select – – – – List Select Source = B8:B58. Drop menu will appear. –
1.7 Range & Dynamic Named Range OFFSET Select from B6 to B 13 – Go to Formula Tab – Select Define Name – Write Name as “Price” - Refers to ='Named Range'!$B$6:$B$13 Click OK To Perform SUM Range in Cell B19 = Sum(Price) Prepare Script List – in Cell B61 – Go to Data Tab – Select Data Validation – Select List – Select Source = A8:A58 (or) Select the Script List – Define Name as “Script” – Go to Data Validation – Select List – Source =Script - Drop menu will appear. Prepare Companies List – in Cell B63 - Select the Companies List (B8:B58) – Define Name as “Company” – Go to Data Tab – Select Data Validation – Select List – Select Source = Company - Drop menu will appear. Prepare Header List in Cell B65 - Select the Headers (A7:K7) – Define Name as “Header” – Go to Data Tab – Select Data Validation – Select List – Select Source = Header - Drop menu will appear. Select all Data points one by one Define Name with its respective Header Prepare Dynamic List - Go to Data Tab – Select Data Validation – Select List – Select Source =INDIRECT($C$65). Drop menu will appear. 1.8 Dynamic Named Range INDEX Prepare Script List – in Cell B61 – Go to Data Tab – Select Data Validation – Select List – Select Source = A8:A58. Drop menu will appear. Prepare Companies List – in Cell B63 – Go to Data Tab – Select Data Validation – Select List – Select Source = B8:B58. Drop menu will appear.
efm2-record.docx