0

So I have a series of worksheet in my workbook names A1-A4,B1-B4 etc. I'm trying to make an index page that pulls some of the data from each worksheet into a table but can't seem to get the INDIRECT function to take. My current function is:

=INDIRECT("'"&B3&"'!D3&CHAR(10)&'"&B3&"'!D4")

This gives me #REF errors, the intention was that it would read the worksheet name from cell B3 on the index page and then display the text in cells D3 & D4 of that worksheet separated by a newline. i.e:

=('A3'!D3&CHAR(10)&'A3'!D4)

(Assuming B3 contained "A3"). I realise that I could just hard-code the worksheet names into the tables, but that all gets a bit squiffy when people rename the worksheets and I'm not intending to be the only on using this index eventually. My plan is that they would be able to edit the B column with the new worksheet names and the formulas would change to suit.

Thanks for any input

Improve this question

1 Answer 1

Reset to default
0

I don't know if you can insert a new line like that, but the format you're looking for is

 =(TEXT(INDIRECT("'"&A1&"'!D3"),"")&CHAR(10)&TEXT(INDIRECT("'"&A1&"'!D3"),""))

I take that back, just make sure the cell is wrap text and the NL works

Improve this answer
1
  • Thank you for this. I've had to change it to =(TEXT(INDIRECT("'"&B3&"'!D3"),"###########")&CHAR(10)&TEXT(INDIRECT("'"&B3&"'!D4"),"###########")) in order to accomodate when the target cell contains only numbers (hopefully they won't extend past 11 digits). If you have a more elegant solution I'd be much obliged.
    – Leigham
    Commented Sep 25, 2014 at 18:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .