Drag Cell Reference but reference each sheet

[danmolesworth]

Hi all,

I'm trying to create a table but using reference to complete it. However i can't work out the correct formula to drag down the table

The first reference is ='[Ordering Monitoring Sheet V3.xlsm]ADAMS HARRISON '!$J$16

For ref the sheets are named after clients:

ADAMS HARRISON (SAWSTON)
ANB LAW (PETERBOROUGH)
BEACON WEALTH LEGAL (KIMBOLTON)
BEACON WEALTH LEGAL (SAWTRY)
BEACON WEALTH LEGAL (ST IVES)

Please help!

[Richard Buttrey]

Hi and welcome to the forum.

It appears that you're addressing sheets in a different file to the one in which you want the table. In which case you'll need to use the INDIRECT() function. The linitation is that the other workbook must be open in memory for this to work.

You'll need a list of the other workbook(s) & sheet names that you want to address and it's one of the trickier functions to get right first time. however upload the workbook in which you've added a list of workbook names and sheet names and no doubt we can help you further.

[danmolesworth]

Hi Richard,

Thanks for the swift response.

i have attached a spreadsheet with the workbook name and a list of all worksheets within it.

I wish to reference cell 'J16' on each sheet.

I look forward to your response.

[Richard Buttrey]

Hi,

If the 'Ordering Monotoring Sheet v3.xlsm' WORKBOOK is OPEN and contains SHEETS like
ADAMS HARRISON (SAWSTON)
ANB LAW (PETERBOROUGH)
BEACON WEALTH LEGAL (KIMBOLTON)
BEACON WEALTH LEGAL (SAWTRY)
BEACON WEALTH LEGAL (ST IVES)

Then in the WORKBOOK that contains the list of Sheet names starting say in C5 (as per your Book1) then in D5 enter

Please Login or Register  to view this content.

then drag this down column D

[danmolesworth]

Brilliant thanks Richards!