Camera Tool with Match

[Gift2women]

I have a spreadsheet with multiple tabs: Sheet1 is Summary and Sheet2 - Sheet20 are all the details (but from here on, we will focus on Sheet1 and Sheet2 as Sheets3-20 will follow the same logic as Sheet2, but I don't want to be told that if it is only one sheet why waste my time). Sheet1!B1:L1 contains the header that is used on all sheets. I am using the Camera tool so that a snap of the data can sit in B2 in Sheet2 (as I cannot use macros and (for cell sizing reasons) I don't want to do merged cells). Now in B3 in Sheet2 I want a snap of the detail row (Sheet1!B:L) where Sheet1!A:A matches Sheet2!A3; I added the snap manually and then tried to change the formula as =Index(Sheet1!$B:$L, Match(A3, Sheet1!$A:$A, 0)), but it says "This formula is missing a range reference or defined name." I tried this a couple of additional ways including with an Indirect/Match but was greeted by the same error message. Is there a way to add a formula to a camera reference?

Any help would be greatly appreciated. Thank you.

 

[Gift2women]

Perfect ... thanks. That makes sense, i just got wrapped up in my own head i guess. thanks again.

 

[Yongle]

This is a (late ) thought

Why did you eliminate VBA at the outset? Is it because you do not want the workbook to contain macros when used by others?

VBA (held in a different workbook) could be used to create the structure including all Named Ranges, all "snaps" and their formulas
- let me know if that is of interest and I will post suitable code

 

[Gift2women]

That's an interesting thought, keeping the VBA in another sheet, which would work if I had more control over the spreadsheets ... but there are those who if they are not using a sheet think they should move/delete these files and leave those of us who create stuck in purgatory. That is a great thought for future projects (that I can retain some sort of ownership on) ... can't say I ever considered that before when I have had the issue of not wanting to have an xlsm file. Thanks for the idea, but I think the previous assistance gets me where I need to go. Thanks again.

 

[jkpieterse]

If you ever consider going that route; I have written quite som e articles on Excel add-ins. Check out:
https://jkp-ads.com/Articles/buildexceladdin.asp
https://jkp-ads.com/Articles/DistributeMacro00.asp
https://jkp-ads.com/Articles/UpdateAnAddin.asp
https://jkp-ads.com/Articles/FixLinks2UDF.asp
https://jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp