Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-06-2020, 07:08 AM
kilroyscarnival kilroyscarnival is offline Chart with Dynamic Array Windows 7 64bit Chart with Dynamic Array Office 2010
Expert
Chart with Dynamic Array
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default Chart with Dynamic Array


We recently changed to Excel (Office) 365.

I've been tasked with creating a template for a series of line charts that will be based on dynamic arrays. I watched some excellent tutorials online, but I'm having a problem getting the chart to accept the input of a dynamic array.

I started by converting my data into a table. I tried using an Index (Match): Index(Match) formula to select the rows between two mileposts (numbers) that can be chosen with two data validation dropdowns. Milepost will be my horizontal axis; there will be anywhere from 4 to 10 columns of data that will be plotted. 365 shows me spilled results that are correct. But when I attempt to make that array formula a named range in Name Manager, and then use that Name in my horizontal access, I get "there's an error in the formula you entered."

=INDEX(Milepost,MATCH(hma_Data!$P$2,Milepost,0)):I NDEX(Milepost,MATCH(hma_Data!$P$3,Milepost,0))

I see that it's replaced selected range (hma_Data!$A$2:$A$500) to the named range "Milepost."

I watched another video that seemed to based the chart on the actual spill array, by selecting the cell where the spill started, and using that cell location ($R$2 in my case) with the # sign. I made this a named range. The chart will not accept that named range either, nor the direct formula of R2#.

I feel like I'm missing the smallest most obvious thing to get it to 'stick' in the chart. Blindspot!

Thanks for any suggestions,

Ann
Reply With Quote
  #2  
Old 10-06-2020, 10:37 PM
ArviLaanemets ArviLaanemets is offline Chart with Dynamic Array Windows 8 Chart with Dynamic Array Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

A foolproof way to define dynamic named ranges is to use OFFSET().

E.g.
Code:
=OFFSET(AnchorPoint, RowsDown, ColumnsRight, NumberOfRows, NumberOfColumns)
where
AnchorPoint is address of some single cell which you are sure it will never be deleted, and is above or in same row as the range you want to define, and left or in same column as the range you want to define;
RowsDown is the number of rows from AnchorPoint to topmost row of range you want to define;
ColumnsRight is the number of columns from AnchorPoint to leftmost column of range you want to define;
NumberOfRows is the number of rows you want in defined range;
NumberOfColumns is the number of columns you want in defined range.

E.g. you want to define a range P5:P1, anchored to cell P1:
Code:
=OFFSET($P$1,4,,6,1)
To make the range dynamic, you have to replace at least one of parameters to be calculated by formula.

To get any detailed help you have to attach some example table here.
Reply With Quote
  #3  
Old 10-07-2020, 06:33 AM
kilroyscarnival kilroyscarnival is offline Chart with Dynamic Array Windows 7 64bit Chart with Dynamic Array Office 2010
Expert
Chart with Dynamic Array
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by ArviLaanemets View Post
A foolproof way to define dynamic named ranges is to use OFFSET().
Thank you for your help! Yes, my formulas already had offset in them for the columns of data dependent on the original dynamic array. And according to the spilled data, they were working perfectly. I think I was having trouble describing my issue, and I was going to clean up an example to upload and I think finally solved my own problem, so I think you're partly responsible.

My trouble was getting the chart to "read" or accept the named range. Finally tried making all of the named ranges under the scope "Worksheet" rather than the default, "Workbook." And with that, the chart is finally working properly.

I'm going to go back and try again, creating the named ranges on "Workbook" scope but apparently the one thing that maybe I was missing initially was ='SHEETNAME'! before my named range in the chart. I could have sworn the example I watched on a video didn't have that detail.

Thank you,

Ann
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a dynamic array for some formulas Bumba Excel 2 01-27-2019 05:10 AM
Fixing legend of a dynamic chart Toonook Excel 0 08-25-2016 04:46 PM
Chart with Dynamic Array Dynamic chart ranges GlowingApple Excel 1 02-17-2016 07:18 PM
Generating a chart with dynamic series Officer_Bierschnitt Excel 3 10-15-2015 12:30 AM
Dynamic Chart with Non Continuous data swati_04 Excel 0 03-02-2015 12:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft