offset formula

I am trying to determine the number of active months for personnel on a rolling 12 month basis. This is the formula I'm currently using but am getting an incorrect result value. I'm new to offset so I know I must be missing something simple. =COUNTA(OFFSET(D2,0,COUNTA(D2:AA2),1,-12))

To make this more visible here's a link to access the file What I need is A formula in columns U1:AC13 which will match the Dates in Column A with Select Dates in Columns G-L and then bring the corresponding Select Value (if any) in columns N-S and place them in the same line as the "Date" the...

Hi I have a multiple sheet workbook - one of the worksheets has a series of event headers that run across 5 merged cells ("event1"= D2:H2, "event2"= I2:M2, "event3"= N2:R2 etc) I have a summary sheet that pulls statistics from the entire workbook and I would like to have a summary of the event...

Dear Friends, I was trying to find this all over the internet but the ones that I found does not work in my worksheet. The idea is to include a VBA coding in order to include the autocomplete feature in all the Data Validation Dropdown Lists that I have included in the worksheet. I found...

Hi, I am using an OFFSET formula to pull every second cell in a column into my new workbook however it displays the #VALUE ! error unless the other workbook stays open. I receive a file from my global head office which includes engagement survey scores (lets call it Global). This file...

Hey guys! Can anyone help me replace the "offset" with "index" in the following formula? =IF(...

Dear All, the problem is when i put the below formula on offset function, the formula doesn't work =OFFSET(RIGHT(TEXT(FORMULATEXT(AB19),""),(LEN(TEXT(FORMULATEXT(AB19),""))-2)),2,3) the following formula that generate cell reference from Excel formula and it work perfect without Offset...

I have a worksheet that users input employee names and date of birth for various calculations. A few users are deleting rows that are causing #REF errors with the calculations and I am looking for a solution around it. Indirect or Offset are options I am considering to solve the issue. Here...

Hello, I am using a nested formula to get data from an excel sheet. The Formula looks like below (just an example) =VLOOKUP($H3,OFFSET($A$1,MATCH(I$2,$A:$H,0),1,17),3,0) Now what happens sometime I have to move more step than defined in above formula. If You see the example table below you...

I've attached an example of the file I'm working on. I would like to keep the data in a separate file. But, offset formulas don't work if the data source is not open on your computer. I'm using the offset formula because I need to use both Row and Column references to find the data I need in...

Each month I create a report with over 100 summary tabs that are referencing 22 data tabs. All tabs are in the same file. The report works great! It is easily manipulated for all of the one-off requests I receive and the information is well presented and useful to my many customers. My...

I have defined list to select rows from A8 to last value in column A (but before A100) =OFFSET(sb!$A$8;0;0;COUNTA(sb!$A$8:$A$100);1) How to do exaclty the same, but select area like: A8:C100? When I did like: =OFFSET(sb!$A$8;0;0;COUNTA(sb!$A$8:$C$100);1) it's not working.

Is there any way that I can copy, on the double click of any cell in column A, the cell that I clicked on as well as the 3 cells to the right of that cell in sheet1, and then paste them in a column on sheet2 in the cells B2:B5. Alternatively, is there any way when i double click a cell to have...

I've got a report that produces a rather nice table with data categorized by the first column. However, to get it to work with another process as required by my employer, I need to get the data for each category into a single row. For example, if it was addresses categorized by state, the source...

I have a cell that looks up an account number and returns what row it is in. (B2) but, the data I am looking for is below and a few columns over, so I need to use an offset command to get the data. =OFFSET(data!F22,1,0) this works just fine. the problem is that I need to copy this command...

I have two worksheets in a workbook (WellbeingDashboard); Charts and Data. The data I am looking at includes product data (five products), accounts and user metrics (two metrics), by state (two states). I am trying to create a single chart (stacked area) that trends the change in a user-selected...

Hi, I've been trying to create a drop down list using the following formula where PROJECTLIST = LISTS!$G$2:$G$71 or G:G (I have tried both) If I just use the PROJECTLIST array for the drop down list it shows everything, if I use the formula below it only returns 47 entries - is there a limit to...

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 would like to be able to copy formula down to sum amounts in COL B based on how many text values in COL A. I have searched to no avail. Is this possible without VBA and without array (would prefer to avoid volatile functions if at all possible)? (Can't use these when uploaded to secondary...

<tbody> Row # Value 1 385 2 -732 3 244 4 457 5 436 </tbody> Is there a formula in excel that would identify the consecutive values that yield the highest sum ? In this simple example adding rows 3 to 5 will give you the highest total which is 1,137. I have a list of nearly 1,000...