Stumped on INDIRECT function

[Batdragon]

What I am trying to do in a single formula is
1. Link to a sheet that has not yet been made
2. Pull the text from a cell to reference that sheet
3. take data from that sheet and put it into the cell with this formula.

Sheet Cell Content
List A1 John Doe
John Doe X1 £5.50
Jane Doe X1 £10.95

In Cell C1 of sheet 'List" I want it to show £5.50 but if I change the name in in cell A1 to 'Jane Doe' the content of cell C1 will change to £10.95. The formula must remain viable even if the sheets 'John Doe' and/or 'Jane Doe' do not exist yet.

I have created a Macro that will make a sheet that is named from the selected cell (eg 'John Doe') and copies a template layout so I know exactly where I want the data to come from (eg in this case cell X1)

I have been trying things like this to get around the fact that there are spaces in the text (and of the sheet name)

=INDIRECT("#"&A1&"!X1")

But nothing I do seems to work. Any and all help would be great as this is basically the final bit of the puzzle for me to finish of this Project

BD

 

[offthelip]

I think i understand what you are trying to do, try this:

=INDIRECT("'" & A1 & "'!X1")

The formula will give a "Ref" error if the worksheet doesn't exist, you could put an iferror function round it if necessary

 

[Batdragon]

I think i understand what you are trying to do, try this:

=INDIRECT("'" & A1 & "'!X1")

The formula will give a "Ref" error if the worksheet doesn't exist, you could put an iferror function round it if necessary

Thank you so much for this, it worked perfectly. I also used the IFERROR function you suggested to make it into this

=IFERROR(INDIRECT("'" & A1 & "'!X1"),0)

Which gives me the perfect solution

I can now finish off this project and not pull any more hair out that I have not got lol.

Thank you very much for your help

BD

 

[Batdragon]

Thank you so much for this, it worked perfectly. I also used the IFERROR function you suggested to make it into this

=IFERROR(INDIRECT("'" & A1 & "'!X1"),0)

Which gives me the perfect solution

I can now finish off this project and not pull any more hair out that I have not got lol.

Thank you very much for your help

BD

Eek I just found an error in that within the list of names in my list is a surname O'Connell and the ' in it is causing the formula not to be able to find the right sheet.

Is there a way around this?

BD

 

[programsam]

If you double the apostrophe in O'Connell, what happens?

O''Connell

Eek I just found an error in that within the list of names in my list is a surname O'Connell and the ' in it is causing the formula not to be able to find the right sheet.

Is there a way around this?

BD

 

[Rick Rothstein]

Eek I just found an error in that within the list of names in my list is a surname O'Connell and the ' in it is causing the formula not to be able to find the right sheet.

Is there a way around this?

Give this formula a try...

=IFERROR(INDIRECT("'" & SUBSTITUTE(A1,"'","''") & "'!X1"),0)

 

[Batdragon]

Give this formula a try...

=IFERROR(INDIRECT("'" & SUBSTITUTE(A1,"'","''") & "'!X1"),0)

That did it, Thank you and sorry to be a pain

BD