Forum Discussion
Picture lookup
With a INDIRECT formule should it be possible to lookup a picture
But I keep getting the message that with the INDIRECT function. " The reference is invalid"
I can't find what's wrong
In Quotation!D16 shows the validation list, and get's it data from Helper!AB1:150.
Picture1 has an INDIRECT(Quotation!$D$16) formula (define names)
Quotation!C16 should show the right picture if I name this Cell "Picture1", according to the explanation I followed on the internet from Miss(es) Gharani.
All the named cells that contains the picture are named, and are Table1 (Pricelist! Tab) and is checked.
I can't find what I'm doing wrong!?!
jomynitima Have a go at the attached file. After a closer look, I discovered that spaces and hyphens were not the only issues in the product names. Named Ranges don't like + and / either and may not start with a number. If the product is called "1+ seater / ", the named range becomes "_1__seater___".
Fixed all of this with the help of Find and Replace in an extra column in the price list table. But please check. I may have overlooked some.
The pictures in the quotation tab will change when the selected product in the dropdown changes. The INDIRECT formulae are now linked to cells in column N. These contain the exact names of the Named Ranges.
BUT...... your biggest challenge is to fix the picture format (size, cropping and placement in the cells) in the price list. I don't know why, but this seems be a crucial element in making this trick work. It works now for the first 8 products in your list. Select any of the other and you see only a portion of the picture (which may just be the white background).
Perhaps you should experiment first on a smaller scale (size, alignment), before trying to re-build your file with over 400 products. And check the video again. I recall she mentioned that the pictures were to set so that they do not move and size with the cells. Not sure though. Good luck!
3 Replies
Found the video you refer to on YT. Quite some interesting tricks. Looked at your sheet and concluded that the issue lies in the Named Ranges. These only allow regular characters and underscores "_".
You can see that in the named ranges that exist in your schedule. For example:
CABIN_3_SEATER_steel_legs_CB_3SR_L_000_0When you then select
CABIN 3-SEATER steel legs CB-3SR-L-000-0in D16 on the Quotation sheet, the picture on the left should refer to the Named Formula "Picture1", being:
=INDIRECT(Quotation!$D$16)Since the content in D16 does not match any existing Named Range, you get an error "Reference is not valid".
Changed the dropdown item D16 (replaced all spaces and hyphens by underscores) and managed to get the correct picture. But it was cropped to the top left corner. Fiddled around with cropping/resizing /positioning the one picture in the price list to make it work.
But you probably don't want to show the description like in the picture. Then you need to link the Named Formula "Picture1" to a hidden cell that does have the correct reference to a named range. In turn, that cell should get its value from the selection in D16 (with the customer friendly description).
I hope you find these comments helpful. If not, I at least learned something of it myself.
Thanks Riny for you're reply, but,...
I changed the spaces and hyphens by underscores. No succes.
I even deleted the productcode from some of the items so have just one tekst-line (ore should it be the complete list?). Still no changing pictures. It's driving me crazy already for 2 weeks.
Could you sent me the fixed file back so can have a look at it.
Thanks for trying to help
John
jomynitima Have a go at the attached file. After a closer look, I discovered that spaces and hyphens were not the only issues in the product names. Named Ranges don't like + and / either and may not start with a number. If the product is called "1+ seater / ", the named range becomes "_1__seater___".
Fixed all of this with the help of Find and Replace in an extra column in the price list table. But please check. I may have overlooked some.
The pictures in the quotation tab will change when the selected product in the dropdown changes. The INDIRECT formulae are now linked to cells in column N. These contain the exact names of the Named Ranges.
BUT...... your biggest challenge is to fix the picture format (size, cropping and placement in the cells) in the price list. I don't know why, but this seems be a crucial element in making this trick work. It works now for the first 8 products in your list. Select any of the other and you see only a portion of the picture (which may just be the white background).
Perhaps you should experiment first on a smaller scale (size, alignment), before trying to re-build your file with over 400 products. And check the video again. I recall she mentioned that the pictures were to set so that they do not move and size with the cells. Not sure though. Good luck!
