0

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.

0

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".
7
  • the reason for the specific naming, cell merging et all is specific to a visual esthetic I'm trying to cultivate. here's a screen shot of what I have so far i.imgur.com/wBH08qX.jpg – Hackofalltrades Dec 29 '20 at 4:00
  • Okay reading the instructions again. Is there any way to do exactly what you've described but without a manual selection of a drop down data validation? The reason being, is my end goal would be to make one master template, "FO_VFX_010" have everything magically pathed, data validated etc... but then, when I duplicate my template and rename it to "FO_VFX_020" everything shifts automatically to the right image? I'm trying to have no manual input once the template is built. In some situations I might have 20 tabs, in others I might have 500. It's for a movie Visual Effects shot tracker. – Hackofalltrades Dec 29 '20 at 4:09
  • Also right now I have a custom script that is in a hidden cell that does a sheetname lookup. Cell C2 get's it's information from the name of the sheet. from that, cells I3, and I4 get their data. Cells A10-A76 are doing a conditional format rule to turn Blue if their name matches C2. So I'm trying to make it all based off the sheet name. Change that, it does it all! – Hackofalltrades Dec 29 '20 at 4:11
  • Actually, your C3 vlookup, should work regardless of the data validation, right? you were only doing the data validation to generate the anem based on All_shots!A2:A68. But if I'm already providing that information via my other script that's pulling in the C2 field from the sheetname.. then it "should" work! Let me play around with it in a little bit and see if I'm reading this right! THANK YOU! – Hackofalltrades Dec 29 '20 at 4:46
  • OMG it totally works!! YOU ROCK! – Hackofalltrades Dec 29 '20 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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