stefanaalten
Board Regular
- Joined
- Feb 1, 2011
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
Hi, I've been struggling with this convoluted "problem" for some time. Hope someone can help! It involves references to images, sorting orders, and all sorts. I'll try to be as brief as I can. What I'm trying to do is to have images inserted or referenced in one worksheet displayed on another worksheet.
My Films spreadsheet as two main worksheets: "List" and "Directory".
"List" contains a list of films, one per row, starting at row 32, headers in row 31. Column A contains film title, columns B-AK hold other info about the film. Column AL is where I would like to place either an image (insert-picture) or a reference to an image (insert-hyperlink, with the link pointing to a file in a local subfolder, i.e. a local reference, not a web/external URL). I don't know which is best but happy to stick with one approach only, i.e. image itself or image reference. My list has filters (i.e. in the header row), and I regularly use these to sort on multiple columns and to provide a subset to work on (e.g. all films in French language, sorted by year).
"Directory" contains the films (by referring to the entries in List) in a different layout, for printing as a booklet, showing 5 films per printed page. So the Directory is just a collection of references to the List worksheet and does not contain any original data itself. Each film is presented in a small "panel", 11 rows by 10 columns. The first row in each panel holds the film title and director, the second row contains other info relating to the film, and the remaining rows have been merged into two big cells to provide a space for a synopsis of the film and an image, like this:
....|.A.|..B..|.C.|.D.|.E.|.F.|.G.|.H.|...I...|.J.|
----+----------------------------------------------
111 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
112 |...|Title|...|...|...|...|...|...|Direct.|...|
---------------------------------------------------
113 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
114 |...|.............................|.......|...|
115 |...|.............................| space |...|
116 |...|.............................|for img|...|
117 |...|..space for film synopsis....|(cells.|...|
118 |...|.(cells B114-H121 merged)....|I114-..|...|
119 |...|.............................|.I121..|...|
120 |...|.............................|merged)|...|
121 |...|.............................|...... |...|
---------------------------------------------------
122 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
123 |...|Title|...|...|...|...|...|...|Direct.|...|
---------------------------------------------------
124 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
125 |...|.............................|.......|...|
126 |...|.............................| space |...|
127 |...|.............................|for img|...|
128 |...|..space for film synopsis....|(cells.|...|
129 |...|.(cells B125-H132 merged)....|I125-..|...|
130 |...|.............................|.I132..|...|
131 |...|.............................|merged)|...|
132 |...|.............................|...... |...|
---------------------------------------------------
etc., repeating the same 11 row pattern over and over.
The references in the Directory worksheet to the List spreadsheet are done like this, for example in cell Directory!B112:
=INDIRECT("List!A" & INT((ROW()-111)/11)+32)
I would like to include the image in the List worksheet in the matching "panel" in the Directory worksheet. So Directory!I114 should contain the image inserted in/referred to in List!AL32, and Directory!I125 should contain the image inserted in/referred to in List!AL33, and so on ... i.e. the actual image should be displayed.
I can pre-size the images to make sure they use the available space in the merged cells in the Directory worksheet.
When I apply a set of filters and sort order on the List worksheet this is reflected in the Directory worksheet, which is how it should work.
Can anyone help with this "problem", specifically with how I can get the images from the List worksheet to show on the Directory worksheet?
Thanks for reading this far! Sorry it's such a long post!
Many thanks in advance.
My Films spreadsheet as two main worksheets: "List" and "Directory".
"List" contains a list of films, one per row, starting at row 32, headers in row 31. Column A contains film title, columns B-AK hold other info about the film. Column AL is where I would like to place either an image (insert-picture) or a reference to an image (insert-hyperlink, with the link pointing to a file in a local subfolder, i.e. a local reference, not a web/external URL). I don't know which is best but happy to stick with one approach only, i.e. image itself or image reference. My list has filters (i.e. in the header row), and I regularly use these to sort on multiple columns and to provide a subset to work on (e.g. all films in French language, sorted by year).
"Directory" contains the films (by referring to the entries in List) in a different layout, for printing as a booklet, showing 5 films per printed page. So the Directory is just a collection of references to the List worksheet and does not contain any original data itself. Each film is presented in a small "panel", 11 rows by 10 columns. The first row in each panel holds the film title and director, the second row contains other info relating to the film, and the remaining rows have been merged into two big cells to provide a space for a synopsis of the film and an image, like this:
....|.A.|..B..|.C.|.D.|.E.|.F.|.G.|.H.|...I...|.J.|
----+----------------------------------------------
111 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
112 |...|Title|...|...|...|...|...|...|Direct.|...|
---------------------------------------------------
113 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
114 |...|.............................|.......|...|
115 |...|.............................| space |...|
116 |...|.............................|for img|...|
117 |...|..space for film synopsis....|(cells.|...|
118 |...|.(cells B114-H121 merged)....|I114-..|...|
119 |...|.............................|.I121..|...|
120 |...|.............................|merged)|...|
121 |...|.............................|...... |...|
---------------------------------------------------
122 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
123 |...|Title|...|...|...|...|...|...|Direct.|...|
---------------------------------------------------
124 |...|.....|...|...|...|...|...|...|.......|...|
---------------------------------------------------
125 |...|.............................|.......|...|
126 |...|.............................| space |...|
127 |...|.............................|for img|...|
128 |...|..space for film synopsis....|(cells.|...|
129 |...|.(cells B125-H132 merged)....|I125-..|...|
130 |...|.............................|.I132..|...|
131 |...|.............................|merged)|...|
132 |...|.............................|...... |...|
---------------------------------------------------
etc., repeating the same 11 row pattern over and over.
The references in the Directory worksheet to the List spreadsheet are done like this, for example in cell Directory!B112:
=INDIRECT("List!A" & INT((ROW()-111)/11)+32)
I would like to include the image in the List worksheet in the matching "panel" in the Directory worksheet. So Directory!I114 should contain the image inserted in/referred to in List!AL32, and Directory!I125 should contain the image inserted in/referred to in List!AL33, and so on ... i.e. the actual image should be displayed.
I can pre-size the images to make sure they use the available space in the merged cells in the Directory worksheet.
When I apply a set of filters and sort order on the List worksheet this is reflected in the Directory worksheet, which is how it should work.
Can anyone help with this "problem", specifically with how I can get the images from the List worksheet to show on the Directory worksheet?
Thanks for reading this far! Sorry it's such a long post!
Many thanks in advance.
