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.

 

[Yongle]

The way to do this is to
- attribute a Named Range with RefersToRange formula returning required range to the image in each sheet

In case you are not familiar with the offset function, its syntax is =OFFSET(reference, rows, cols, [height], [width])

Stage1
1. Activate Sheet2
2. Create Named Range snap2 with RefersToRange =OFFSET(Sheet1!$B$1,MATCH($A$3,Sheet1!$A:$A,0)-1,,,11)
3. Create your "camera snap" in sheet 2, click on the image and in formula bar amend formula to =snap2

Does that give the correct result? If not let me know, otherwise move onto Stage2

Stage2
The above can be replicated for all sheets to provide your solution as follows
- (for efficiency) create ALL Named Ranges for ALL sheets before attributing any to images
- selecting the sheet each time forces Excel to automatically put the correct sheet reference before $A$3 in each Named Range alowing you to paste the same formula into the RefersToRange
- select sheet3, create Named Range snap3
- select sheet4, create Named Range snap4
... etc for all sheets
- attribute Named Ranges snap3...snapX to all images

Is there a less cumbersome way...
When I get some time (next week maybe?), I will investigate further
Something I tried but it did not work
- create worksheet scope Named Ranges snap for each sheet (ie lots of Named Ranges ALL with name snap)
- naively I thought that the same formula (=snap) could then be used for each image
- which would allow copying and pasting the image (with that formula already attributed) from one sheet to another
- I expected Excel would choose snap pertaining to that sheet
- but Excel baulked when the image was copied from one sheet to another
- Excel did not like choosing from duplicated names
- parallel names are allowed but this specific usage of them fails
If I make progress of any real value I will update the thread

 

[Gift2women]

What type of sorcery is this? That is awesome, THANK YOU! Took me a few attempts to get it right, but I think I'm just about there. There is one caveat though: say if the match fails, it seems to just stay with the value it last matched; any way to make this #NA or something so I know it is wrong? Stage2 is a bit cumbersome, I was trying to make the name relative (not putting a reference to the sheet name in the first part of match, but it auto-fills), but I think I can deal with it as is (unless you have a better solution). Thank you again.

 

[jkpieterse]

Wrap the formula of the range name with an IFERROR and have the ELSE part of the IFERROR point to a range of cells which is either empty or contains a message stating the item was not found.

 

[Yongle]

I will think about your N/A issue and test a few things when I get back to my PC
- perhaps wrap the formula in IFERROR or in IFNA
- I do not know what that does when formula is assigned to an image

ah - someone else has stepped in with same suggestion - must be correct
thanks @jkpieterse

 

[Gift2women]

edit:
Looks like I got ****y, that doesn't seem to do the trick. I get a Reference isn't valid. message if it doesn't match ... pretty much if i set the IfError while the match on A3 is bad, it gives me the result I want "No Results Found", but if I change A3 to something else that should match, it still shows as "No Results Found" ...

original:
Thank you, that did the trick. I had tried it, but on the reference to snap2, not on the Name Manager apparently (as that works and the other does not). Thanks again.

 

[jkpieterse]

Please show us the formula in Name Manager.

 

[Gift2women]

Pretty sure it is as follows (always fun changing it to the generic version to match my original post).

=IFERROR(OFFSET(Sheet1!$B$1,MATCH($A$3,Sheet1!$A:$A,0)-1,,,11), Sheet1!$B$3:$L$3)

 

[jkpieterse]

What happens if you enter that formula in a normal range of cells (as an array formula)? Does it show the expected results?

 

[Yongle]

Try this
- wrap the IFERROR around the function that errors, the offset required is 2 rows to take range to row 3

=OFFSET(Sheet1!$B$1,IFERROR(MATCH(Sheet2!$A$3,Sheet1!$A:$A,0)-1,2),,,11)