Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hi there,
I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hi,
No need for macro, it can be done with dynamic named ranges. Here is an example of scroll bar control content of chart http://www.andypope.info/charts/Scrolling.htm And links to other explanations of dynamic charting. http://peltiertech.com/Excel/Charts/Dynamics.html http://tushar-mehta.com/excel/newsgr...rts/index.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hey Andy,
Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hi,
The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
The formula that I was given before is highlighting the cells i'd like and i
have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hi Karine,
Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hi Andy,
I'm sorry those were my simple typos when i was writting my question, what you actually have as a recommondation is what I have on my data. These Formulas below are copied and pasted. =OFFSET(PWR!$B$3,0,COUNTA(PWR!$B$3:$P$3)-16,1,16) =OFFSET(PWR!$B$2,0,COUNTA(PWR!$B$2:$P$2)16,,0,-16) any other suggestions? Respectfully, Karine "Andy Pope" wrote: Hi Karine, Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
For clarification I am only doing the first 16 weeks for know.
Thank-you Karine "Andy Pope" wrote: Hi Karine, Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
Hi,
The second formula still looks like it is incorrect. Try these two Weeks: =OFFSET(PWR!$B$2,0,COUNTA(PWR!$2:$2)-16,1,16) Data: =OFFSET(PWR!$B$3,0,COUNTA(PWR!$3:$3)-16,1,16) If you are still having problems you can email the file to me direct. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hi Andy, I'm sorry those were my simple typos when i was writting my question, what you actually have as a recommondation is what I have on my data. These Formulas below are copied and pasted. =OFFSET(PWR!$B$3,0,COUNTA(PWR!$B$3:$P$3)-16,1,16) =OFFSET(PWR!$B$2,0,COUNTA(PWR!$B$2:$P$2)16,,0,-16) any other suggestions? Respectfully, Karine "Andy Pope" wrote: Hi Karine, Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
And i took that extra "," out. still a no go.
Karine "Karine" wrote: For clarification I am only doing the first 16 weeks for know. Thank-you Karine "Andy Pope" wrote: Hi Karine, Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
ok how do i send the file to you directly? it's still not working.
Thanks "Andy Pope" wrote: Hi, The second formula still looks like it is incorrect. Try these two Weeks: =OFFSET(PWR!$B$2,0,COUNTA(PWR!$2:$2)-16,1,16) Data: =OFFSET(PWR!$B$3,0,COUNTA(PWR!$3:$3)-16,1,16) If you are still having problems you can email the file to me direct. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hi Andy, I'm sorry those were my simple typos when i was writting my question, what you actually have as a recommondation is what I have on my data. These Formulas below are copied and pasted. =OFFSET(PWR!$B$3,0,COUNTA(PWR!$B$3:$P$3)-16,1,16) =OFFSET(PWR!$B$2,0,COUNTA(PWR!$B$2:$P$2)16,,0,-16) any other suggestions? Respectfully, Karine "Andy Pope" wrote: Hi Karine, Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
SCROLL BAR FOR A 52 WEEK CHART
andy AT andypope DOT info
change the text to form a true email address. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... ok how do i send the file to you directly? it's still not working. Thanks "Andy Pope" wrote: Hi, The second formula still looks like it is incorrect. Try these two Weeks: =OFFSET(PWR!$B$2,0,COUNTA(PWR!$2:$2)-16,1,16) Data: =OFFSET(PWR!$B$3,0,COUNTA(PWR!$3:$3)-16,1,16) If you are still having problems you can email the file to me direct. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hi Andy, I'm sorry those were my simple typos when i was writting my question, what you actually have as a recommondation is what I have on my data. These Formulas below are copied and pasted. =OFFSET(PWR!$B$3,0,COUNTA(PWR!$B$3:$P$3)-16,1,16) =OFFSET(PWR!$B$2,0,COUNTA(PWR!$B$2:$P$2)16,,0,-16) any other suggestions? Respectfully, Karine "Andy Pope" wrote: Hi Karine, Is this a simple typo? xsl should be xls =book.xsl!DATA Also you appear to have extra $ WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Try, WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... The formula that I was given before is highlighting the cells i'd like and i have attached the scroll bar to the cell, so thanks for that however, when i go to my chart options and put in for example =book.xsl!DATA in my X or Y axis, it gives me an error msg saying "A formula in this worksheet contains one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell ferencence" my formulas look like this, Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52) WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52) Whats wrong with my formula? In advance thanks again. Karine "Andy Pope" wrote: Hi, The OFFSET formula takes 5 arguments. OFFSET( <AnchorCell , <StartRow , <StartColumn , <NumberOfRows , <NumberOfColumns ) When dealing with data in row the StartRow and NumberOfRows are usually specified by formula. If your data is in Columns then you just need to modify the formula. You do not attach defined names to the scroll bar. The scroll bar is used to change a linked cell. The linked cells value is used as the Start or NumberOf parameter. Whether you change the Row or Column arguments depends on your data layout. So if your data is in B2:B20 for example the formula may look like this. =OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Karine" wrote in message ... Hey Andy, Thanks for the information, however the data I have in my tables is used for multiple graphs and the data goes across rather then down. I tried to do the Name Define but it keeps reading it downwards rather then across to get the data. Any tips? And how do I attach the defined names to the scroll bar? Thanks again, Karine "Karine" wrote: Hi there, I have many charts that are updated on a weekely bases (52 weeks) however currently I am going in manually and changing the cell rangers to update my graphs because I only need 10 weeks at a time showing, but I'd like the option to check the previouse weeks if need be. I know there is a way to add a scroll bar to the charts and drag that along to update the charts. I can't seem to figure it out the macro to run. I am using the Scroll Bar from the Forms toll bar. Any help would be great. Thanks, Karine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart data by day of week | Charts and Charting in Excel | |||
Errors with per Week Chart | Charts and Charting in Excel | |||
How do I make a to do chart for every day of the week? | Charts and Charting in Excel | |||
dynamic chart with scroll bar | Excel Discussion (Misc queries) | |||
scroll bar on chart disappears | Charts and Charting in Excel |