i have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker
i would like to make a dynamic chart that updates the data ranges based on something like this: ....
I have a table in cells A1:B11. I have two drop down menus in cells G3 and H3. When I select red in G3 I need Bob, Sam and Sarah appear in the drop down menu in cell H3. Instead, with my current formula, only Sarah appears in the drop down in cell H3. When I select green in G3 I need Tommy, Bob, Sandy, Carl and Phil appear in the drop down menu in Cell H3. Instead with my current formula only Phil appears in the drop down in cell H3.
I linked cell H3 to the defined name Names which equals
OFFSET(INDEX(Sheet4!$B:$B, MATCH(Sheet4!$G$3, Sheet4!$A:$A,0),0),COUNTIF(Sheet4!$A:$A, Sheet4!$G$3)-1,0).
What should I add to this formula to make it work correctly?
ABCDEFGH
1RedBobRed2RedSamGreen3RedSarahBlueRedSarah4GreenTommy5GreenBob
6GreenSandy7GreenCarl8GreenPhil9BlueCarl10BlueSam11BluePhil
I want to use a dynamic range in order to use this formula below in column A as long as there is something entered in column H = SUMIF($N:$N,A5,$I:$I). any ideas on syntax and will this be using the offset function within excel. I know I have posted a couple times about getting this formula to replicate itself down certain columns as long as someone enters text in another. I looked into the offset reply information somone posted but it seemed a little over my head.
View 4 Replies View RelatedI have a worksheet where the number of rows will change on a weekly basis and I would like to print the sheet out without having to change the print range every week.
I have tried to use the OFFSET function in the print range in print setup but once this has been entered and printed, it changes to the actual print range the OFFSET returns.
Is there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?
For example, rather than this:
=OFFSET(H3,0,-7,1,1)
I want to do this:
=OFFSET(max(H:H),0,-7,1,1)
Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.
i'm running out an option, i must use offset or index function but there are problem found: 1.Offset function cannot use a dynamic height or number of columns and the result are #NAME?, here is the code
VB:
Private Sub CommandButton1_Click()Dim k As Variant
Dim b As Variant
Range(Me.RefEdit1).Name = "item"
[Code]......
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).
So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.
Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.
I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?
So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?
View 2 Replies View RelatedI have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..
View 6 Replies View RelatedI have three rows that each attempt to pull in data from a range on different worksheets. All but one return nothing but "#N/A" values. The one row returns values from the proper range. (User selections determine which row will have data).
Second, I successfully pull data from the row containing actual information into another row, using the following formula: "=OFFSET(E$36,CHOOSE($Y$1,0,1,2),0)" on a cell-to-cell basis. The value in $Y$1 chooses the row to look at, based upon a dropdown selected on another worksheet.
So I now have a row with =OFFSET(E$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(F$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(G$36,CHOOSE($Y$1,0,1,2),0) and so forth. I can find the last value in the row, but I cannot find a way to extract the address from that, and create a table that will use as a series the last 26 values in the row.
For reference, I use "=LOOKUP(9.99999999999999E+307,E49:FD49)" to determine the last cell with a value (gleaned from a Dueling Excel Youtube Video, - very helpful!).
I hope the above will be clear enough, but if not, I'll be happy to provide additional information / clarification.
Charting Data Based On Drop Down List Choice
I am trying to build a report where I can chart data base on a dropdown selection.I did build the dropdown lists, however I don't have any expertise on execel functions in order to make it work.I am attaching my draft report.
I have several dynamic charts using named ranges, but I am charting 1 series of data. What about when I have multiple data sets or series and do not want to use PivotTables? Do I really have to make each one a named range? If that's the case, using VBA looks much simpler.
View 2 Replies View RelatedI have a log where I'm recording daily readings of a pipette. Each day three readings are taken and I have each stored as a dynamic range so that I can monitor how in control my process is. However, when I graph it, I would like it to be one continuous line graph (Three readings from day one followed by three readings from day two, etc.). I'm having difficulty doing any sort of combination.
View 7 Replies View RelatedI am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
I have a spreadsheet which is generated by someone else. I am not allowed to edit or sort this data It is in the format:
Column A Names (Example Simon is in cell A11)
Column B emails (1 row down so a number would be in cell B12)
Column C Phone calls etc. (cell C12)
The data is generated automatically so one day Simon could be at the top and the next day Daniel etc.
I need to copy each set of data so I can produce a graph on a team report.
I would normally use OFFSET but as the NAME column could be in any order, the results appear for a different NAME
The data I need is always OFFSET by the same number of columns and rows so the data for Simon would be OFFSET by 1 row and 1 and 2 columns. The data always occupies the same cells in the sheet namely A11:F46.
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedI have two lists ("fulllist" & "code" - A1:A20 is a data validated dropdown list of "fulllist". I want B2:B20 to return a list item offset one column to the item selected in A1:20
So, if A:1 drop down equals "asphalt paving" I want B:2 to insert a code from a list on cell right of asphalt paving.
Something like =IF(B31<>"fulllist",=OFFSET("fulllist",0,1,,),"") This returns an error. I have a feeling I need to use match. I can't seem to find an answer to this and I bet it is fairly simple.
I know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...
-----A-----
1--Apple
2--Orange
3--Pear
4--Bannana
5--
6--
A1:A6 would be named Fruit, then name manager, edit: =OFFSET($A$1,0,0,COUNT($A1:$A6),1)
The Data Validation list then shows Apple, Orange, Pear, Bannana. And if I add Peach into A5 later, that then appears in the list.
What I need though is to edit this as if the named range 'Fruit' covers cells A1:F1.
------A---------B-------C--------D-----------E-----------F
1--Apple---Orange---Pear---Bannana---
2--
I have a data set that alters each week which means at the moment I have to update the ranges manually each time. I would like to use a dynamic range and think I need to use the Offset and CountIf functions. Both the start and end of the ranges are dynamic. The range is based on grouping the numbers in Column E (LocNo)
What I would like to do or think the way to achieve this is by:
First looking at Column E
Count how many cells are equal to criteria. (CountIf)
offset 5 columns left from the first cell that meets the criteria (Offset -5)
offset X rows down based on how many cells meet the criteria (Offset X)
I've include a spreadsheet spread sheet which 'should' help illustrate the problem showing the ranges I would like and how the data changes each week.
When I make a dynamic named range, I typically do something like:
=offset($A$1,,,counta(A:A),counta(1:1))
I remember awhile ago I saw (within some of the comments on Jon Peltier's site, I believe) a way to make a dynamic named range without using offset. Does anyone know how to do it?
why this isn't working?
=SUM(OFFSET(A4,0,3,1,(LOOKUP(9.99999999999999E+307,4:4))))
I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.
{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}
The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.
Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Table TMI:
A B C DClient
Account
NAM
Contact
Comments
[Code]..
Table with offset function:
A B C D
Chi
Account
NAM
Contact name
Comments
[Code]..
All client, NAM and account lists are dynamic !
I have three columns of data in columns A,B,C - there are formulas in these columns that either produce a numeric value or return blank.
I have created Dynamic Named Ranges for each of them and have entered the following formula:
=OFFSET(Working!$A$1,1,0,MATCH(1E+305,Working!$A:$A,1))
My issue is that this formula is looking one row past the last numeric entry in the column (a blank) which is leaving a blank space when I graph this data.
Essentially i'm trying to get the max of 5 cells at a time using the 'offset' reference. Here's my erroneous code:
Dim maxCell As Range
For Each maxCell In Range("y9:y150")
If maxCell.Value "" Then
maxCell.Offset(0, 1).Formula = "=max(maxCell.offset(0,-20): maxCell.offset(-5,-20))"
'maxCell.Offset(0, 1).Formula = "=max(maxCell.Offset(-5, -22) : (0, -22))"
End If
Next maxCell
I have two slightly different lines of code here (highlighted in red), one is commented, but i've been changing both to no avail.
I only want this to be triggered when the cell to the left (maxCell) is not empty, hence I use the offset feature. I've tried to use absolute references for the max formula, but then each cell has the same answer.
Is there a way to use the offset function with a cell reference for the first reference?
I have many columns of named ranges, each representing a month of financial statement data. I want to pull up certain cell values based on the months being compared.
For instance, if the user selects "May", I want to pull only the sales data for May 2013, May 2012 and May 2011. That data is in the 311 row of the columns for each individual month.
=OFFSET(ytdmay2013,311,0,1,1)
In short, I am using a vlookup to get the ytdmay2013 based on the chosen month of May in the input section. If the user then chooses June, the lookup will return ytdjun2013. I want to link the ytdjun2013 in the offset function.
I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.
New York (column header)
Wages
Lights
Net Profit
Boston
Lights
Wages
Sales
Net Profit
How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.
I am trying to create a dynamic Excel line chart.
All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg
I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.
However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.
I am in the process of setting up some graphs. The graphs will show the last 6 months of data so they move as each month is goes. I saw a slick way of doing this using the count function but this did not quite fulfill my needs. What I was looking for is to be able to input the start month in a cell. The cell would be part of the function within the formula. I created a simplified version of the spreadsheet below.
Spreadsheet.jpg
Then I created my names using the ctrl-F3
Names.jpg
I then created the graph I wanted and wanted to use the formula =SERIES(Sheet1!$B$3,!chtCat,!chtIssuesReported,1). This kept getting an error indicated nothing really. It basically says there is something wrong with my Series function. I have a working version of a similiar worksheet I have been basing my entries on. They look almost identical but mine is failing. I also created a new file and tried this and it still fails. I can run the formula evaluator within Excel 2010 against the !chtCat and !chtIssuesReported names and they both return the correct value (which equals areas on the spreadsheet). I have also tried to enter the spreadsheet name and a tab in front of the names to get them to work and still get an error. Basicall I am trying to create the graph below (this grpah is using the hard coded locations)
Graph.jpg
I use the standard OFFSET-COUNT-MATCH method to create dynamic named ranges in my Excel projects. Needless to say, this method won't work on a spreadsheet with formulas extending beyond the current range. The count function counts the cells containing formulas, even though they may contain no data. Does anyone know how to construct a formula that will IGNORE the "formula only" cells??
View 3 Replies View Related