If I have 2 sheets. Sheet 1 and sheet 2, (names will be changed)

I need an if/then formula to say

If sheet1 sheetname is "X" and "X" matches the name of Sheet2 A1 Fill a specific cell in Sheet 1 with the info from Sheet2 B1

So example:

Sheet1 is named "shot_001". I want the result to end up in "Shot_001!C3"
Sheet2 is "All_shots" So I want to read and compare to see if "All_shots!A1" is equal to "shot_001!" sheetname. If "shot_001!" sheetname matches "All_shots!A1" then paste "All_shots!B1" into "shot_001!C3"

That way it's a dynamic link based on the sheetname of "shot_001" if I duplicate it, and rename it to "shot_002" it will update to "All_shots!B2" Duplicate and rename to "shot_003" it will return "All_shots!B3" etc.

"All_shots" A1-A67 have incremental numbers "shot_001-shot-067" etc.

1 Answer 1


Your assumptions are complicated by the fact that there is no standard function that will return the name of a sheet. So while the logic is sound, the ability to test for the sheet name is flawed.

However a solution is possible by making a small adjustment, using vlookup, and also using some appropriate scripting for duplicating sheets.

Dynamic Lookup

Let's assume that the name of a given "Sheet1" called "shot_001" is found in cell A1.

The formula in "shot_001" Cell C3: =vlookup(A1,All_shots!$A$1:$B$67,2,0)

  • the value in cell A1 (the sheet name) is the search key.
  • the range is always "All_shots!$A$1:$B$67". Note the absolute references for the range values. This will ensure that the same range is used when "shot_001" is duplicated.
  • the index = 2; the value from column B of "All_shots"
  • the final parameter: is sorted = FALSE. This will ensure a search for an exact match for the search key.

Duplicating the template

Webapps Q&A: Duplicate and rename sheet in one action on Google Sheets provides some code to duplicate a and rename a sheet; I have added a routine to insert the new sheet name into cell A1 of the new sheet.

The script creates a duplicate, one-at-a-time based on the active sheet and it requires user input.

An alternative would be to create all the duplicates (based on the names in Column A of "All shots") in a single pass. I note that this is the subject of your question Duplicate and rename vs make a blank sheet and name.

function dupName() {
  // https://webapps.stackexchange.com/a/89990/196152
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var name = Browser.inputBox('Enter new sheet name');
  ss.insertSheet(name, {template: sheet});
  var newsheet = ss.getSheetByName(name)
  var target = newsheet.getRange("A1");

Sheet: All_shots

All Shots


Shot 001

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.