Reference cell in different sheet by naming the sheet in seperate cell

**johnnyboyisme** · 07-10-2013, 02:52 AM

Hi All,

Have been looking for a way to do this for a few hours now but cannot find what im after.

What i am trying to do, is to effectively produce the same answer as ='Alpha Records'!A5 but in a different manor

I have returned the name of the sheet using =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))&""&" Records" in cell A1 sheet 'Alpha'

I now want to find use something similar ie ='A1'!A5 in the sheet 'Alpha' but cannot find a way to do this. Using Indirect can reference values in 'Alpha Records' but by first naming that cell in 'Alpha'. Is there a way to do this?

The ultimate forumla is more complicated but by getting this initial reference i will be able to complete.

The standard cell referencing system is not suitable in this situation as there is creation of multiple sets of sheets and do not want to correct the formulas

Thanks

**vlady** · 07-10-2013, 03:06 AM

Moderator's note:

I think you should post a sample workbook atleast 2 to 3 data and the expected output.

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**johnnyboyisme** · 07-10-2013, 03:22 AM

Example.xlsx
Sorry about that,

Have attached simple example for what i would like to do.

I understand the 'regular' method is easier but does not work in my situation

Thanks

**vlady** · 07-10-2013, 03:43 AM

the indirect works

=INDIRECT("'"&E3&"'!A5")
this also works
=INDIRECT("'"&RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))&""&" Records"&"'!A5")