what is wrong with this code??

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
I am trying to make a dynamic chart, here is my code, And I do not want an a website reference, i want to know what is wrong with my code and how to fix it.

=SERIES('WCM MC'!$A$29:$B$29,'Monte Carlo '!rngdates,'Monte Carlo '!ssninety,6)
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,436
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
You haven't actually said what your problem is, or what those ranges refer to. Also, does your 'Monte Carlo' worksheet actually have a space at the end of 'Monte Carlo '?
 
Upvote 0

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
Yes there is a space at the end of monte carlo, the ranges refer to dates and numbers, and the problem is i get different errors depending on how i change that code.
 
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,436
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Are those names defined locally to the monte carlo sheet? If not, they would normally be prefixed with the workbook name rather than a sheet name. Other than that the formula (not code) looks syntactically fine but unless you tell us what your actual error is, it's hard for us to pin down. It would also help if you gave the definition of the two names rather than just saying that they refer to dates and numbers.
 
Upvote 0

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
I was getting an invalid reference error. And no they are of workbook scope. Also, the rngdates refers to dates like 2008 etc. and the numbers are just seven digit numbers in a column.
 
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,436
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Then try using:
=SERIES('WCM MC'!$A$29:$B$29,'workbook_name.xls'!rngdates,workbook_name.xls'!ssninety,6)
For the range definitions, I was interested in the formula you are using to determine the ranges, not what's in the ranges. As you said it was a dynamic chart, I thought you would be using dynamic range names (i.e. defined using OFFSET or INDEX formulas, rather than straight cell references)
 
Upvote 0

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
i used offset, and i want it to be dynamic, should i change that??
 
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,436
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
No that should be fine as long as the OFFSET formula is correct.
 
Upvote 0

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
So would this be correct?? --
=SERIES('WCM MC'!$A$29:$B$29,'WCM_Monte_Carlo.xls'!rngdates,WCM_Monte_Carlo.xls'!ssninety,6)

Actually, I found that I have to do it this way, but i keep getting an invalid reference error:

=SERIES('[WCM Monte Carlo.xlsm]WCM MC'!$A$29:$B$29,'[WCM Monte Carlo.xlsm]Monte Carlo '!rngdates,'[WCM Monte Carlo.xlsm]Monte Carlo '!ssninety,6)
 
Last edited:
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,436
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Is the data in the same workbook as the chart? Any chance you could post a stripped down copy of the workbook somewhere or mail it? I'll have to double check the 2007 syntax later on, but it might still be useful to see the OFFSET formulas. (I'm assuming you do have 6 series of data)
 
Upvote 0

Forum statistics

Threads
1,191,717
Messages
5,988,257
Members
440,146
Latest member
rgomes8

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top