Using cell value as sheetname for closed workbook

sxpeter

New Member
Joined
Apr 10, 2002
Messages
36
Is there a way to use a cell value in an open workbook to access data in a closed workbook? As an example, I have a closed workbook with multiple tabs each named for a year. In my open workbook, I would like to use the value stored in A1 (e.g. 2017) to be able to pull data out of the correct sheet in the closed workbook. I want to do this without using VBA.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
It is not possible without VBA.

This requires using the INDIRECT function, which will work only when the other workbook is open. To do this on a closed workbook, you must use VBA to do one of the following (there may be others):

  1. Write the formula into your referencing sheet so that it hard-codes the target sheet name into the formula
  2. Open the target workbook and retrieve the data
  3. Use ABDO to retrieve the data without opening the target workbook
 
Upvote 0
Thanks for the response. What I was looking for was something similar to the INDIRECT.EXT function that the MOREFUNC addin provided in the past. Does anyone know if that still works with Excel 2019 or if Microsoft is planning to add that capability any time in the near future?
 
Upvote 0
INDIRECT.EXT uses VBA and you said you didn't want to do that, so I didn't suggest it. It will work in any version of Excel that supports VBA.

Only Microsoft knows what they are planning to add in the future and they are not talking.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top