Using worksheet ref as formula source

Topic

New Reply

I’d like to be able to use a worksheet name as part of a formula.
The worksheet name has been derived from the formula:

=RIGHT(CELL(“filename”,MainInput!$A$1),LEN(CELL(“filename”,MainInput!$A$1))-FIND(“]”,CELL(“filename”,MainInput!$A$1),1))

where MainInput is the originating worksheet

The result is obviously MainInput.

If the user changes the worksheet name then the output is updated accordingly.

I would then want to use that worksheet name as part of a formula,
i.e. the result would be “=MainInput!C2*B2”

It’s easy enough to include the link as normal entry.
If the formula is entered simply as “=C2*B2”, then when it is copied the formula stays as “=C2*B2”, but the user wants to be able to copy this worksheet AND retain the reference to MainInput worksheet.

Is this making sense?

My alternative at present is for 2 worksheets – MainInput and 2nd alternative calc worksheet that already includes the link to MainInput.

Thanks
Alan

Reply | Quote

Replies

You can use the INDIRECT function for this. Let’s say that the formula

=RIGHT(CELL(“filename”,MainInput!$A$1),LEN(CELL(“filename”,MainInput!$A$1))-FIND(“]”,CELL(“filename”,MainInput!$A$1),1))

is in cell A1. The formula

=INDIRECT(“‘”&A1&”‘!C2”)

will return the value of cell C2 on the sheet whose name is contained in cell A1. (The single quotes ‘ are needed if the sheet name contains spaces.) So if A1 contains MainInput, the result will be equivalent to

=MainInput!C2

but if A1 contains Monthly Data, the result will be equivalent to

=’Monthly Data’!C2

Reply | Quote

Thank you Hans, reading up on it now.

Reply | Quote