Google-sheets – google sheets if/then formula. 2 sheets. Cell in Sheet 1 filled with info from sheet 2. If sheet 2 A1 = Sheet1 sheetname, then fill with Sheet 2 B1

google sheets

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.

Best Answer

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");
  target.setValue(name);
}

Sheet: All_shots

All Shots

Sheet:shot_001

Shot 001

Related Topic