Indirect Formula & worksheets

Resolved · Medium Priority · Version 2010

Wed 23 Aug 2017, 14:14 Delegate Jackie said...

Jackie has attended:
Excel Financial Modelling course

Indirect Formula & worksheets

How do you refer to a worksheet name in a formula by referring to it in a cell eg sheet is called JAN - in your summary sheet cell A1 says JAN - I want a formula to say =indirect(A1)&"!"&.... and not ='Jan'!'.....

Wed 23 Aug 2017, 20:39 Trainer Jens said...

RE: Indirect Formula & worksheets

Hi Jackie,

Thank you for the forum question.

To reference a worksheet from the Indirect function do like this:

=indirect(A1&"!E2:F10")

If you have Jan in A1 the indirect function will reference the range E2:F10 in the Jan worksheet. If you change A1 to Feb the function will reference the same range but in the Feb worksheet.

I hope this make sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 24 Aug 2017, 08:58 Delegate Jackie said...

RE: Indirect Formula & worksheets

Hi Jens
Thanks for the reply. After I sent my query yesterday I realised I should have been more specific. We got that far with the formaula but the limitation there is that you can only ever reference E2:F10 and if you drag /copy the cells won't change. What I was hoping for was something like :

=indirect($A$1&"!"&E2)
which if dragged down to the next cell would change to
= indirect($A$!&"!"&E3)
but I know that formula doesn't work

Thanks
Jackie

Thu 24 Aug 2017, 13:17 Trainer Wendy said...

RE: Indirect Formula & worksheets

Hi Jackie

=INDIRECT($A$1&"!B"&ROW())

I've added the Row reference which should update as you drag down instead of locking it to the range. This does depend on how your data has been set up.

Are you planning on dragging down and over or only in one direction?

If so, your formula might work better using
=INDIRECT($A$1&"!"&ADDRESS(ROW(),COLUMN()))

I hope that helps

Kind regards,
Wendy

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Microsoft-office-training london

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Microsoft Office tip:

Standard Ctrl Keys

Here are some standard Ctrl keys you can use in all your Office applications.
A - Select All
B - Bold Text
C - Copy
F - Find
G - GoTo
H - Replace
I - Italics
K - Insert Hyperlinks
N - New File
O - Open File
P - Print
S - Save
U - Underline
V - Paste
W - Save and Close
X - Cut
Y - Redo
Z - Undo

Other vary between applications. See additional lists.