Google-sheets – Use Text in cell as formula in Google Sheets

You need to use the indirect() formula. So in cell B1 you need to put this formula:

=indirect(A1&"!B4")

For Google Spreadsheets, it is possible by writing a script:

function copyValuesAndFormatting() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();

    var fromRange = sheet.getRange("A2:A");
    var toRange = sheet.getRange("B2:B");
    var values = fromRange.getValues();
    var fontColors = fromRange.getFontColors();
    var backgrounds = fromRange.getBackgrounds();
    var fonts = fromRange.getFontFamilies();
    var fontWeights = fromRange.getFontWeights();
    var fontStyles = fromRange.getFontStyles();

    toRange.setBackgrounds(backgrounds);
    toRange.setFontColors(fontColors);
    toRange.setValues(values);
    toRange.setFontFamilies(fonts);
    toRange.setFontWeights(fontWeights);
    toRange.setFontStyles(fontStyles);
}

Add a trigger for the script function, so that it runs on every spreadsheet modification.

I have created a sample spreadsheet here. Feel free to copy it to your own account, and start experimenting with it.