offset function

Hi guys I am looking to create a macro which essentially copy data from one workbook to another. The data is organised by ID numbers for which there are specific data points. The ID’s in both workbooks are organised vertically in one column with the data points for each ID in the same row as...

Currently manually updating about 30 charts monthly. Adding new data monthly. Would like to make charts dynamic I would like to use the offset function and named ranges. I can get as far as one dimension and one series but I am stuck as to how to use both series (revenue and volume). But I...

Hello, I'm working to create dynamic charting in my dashboard using the OFFSET Function as follows: =OFFSET('7'!$G$1,COUNTA('7'!$G$2:$G$25)-1,0,-MIN(12,COUNTA('7'!$G$2:$G$25)-1),1) As you can see, I want the last twelve months of data in therange. For the associated dates (to get the last...

My output should have both the fruits which have value as true. For eg: For third row, Apple, Orange and Watermelon has the value as "True", but it is returning only first value. Formula i have used : =IFERROR(OFFSET([@Apple],[@[Offset Tower]],MATCH(TRUE,Table2[@[Apple]:[pineapple]],0)-1),"")...

Hi I'm putting a summary sheet together for a rollout of work across several regions. The first table was fine, just showing overdue sites based on the 'required by' date...

Hello gentleman Im having a small issue with finding the correct way to make a formula. i request your assistance. i would like to find a value in a table based on something like this: =INDEX(X:X,MATCH(S,$B$:$B$,0),MATCH(R$,$C$:$N$,0)) the problem is that i need it to go up or down the table...

Hi, I had formula: = INDEX($E$24:$AK$24,1,$E$8) How can I here add offset, that function will return value number from row 25 ? (now its value from row = 24, but I need value from row = 25).

I can't seem to get a good formula to return a drop down list using Data Validation for every other cell in a row. I need a dropdown list that produces the values from D9:AR9, the list would only include D9,F9,H9,L9...AN9,AP9 and AR9. I can produce a formula that I can drag across columns to...

Need help... I have two worksheets, sheet1 contains a column of consecutive entries in A1:A20 and sheet2 has 20 groups of data in 13x13 grids with a grid label in the upper left portion of each grid. Grid 1 fills A1:M13, grid 2 fills A14:M26, etc. The grid label is in A1,A14,A27, etc. I am...

Hi, I'm new to the Excel board, but I'm hoping you guys can help me out. I need to find a way to calculate employees' sales for the last 3 months on a rolling basis. I think I want a function that looks for the column that matches "current month," offsets by -2 columns, and then sums from...

I'm pulling my hair out on what I thought would be simple. I have a spreadsheeet with 31 tabs for each day of the month starting with tab ONE and ending with THIRTYONE. I want to find the MAX number in all of the Cells D20 across all 31 tabs (no problem using the formula...

Hi, I have a question related to using Data Validation to make Dependant Lists, ie. lists that vary depending on selections made from another list. I have this working when I use definite range name references ie. List1 =Sheet1!$A$2:$A$5 List2 =Sheet1!$B$2:$B$4 and so on. However if I wish to...

Hi All, Looking for a little advice here. I am having an issue when I try a chart a named range. The named range "Refers To" is =OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1)))) As far as I...

Hello, Thanks for such a great forum. I searched before asking, but could not find an answer to this. In this formula: ="There were "&OFFSET($B$4,COUNT(B:B)-1,0)&" visits to the website for the week of "&OFFSET($A$4,COUNT(A:A)-1,0)&" ." Column A is the date and Column B is the number of site...

I have two spreadsheets in the same workbook. Both spreadsheets a have a similar layout for the monthly columns portion. 12 columns with the "month" name for column headers and below each monthly header a numerical #. For the first 12 months 1 -12 entered under each month respectively...

Hello All, I have a requirement where i have to search a value of cell in the C:C range and find all values found in that column, if the values are found then i have to copy the value of first string in A:A range. Example: A B C 1 Cat Cat 2 tiger Cat 3 son Cat here in column...

Hi, I need to write a macro for a goal seek, where the changingcell cell will be dependant on which scenario I am using at the time. The relevant cells which might be the changingcell cell are in a row, so I figure I should be able to make the changingcell value = an offset from the cell...

Hi Everyone and thanks for any help in advance. I have an excel spreadsheet in which I am linking to another spreadsheet. From this worksheet I have to return the SUM of an OFFSET. Because of the limitations of the OFFSET function it is ineffective if you change the linked workbook and the...

I have a list of names on a worksheet called "customer information" The customer names are entered in column A and are listed by entry not alphabetically. I want to be able to take a name from that list and put it onto a worksheet called "invoice details". . I would like to open a drop down box...

Hi - I am attempting to use a reference table in one worksheet to populate column headings in another worksheet in Row 1. <TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=203 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH...