Using OFFSET With Dynamic Data
Mar 26, 2014
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.
View 9 Replies
ADVERTISEMENT
Jul 23, 2014
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--
View 7 Replies
View Related
Dec 3, 2008
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.
View 5 Replies
View Related
Oct 15, 2009
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?
View 9 Replies
View Related
Jan 1, 2010
why this isn't working?
=SUM(OFFSET(A4,0,3,1,(LOOKUP(9.99999999999999E+307,4:4))))
View 9 Replies
View Related
Jun 24, 2006
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 Related
Sep 29, 2011
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
View 9 Replies
View Related
Aug 26, 2012
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.
View 3 Replies
View Related
Jul 19, 2014
I 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.
View 1 Replies
View Related
May 27, 2013
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]......
View 3 Replies
View Related
May 28, 2014
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.
View 5 Replies
View Related
May 18, 2007
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: ....
View 9 Replies
View Related
Jan 7, 2014
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
View 2 Replies
View Related
Dec 25, 2008
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
Apr 12, 2012
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"))
View 3 Replies
View Related
Jun 24, 2009
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
View 5 Replies
View Related
May 31, 2014
I 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.
View 3 Replies
View Related
Nov 1, 2006
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
View 9 Replies
View Related
Jul 8, 2006
I'm trying to use three nested Do..While loops to find data in a worksheet and consolodate it in a table. Individually the loops work, however when I nest them only the innermost loop works. The inner loop is written to exit when the Find value is Empty and the other two exit when firstaddress = latest address so they will only cycle through their range once.
Problem is when nested, the middle loop FindNext is Empty and exits on the first go, and the outer loop FindNext looks for the value of the cell above the first found cell, rather than what it should be looking for, and so creates an infinate loop. I thought this might be a problem with the ranges being redefined in each loop but respecifiing the range at every Find/FindNext hasn't solved it.
View 4 Replies
View Related
Jun 19, 2013
I have a dropdown list for data validation on the "display" tab. The dropdowns come from a named range on the "Parts List" tab. When you select a part from the data validation list on the "display" tab it displays the data along the same row from the "data" tab. What i am trying to do is create the logic that will allow it to also display the information below until it hits a new part.
For Example:
If I choose "part1" in cell A1 dropdown list on the "display" tab, it will display "a,1,2,3,4" from cells A3 to E3 (this data comes from "data" tab cells B1 to F1). I also want to include the information in the rows below until it hits a new part. The number of rows may vary. Something along the lines of IF(A1) show B1 to F1, then if A2=blank then show B2 to F2, then if A3=blank display B3 to F3, etc. I need this to be dynamic, if the cell below is blank then display row info.
View 5 Replies
View Related
Jan 11, 2013
Is it possible to use offset formula to move to the next row data? I am trying to achieve all H&S data will be captured in sheet 2. I know there is an easy way to do that by filtering it. But I am trying to automate the summary template in sheet by putting a formula from the data in sheet 1 below.
Code
Customer Unit Price
Err
0
[Code].....
View 1 Replies
View Related
Jul 16, 2006
How do I hardcode the Offset function? Right now I am placing the offset formula in Validation from the Data menu toolbar.
Also, I have two columns "Expense" and "Income" used for offset. Expense has 11 selections for the listbox, and Income has only 3. When I choose income from the dropdown in column B, I get way too many blank selections in the dropdown just because It doesnt contain as many items as Expense. How can I re-code adn hardcode the formula so I have only few blanks for my selection.
I have attached a copy "data validation test" to help make things more clear.
View 7 Replies
View Related
Apr 11, 2007
I need to create a macro wheather it be for each row or perhaps a dynamic macro that based on the active cell it can then offset based on my Expense type(Column B) into the respective row.
i had placed a sample of what i need to come up with highlighted in red. I need to copy the data in C7, C8, C9, etc... into D7:N7, D8:N8, etc... I have over 950 rows this code will need to be copied to. Columns do not move.
So if choose "fixed then i need for the macro to copy and paste data in each of those cells for Febuary to December. Should i choose "variable" then it places a "0" for Febuary to December and should I choose "NA" then nothing happens and i dont need code for that. It needs to be running constantly that way i dont need to run the macro so it needs to be in a module i beleive.
My problem is that i have three years of data and i have over 950 rows for expenses. THis is just a morsol. I cant place code in the cells as the expenses go there and it will just over write the code and is useless to me. That is why i need a macro that based on what i choose it then copies January's expense appropriately and for now it doesnt need to be dynamic but i prefer that it be based on the active cell if this is possible as i used over 950 option buttons and it worked but as the file gets bigger it crashes my computer.. 'Not enough Resources" .. gigs of ram and a 3.6 processor..
View 9 Replies
View Related
May 2, 2007
I am in need of perhaps a code that will allow me to offset the data which is highlighted in yellow to something that is similar to the data which is highlighted in green. The logic behind that is i do multiple loans that begin at various months of the year and must be presented into the business plan accordingly.
In this example "spreadsheet included" i chose payment 1 as January but some loans dont begin payment for 2 or more months. Hence the expense and hence the numbers located in the "Control Box" will then be able to offset the data down the number of rows that i specify in the control box at the upper left most of the screen into the section that is highligted in green respectively.
Once the data has been shifted i then need to transpose the data to every two columns in the far most payment schedule which includes the principle "prin" and the interest "int" in the respective months broken down. I need the ability to offset this data a maximum of 11 months as 12 would push the loan into a different year.
I have 15 simultaneous loans so i would need a macro that can do this for me. I am a beginner coding but very familar in excel and a formula would not work as it would be over written each time. I am thinking of if statements but that would be a pain in the rear.
I would then need to repeat this macro for the next 14 loans but in this case i would need to do it four more times and then i would need to do the same procedure for the interest side of the loan.
View 11 Replies
View Related
Sep 15, 2007
I am attempting to change what cells this macro places into the report from each sheet and am having no luck. It now captures the “ Date”, Worksheet name”, “Address”, “Pwo”, “Part”, “Time” and “Comments” . I would like to replace the time field (the one after the “Part” and change it to “Quality Technician” so that each entry it finds with comments it will also list who’s comments they are instead of the time.
I have not been able to fix the missing headers or changing what it captures.
Private Sub CommandButton1_Click()
Dim wks As Worksheet
..........
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
myDate = Application.InputBox(prompt:="enter date:", Type:=1)
If IsDate(myDate) Then
'keep going
Else
MsgBox "Please try again!"
Exit Sub
End If .........................
View 9 Replies
View Related
Jul 23, 2014
Attempting to match values in column E with column G and insert the corresponding number (column H) into column F See attached workbook for example.
Hoping to do this using VBA, not just a VLOOKUP.
The code below, places 0 values into the correct rows of column F, instead of the correctly associated value.
Attached file: Macro Match question.xlsx‎
View 4 Replies
View Related
Aug 1, 2007
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
View 10 Replies
View Related
Apr 20, 2012
I have the following formula in a Data Validation List:
=OFFSET(B8,MATCH(J2,
(CHOOSE(B7,D1Array,D2Array,D3array,D4Array,D5Array,D6Array)),0),1,COUNTIF(CHOOSE(B7,D1Array,D2Array, D3array,D4Array,D5Array,D6Array),J2),1)
In B8 it is the formula =VLOOKUP(B7,LookupTable,2,0). and the lookup array is this 1$H$112$J$113$L$114$N$115$P$116$R$11
So the value returned in B8 will be a the start reference for the OFFSET function. This works but it retuns blank values and not the values in the arrays.
View 3 Replies
View Related
Jan 30, 2008
The main worksheet in my workbook contains lists of suppliers with unique Vendor Numbers, of which there are approximately 4,500 rows. There is another worksheet which holds approximately 6,500 entries I need to bring data from that sheet to the main sheet using the Vendor Number and the information that is held on 4 columns unique to that number.
The information is a classification system that comprises of 4 columns holding a mixture of numbers and text.
Worksheet 1 has the Vendor Number in Column A and the Classification destination starting in Columns Q, R, S & T.
Worksheet 2 once again has the Vender Number in Column A, and the Classification information in Columns C, D, E & F.
What I need guidance on is how to build the formula to copy the information across, or direction into what other method I should be looking at instead.
View 4 Replies
View Related
Mar 31, 2008
I'm coming up with a dynamic graph using VBA. The one originally proposed to me using "offset" and insert chart doesn't work well with an an animation that happens concurrently with the dynamic chart.
So i've come up with a VBA version of it. It works extremely well on Excel 2007 but when i open it with Excel 2003, it just doesn't work in the line highlighted in yellow (below)
Sub create_graph()
Dim start_row, Start_col, range, number_of_tries, m, n
Dim graph As Chart
Dim wks As Worksheet
Dim myArray As Variant
View 3 Replies
View Related
