Google-sheets – I need a script/equation to reference sheetname to trigger info being pulled into a cell from another sheet

google sheets

Trying to figure out what I think is an "if/then" command. But I'm hopeless at writing them.

I have a template that I've built for one sheet and a second sheet with all the info to be referenced.

Sheet1 is named "FO_VFX_010"

Sheet2 is named "All_shots"

"All_shots" contains columns that list all the shots in the sequence, (FO_VFX_010 to FO_VFX_670) counting by 10's so 010, 020, 030, 040 etc. This is located in cells A2-A68

B2-B68 contain thumbnail images. One for each shot.

example

What I want to figure out is this. Is there a way to have a script or an equation in "FO_VFX_010" that basically says, if the sheetname is "this" (the same shot name as in column A on "All_shots" ) then pull the thumbnail from Column B for the corresponding shot and display it in this cell on "FO_VFX_010".

The reason is, I'm building "FO_VFX_010" as a template, that will be duplicated and renamed for each shot in the show. So separate tabs for "FO_VFX_010", "FO_VFX_020", "FO_VFX_030" etc. As each tab is created I want the script or equation to look at the sheetname and update to the correct thumbnail.

End result will display the thumbnail in a merged cell located on "FO_VFX_010" covering merged cells C3,C4,C5D3,D4,D5. I assume it would be looking for the address of FO_VFX_010!(C3).

Does that make sense? is that even possible??

Someone on reddit pointed me in this direction, but I do not have the needed coding ability to transform it into functional code:

function putImageInCell() {
  let url ='https://www.google.com/images/branding/googlelogo/1x/googlelogo_color_272x92dp.png'; 
  const ss = SpreadsheetApp.getActiveSpreadsheet(); 
  const s = ss.getSheetByName('Sheet1'); 
  const r = s.getRange('A1'); 
  r.setFormula(=image("${url}")); 
  SpreadsheetApp.flush(); 
  r.copyTo(r,{contentsOnly: true}); 
  SpreadsheetApp.flush(); 
}

I can kit-bash code, but man, I can't write it to save my life.

So any suggestions from you nice folks would need a little handholding of..paste this here, add this command in this cell here to make magic happen.

Best Answer

Welcome. You have described a rather complex web of sheets, cross-links, merges, etc. Possibly there is an ulterior motive driving this, but this is not apparent from the question. May I offer an answer that appears to satisfy your requirements but does not require any complex coding or additional sheets. The solution is based on use of vlookup

Sheet = "FO_VFX_010"

  • Cell C2

    • Make this a drop down containing shot-names.
      • From the main menu, select Data, Data validation.
      • Select "List from a range", range = "All_shots!A2:A68"

  • Cell C3

    • insert =vlookup(C2,All_shots!$A$2:$B$68,2,0)

  • Selecting the shot-name from the drop-down will cause the relevant image will appear.

Other notes

  • You don't have to merge the cells in columns C and D. You can increase the size of the image by changing the column width and/or row height.
  • Naming Sheet1 as "FO_VFX_010" is going to create confusion; perhaps it could be renamed "Image Analysis" or similar
  • The shot-names in cells A10-A176 aren't required. There should be only one set of shot-names, and these are found on "All-shots".
Related Topic