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:


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:


(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


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


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

| improve this answer | |
  • 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 Sep 25 '14 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.