offset formula

  1. S

    CountA with a Dynamic Range

    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))
  2. L

    match dates then move values to corresponding cells

    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...
  3. K

    Copying every 5th column cell into a list

    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...
  4. G

    Autocomplete dependent data validation drop down lists

    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...
  5. L

    Offset returns #VALUE! error when linking to external workbooks

    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...
  6. E

    Replacing "offset" with "index"

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

    Offset formula Error

    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...
  8. S

    Deleting rows of data causing #REF error

    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...
  9. A

    Multiple Offset in Vlookup

    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...
  10. A

    Is there an OFFSET style formula that works when the Data Source is not open?

    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...
  11. A

    Excel File is TOO Big...using Offset Formulas

    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...
  12. F

    Select range till last value

    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.
  13. S

    Copy target as well as three cells to the left of the target.

    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...
  14. HawaiianShirts

    Using If with Offset in a Formula

    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...
  15. D

    can you connatenate an offset command?

    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...
  16. T

    Creating a Dynamic Chart Combining If/Then Statement and Offset Formula with Named Ranges

    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...
  17. O

    Creating a dropdown list using OFFSET

    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...
  18. F

    Index + offset

    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).
  19. M

    Sum variable number of rows with formula

    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...
  20. B

    Identifying rows that sum up to the largest total

    <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...

Some videos you may like

This Week's Hot Topics