Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obtaining cell reference from index function
I am attemtping to build custom screen tips for a scatter graph and already
been given some help. However I am stuck again. The following code extract myX = WorksheetFunction.Index _ (.SeriesCollection(Arg1).XValues, Arg2) ' Extract y value from array of y values myY = WorksheetFunction.Index _ (.SeriesCollection(Arg1).Values, Arg2) populates two variables (myX and myY) which correspond to the x and y values. What I am having trouble with is obtaining the cell reference to either offset from to obtain the data I wish to display in my custom screen tip. If for example, myX extracts a value from cell B3, i need to access B5 for my custom data. I must be getting close. Thanks to anyone who helps me in my (hopefully) final hurdle. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obtaining cell reference from index function
How about just:
myX = .SeriesCollection(Arg1).XValues(Arg2+2).Value HTH, Bernie MS Excel MVP "MH UK" wrote in message ... I am attemtping to build custom screen tips for a scatter graph and already been given some help. However I am stuck again. The following code extract myX = WorksheetFunction.Index _ (.SeriesCollection(Arg1).XValues, Arg2) ' Extract y value from array of y values myY = WorksheetFunction.Index _ (.SeriesCollection(Arg1).Values, Arg2) populates two variables (myX and myY) which correspond to the x and y values. What I am having trouble with is obtaining the cell reference to either offset from to obtain the data I wish to display in my custom screen tip. If for example, myX extracts a value from cell B3, i need to access B5 for my custom data. I must be getting close. Thanks to anyone who helps me in my (hopefully) final hurdle. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obtaining cell reference from index function
You are extracting a value that is derived from the value in B3. There is
nothing in your code that makes reference to B3. Perhaps take a look at this page by John Walkenbach http://www.j-walk.com/ss/excel/tips/tip83.htm A Class Module to Manipulate a Chart SERIES The code he offers shows how to get the range reference and then you can offset from there. -- Regards, Tom Ogilvy "MH UK" wrote in message ... I am attemtping to build custom screen tips for a scatter graph and already been given some help. However I am stuck again. The following code extract myX = WorksheetFunction.Index _ (.SeriesCollection(Arg1).XValues, Arg2) ' Extract y value from array of y values myY = WorksheetFunction.Index _ (.SeriesCollection(Arg1).Values, Arg2) populates two variables (myX and myY) which correspond to the x and y values. What I am having trouble with is obtaining the cell reference to either offset from to obtain the data I wish to display in my custom screen tip. If for example, myX extracts a value from cell B3, i need to access B5 for my custom data. I must be getting close. Thanks to anyone who helps me in my (hopefully) final hurdle. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Obtaining cell reference from index function
Thanks for your help, I have worked a solution though not quite as either
response suggested. Basically I created a new variable equal to the X Value & Y Value and performed a look-up on a column in my data-set where the x and y values are merged. I could not quite get to be able to offset from the series range to my required data. My solution my not be the neatest but works. It will mean changes to my data-set but I can live with that. Though I am of course happy to receive a better solution. "Tom Ogilvy" wrote: You are extracting a value that is derived from the value in B3. There is nothing in your code that makes reference to B3. Perhaps take a look at this page by John Walkenbach http://www.j-walk.com/ss/excel/tips/tip83.htm A Class Module to Manipulate a Chart SERIES The code he offers shows how to get the range reference and then you can offset from there. -- Regards, Tom Ogilvy "MH UK" wrote in message ... I am attemtping to build custom screen tips for a scatter graph and already been given some help. However I am stuck again. The following code extract myX = WorksheetFunction.Index _ (.SeriesCollection(Arg1).XValues, Arg2) ' Extract y value from array of y values myY = WorksheetFunction.Index _ (.SeriesCollection(Arg1).Values, Arg2) populates two variables (myX and myY) which correspond to the x and y values. What I am having trouble with is obtaining the cell reference to either offset from to obtain the data I wish to display in my custom screen tip. If for example, myX extracts a value from cell B3, i need to access B5 for my custom data. I must be getting close. Thanks to anyone who helps me in my (hopefully) final hurdle. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Obtaining Multiple Results Using Index/Match Functions | Excel Worksheet Functions | |||
Obtaining Multiple Results Using Index/Match Functions | Excel Worksheet Functions | |||
Obtaining Multiple Results Using Index/Match Functions | Excel Worksheet Functions | |||
Now With Index -- IF Function Does Not Work With Cell Reference | Excel Worksheet Functions | |||
Cell reference from previous index function | Excel Programming |