# 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

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.

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