Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
Hi Folks:
Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
On Apr 24, 11:20 pm, "S Boak" wrote:
Hi Folks: Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards The pictures are on sheet 1 but all bar the one whose name is in F1 are not visible because their Visible property has been set to False. You could add your own pictures by simply pasting them into sheet 1 and including their detail in the lookup table. Also you can delete unwanted pictures by deleting them after making them visible and deleting their details from the lookup table. You can render all the pictures visible by temporarily commenting out the second line (Me.Pictures.Visible = False) then separately selecting each picture from the A1 drop down. That way all the pictures will end up visible and on F1. Ken Johnson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
Ken:
Many thanks . . . part way there. Made the pictures visible, added a pic of my own, and expanded the lookup table, but the new pic won't come up. I'm missing something I'm sure. How to I label or tag the pic I added?? Thanks Steve "Ken Johnson" wrote in message ... On Apr 24, 11:20 pm, "S Boak" wrote: Hi Folks: Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards The pictures are on sheet 1 but all bar the one whose name is in F1 are not visible because their Visible property has been set to False. You could add your own pictures by simply pasting them into sheet 1 and including their detail in the lookup table. Also you can delete unwanted pictures by deleting them after making them visible and deleting their details from the lookup table. You can render all the pictures visible by temporarily commenting out the second line (Me.Pictures.Visible = False) then separately selecting each picture from the A1 drop down. That way all the pictures will end up visible and on F1. Ken Johnson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
Ken:
A missing piece of info . . . The data-val cell has the formula =OFFSET(PicTable,,,,1) I understand OFFSET a little, but this is beyond me. Steve "S Boak" wrote in message ... Ken: Many thanks . . . part way there. Made the pictures visible, added a pic of my own, and expanded the lookup table, but the new pic won't come up. I'm missing something I'm sure. How to I label or tag the pic I added?? Thanks Steve "Ken Johnson" wrote in message ... On Apr 24, 11:20 pm, "S Boak" wrote: Hi Folks: Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards The pictures are on sheet 1 but all bar the one whose name is in F1 are not visible because their Visible property has been set to False. You could add your own pictures by simply pasting them into sheet 1 and including their detail in the lookup table. Also you can delete unwanted pictures by deleting them after making them visible and deleting their details from the lookup table. You can render all the pictures visible by temporarily commenting out the second line (Me.Pictures.Visible = False) then separately selecting each picture from the A1 drop down. That way all the pictures will end up visible and on F1. Ken Johnson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
On Apr 25, 2:04 am, "S Boak" wrote:
Ken: A missing piece of info . . . The data-val cell has the formula =OFFSET(PicTable,,,,1) I understand OFFSET a little, but this is beyond me. Steve "S Boak" wrote in message ... Ken: Many thanks . . . part way there. Made the pictures visible, added a pic of my own, and expanded the lookup table, but the new pic won't come up. I'm missing something I'm sure. How to I label or tag the pic I added?? Thanks Steve "Ken Johnson" wrote in message ... On Apr 24, 11:20 pm, "S Boak" wrote: Hi Folks: Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards The pictures are on sheet 1 but all bar the one whose name is in F1 are not visible because their Visible property has been set to False. You could add your own pictures by simply pasting them into sheet 1 and including their detail in the lookup table. Also you can delete unwanted pictures by deleting them after making them visible and deleting their details from the lookup table. You can render all the pictures visible by temporarily commenting out the second line (Me.Pictures.Visible = False) then separately selecting each picture from the A1 drop down. That way all the pictures will end up visible and on F1. Ken Johnson Hi Steve, When you paste in one of your pictures Excel gives it a generic name eg Picture 8. When that picture is selected its given name appears in the Name Box that is on the left side of the Formula Bar (make sure the Formula Bar is visible). Say Picture 8 is a picture of "Orson CART" and you want its visibility to be controlled by the code. All you need to do is add "Orson CART" to column 1 of the PicTable and "Picture 8" to column 2 of the same row in PicTable (both without the speech marks). Then, when you type "Orson CART" into A2 on Sheet1 the formula in F1 is calculated to be "Picture 8" then the Code is run. The code first makes all pictures on Sheet1 invisible then it loops through all the pictures and makes visible only that one with its given name in F1. One thing to check is that the Named Range PicTable actually contains the information you typed in (Orson CART and Picture 8). If PicTable is not a Dynamic Named Range then its address might need to be extended to included added rows of information. This is done in the Refers to: box on the Define Name dialog, which you get to by going Insert|Name...|Define. Ken Johnson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
On Apr 25, 9:48 am, Ken Johnson wrote:
On Apr 25, 2:04 am, "S Boak" wrote: Ken: A missing piece of info . . . The data-val cell has the formula =OFFSET(PicTable,,,,1) I understand OFFSET a little, but this is beyond me. Steve "S Boak" wrote in message ... Ken: Many thanks . . . part way there. Made the pictures visible, added a pic of my own, and expanded the lookup table, but the new pic won't come up. I'm missing something I'm sure. How to I label or tag the pic I added?? Thanks Steve "Ken Johnson" wrote in message ... On Apr 24, 11:20 pm, "S Boak" wrote: Hi Folks: Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards The pictures are on sheet 1 but all bar the one whose name is in F1 are not visible because their Visible property has been set to False. You could add your own pictures by simply pasting them into sheet 1 and including their detail in the lookup table. Also you can delete unwanted pictures by deleting them after making them visible and deleting their details from the lookup table. You can render all the pictures visible by temporarily commenting out the second line (Me.Pictures.Visible = False) then separately selecting each picture from the A1 drop down. That way all the pictures will end up visible and on F1. Ken Johnson Hi Steve, When you paste in one of your pictures Excel gives it a generic name eg Picture 8. When that picture is selected its given name appears in the Name Box that is on the left side of the Formula Bar (make sure the Formula Bar is visible). Say Picture 8 is a picture of "Orson CART" and you want its visibility to be controlled by the code. All you need to do is add "Orson CART" to column 1 of the PicTable and "Picture 8" to column 2 of the same row in PicTable (both without the speech marks). Then, when you type "Orson CART" into A2 on Sheet1 the formula in F1 is calculated to be "Picture 8" then the Code is run. The code first makes all pictures on Sheet1 invisible then it loops through all the pictures and makes visible only that one with its given name in F1. One thing to check is that the Named Range PicTable actually contains the information you typed in (Orson CART and Picture 8). If PicTable is not a Dynamic Named Range then its address might need to be extended to included added rows of information. This is done in the Refers to: box on the Define Name dialog, which you get to by going Insert|Name...|Define. Ken Johnson Hi Steve, You shouldn't have to worry about that OFFSET formula, it just extracts column 1 from the PicTable to use as the list shown in the data validation drop down. When you add new rows of information to PicTable, provided you increase its address to include the new rows, the OFFSET formula includes those new rows in the Data Validation drop down. Ken Johnson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't find pictures in a referenced formula
On Apr 25, 10:16 am, Ken Johnson wrote:
On Apr 25, 9:48 am, Ken Johnson wrote: On Apr 25, 2:04 am, "S Boak" wrote: Ken: A missing piece of info . . . The data-val cell has the formula =OFFSET(PicTable,,,,1) I understand OFFSET a little, but this is beyond me. Steve "S Boak" wrote in message ... Ken: Many thanks . . . part way there. Made the pictures visible, added a pic of my own, and expanded the lookup table, but the new pic won't come up. I'm missing something I'm sure. How to I label or tag the pic I added?? Thanks Steve "Ken Johnson" wrote in message ... On Apr 24, 11:20 pm, "S Boak" wrote: Hi Folks: Need some help with a reference in a workbook I found for looking up pictures - the book works great, but I owuld like to use other pictures, and I can't find where the pictures are stored. The book has 2 sheets - 1 and 2. Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on Sheet 2 - a simple 2 Column 4 Row lookup ) Sheet 1 F1 displays the piture and has a formula: =VLOOKUP(A2, PicTable, 2, FALSE) I understand the formula, but can't find the pictures . . . I'm bad with VB and the only code in the book I can find is: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub I wd like to use different pictures and learn how to drive this for what I need, but I can't find the pictures . . . . Would appreciate any help. Thanks / Regards The pictures are on sheet 1 but all bar the one whose name is in F1 are not visible because their Visible property has been set to False. You could add your own pictures by simply pasting them into sheet 1 and including their detail in the lookup table. Also you can delete unwanted pictures by deleting them after making them visible and deleting their details from the lookup table. You can render all the pictures visible by temporarily commenting out the second line (Me.Pictures.Visible = False) then separately selecting each picture from the A1 drop down. That way all the pictures will end up visible and on F1. Ken Johnson Hi Steve, When you paste in one of your pictures Excel gives it a generic name eg Picture 8. When that picture is selected its given name appears in the Name Box that is on the left side of the Formula Bar (make sure the Formula Bar is visible). Say Picture 8 is a picture of "Orson CART" and you want its visibility to be controlled by the code. All you need to do is add "Orson CART" to column 1 of the PicTable and "Picture 8" to column 2 of the same row in PicTable (both without the speech marks). Then, when you type "Orson CART" into A2 on Sheet1 the formula in F1 is calculated to be "Picture 8" then the Code is run. The code first makes all pictures on Sheet1 invisible then it loops through all the pictures and makes visible only that one with its given name in F1. One thing to check is that the Named Range PicTable actually contains the information you typed in (Orson CART and Picture 8). If PicTable is not a Dynamic Named Range then its address might need to be extended to included added rows of information. This is done in the Refers to: box on the Define Name dialog, which you get to by going Insert|Name...|Define. Ken Johnson Hi Steve, You shouldn't have to worry about that OFFSET formula, it just extracts column 1 from the PicTable to use as the list shown in the data validation drop down. When you add new rows of information to PicTable, provided you increase its address to include the new rows, the OFFSET formula includes those new rows in the Data Validation drop down. Ken Johnson Hi Steve, This is only a recommendation... Convert PicTable to a Dynamic Named Range so that you don't have to worry about changing its "Refers to" address every time a new row is added or subtracted. Just go Insert|Name...|Define, click on PicTable in the list of Names on the Define Name dialog, replace the formula in the Refers to: box with... =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,2) Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a formula to find a range for a cross-referenced value | Excel Worksheet Functions | |||
Find where else in a spreadsheet a cell is referenced with formula | Excel Worksheet Functions | |||
Missing linked file - can still be referenced but can't find it! | Excel Worksheet Functions | |||
Find Pictures and Hyperlink | Excel Discussion (Misc queries) | |||
Excel should find all places a particular cell is referenced | Excel Worksheet Functions |