Home Tags Indirect
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.