Home Tags Reference
You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.
A reference to another sheet always includes the sheet name (Sheet1), an exclamation (!), and the cell reference (I6). In this case we want the sheet name to be a reference from another cell in the worksheet and that whole reference to then be calculated. In each case the end of the reference is to the same cell "$I$6" which is the location of the Password in each worksheet. Then in front of that reference we want to insert the worksheet name. We need to get a combined string with the worksheet name and the cell reference, to acheive this we can use concatenation.
you can create a Name for that cell that can be used in the workbook to reference that cell. For example if the worksheet contains Sales Data and the cell in question is the total of sales for all sales staff it may called TotalSales. Then wherever we need to use that value we can refer to the name rather then the specific cell reference.