WORKBOOK Articles: Excel and Google Sheets

Excel allows us the ability to reference another sheet based on another cell’s value. This step by step tutorial will assist all levels of Excel users in using the INDIRECT function to reference another sheet. Figure 1. Final result Syntax of the INDIRECT and SUM formula The generic formula for...

Is it accurate to say that you are hoping to get workbook name and path without making use of the sheet name? You can achieve this by using a formula based on various Excel functions like SUBSTITUTE, LEFT, FIND and CELL functions to return the final result. Get a workbook...

Excel allows us to obtain the workbook name of the file we are working on, by using the CELL, MID and SEARCH functions.  The CELL function provides information about a cell such as the cell’s address, format or contents.  The MID function extracts a substring from the middle of a...

We can create a dynamic worksheet so that cell references can be variable instead of hand-keying them. A dynamic worksheet can be created with the INDIRECT function. The easy steps below will walk through the process. Figure 1: Result of the Dynamic Worksheet Reference Syntax =INDIRECT(sheet_name&"!A1”) Formula =INDIRECT(B4&"!A1") Setting up...

In order to check whether a certain worksheet name exists in a workbook or not, we can perform this check with the help of the Excel ISREFand INDIRECT functions. This article demonstrates how to perform this. Figure 1. Checking Worksheet Name in a Workbook Formula Syntax The generic formula syntax...

We can build a dynamic workbook reference by using the INDIRECT FUNCTION. This will enable us to easily work on different workbooks, allowing us to switch between them frequently to check and retrieve values from several worksheets. Figure 1: Created Dynamic Workbook Reference General Formula =INDIRECT ("'["&workbook name&"]"&sheet name&"'!"&reference cell)...

When the Excel Workbook contains multiple worksheets and we want to use each worksheet name as the header in the worksheet without typing it again and again. This can be achieved by using a formula in a blank cell of each worksheet that returns the worksheet name only. The formula...

We can utilize a formula that is based on the SHEET and INDIRECT functions to list the index numbers of sheets in an excel workbook. In this article, you will learn how to list the sheet index numbers in excel using these two functions. Figure 1: List sheet index in...