CODEWITHSUNDEEP
excelexcel-formula
Prospidnick
Prospidnick

Reputation: 133

Indirect Sheet name only as a variable, not workbook or cell

Very simple question, I want to recall values from the same cell address from the same workbook from different sheets (tabs) I am returning the values to a different workbook.

I'm using the INDIRECT method and it works fine, however I do not need the cell address or the workbook name to change and therefore have no need to place it in a variable. Can I concatenate two functions to make the code smaller and faster ?

Below is an extract from my code, which works fine, I am aware that the other workbook needs to be open to return the value.

=IF(J65>INDIRECT("'["& $N$1 &"]"& H65 &"'!"& $N$2),INDIRECT("'["& $N$1 &"]"& H65 &"'!"& $N$2),J65)

Many thanks.

Upvotes: 3

Views: 781

Answers (1)

John Coleman
John Coleman

Reputation: 52008

You could use defined names. I created a workbook called testbook.xlsx and then in another workbook I defined two names:

1) Name target with value ="!$A$1"

2) Name wb with value ="[testbook.xlsx]"

It is important that the quotation marks, brackets, explanation marks, etc. are part of the defined name. Note also that = is not inside the quote marks. For example:

enter image description here

The you can just use expressions like INDIRECT(wb & B1 & target), which is more readable than what you are doing now:

enter image description here

For testing purposes I entered the formula =wb & B1 & target in C1 to make sure that I defined the strings wb and target correctly.

5 is indeed the value I put in A1 in testbook and if I change the contents of B1 in the screenshot above to Sheet2 the value in C1 successfully changes to the value I have in [testbook.xlsx]Sheet2!A1.

Upvotes: 2

Related Questions