google-sheets - how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet? - TagMerge
2how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet?how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet?

how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet?

Asked 1 years ago
1
2 answers

Try to remove the "'"& before D4 and the ' after the Overall.

Your formula should look like this:

=IFERROR(HLOOKUP($A$2,INDIRECT(F4 &" Overall!$A$5:$X$150", Utility!$A10, FALSE)))

Source: link

0

With Nikko's nudges in the right direction, I eventually figured out the right format. This allows the formula to work in multiple sheets and to be filled right (for more years) and down (for more classes).

=IFERROR(HLOOKUP($A$2,INDIRECT("'"D$4&" Overall'!$A$5:$X$150"), Utility!$A3, FALSE))

Note that if you try to replicate this, you may need to type the formula out from scratch—I had a problem where pasting it in didn't work. Once I'd retyped it and Google Sheets acknowledged it, it worked from then on in the spreadsheet, even when pasted from sheet to sheet.

Source: link

Questions related to 'how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet?'

how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet?
Wrapping filter formula into Array Formula to avoid dragging formula down sheet while also using named ranges
Reference another sheet in a formula using INDIRECT but it does not update as I add a new sheet
How can I use my cell value in A1 and write this value into another vba code?
How can I use sumproduct to gather data on the sum of specific cells on another sheet?
Can I use another build system to build custom processors with NiFi?
Highlight any cell in a range that matches any cell in another range
How to use entries in a listA to refer other lists with names that have prefixes same as ListA's entries
Using script via custom menu to one-click refresh formulas in cells rather than clicking into cells individually to refresh formulas in cells
Is there any Excel function that can copy culumn's (first row's) name if the value matches?
VBA: Open a file and copy/paste the 3rd sheet's data into a workbook using a dynamic path made with cells "B2" "B9" and "B4"
A badge in GitHub template repository that will refer to clones' build status, not the template repo's build status
Grab data from certain cells in a row based on cell value in another sheet (Google Sheets)
How can I sort using MUI Datatable using value, instead of what's in my custom body render?
How can I write the names of variables as value labels into another variable in R?
How can I write row(s) of 2d array to specific rows in Google Sheets?
How can I use the sumif function with criteria beeing exact text from another cell?
How to check if a cell is greater than all cells above in google sheets, and do this for a whole column of cells (dynamic range)
How can I specify a 'formula' while building widgets: flutter
Can I use multiple 'await' in an async function's try/catch block?

Recent Questions on google-sheets

    Programming Languages