offset

  1. T

    VBA inserting formula's in cells not working

    For Excel VBA, I am trying to make a code which looks at the value in the column "case type" and let it paste a formula or clear a cell, based on said value in the cell of the column "case type". For Case type 1, Column 1 should contain be cleared of any values and Column 2 should contain...
  2. M

    Calculating 1 / 3 / 5 year Returns on a table that varies in length.

    Hi, I have a table of monthly performance data where each new month the previous month's performance is annotated. I'm looking to use a formula that calculates the 1, 3, 5 year returns automatically and allows for the fact that the table or performance data increases each month. This formula...
  3. D

    Excel Offset function

    Hi, if I want to use Offset but want to do this based on a value , so I want the cell reference containing X as my starting point, I have tried ; CELL("address",INDEX($A$1:$A$13,MATCH(D1,$A$1:$A$13,0),1) )[ = $A$4 Which returns the correct cell reference but when I nest in Offset I get...
  4. ibmy

    offset the formula,1 cell below to start calculation

    Hi Experts, I been thinking and try change current formula but not succesful to meet my need. Current Formula : Start from currrent cell GIJ1datatotalCount230.63240.150.860.3J3J3=IF(I3="","",COUNTIF(INDEX(G:G,ROW()+I3-1):G3,">0.5")) The best solution I have tried so far : but the result...
  5. L

    Sparkline Dynamic Range

    So I have I have a list with categories and base o the category that I select a Dynamic Range with subcategories is created with Filter,Unique and Order functions. In the following columns I have the sales from Jan to Dec which is connected to s spark lines. The thing is that not all categories...
  6. J

    Sum values in column O until empty cell is reached whilst excluding "NO" values

    Hi all, Hoping someone can help me with this query. I have a workbook I am working on that currently sums all the values in column "O" if the value on the same row in column "L" is "WP" or "BS" until an empty cell is reached. I would like to expand upon this formula so that it still sums like I...
  7. ibmy

    If Both C=1 And D>0 ,then ... ?

    Greetings experienced, With my limited knowledge of Excel, only know basics, I was thinking to use function "=IF(AND(N4=1,O3>0), , ) " & "(INDEX(O:O,ROW()-O3-1" ,in formula but do not know how to structure/complete it. When cell in Column N =1 and above row of it, cell in Column O >0 , assign...
  8. J

    XIRR with Non-Contiguous Entries (Single Column of Cash Flows, Multiple Entities)

    I have a stream of cash flows with with two different Categories by which I would like to generate IRRs. I know you can create TRUE/FALSE ranges to be referenced by formulas and I think this is probably the way I want to do this? Basically I want to calculate separate IRRs for apple/banana/pear...
  9. G

    Indirect reference with changing cell value?

    Good Evening, I'm trying to drag/copy-paste an indirect formula where the row value in the reference changes. Then continue the pattern over hundreds of cells. This is the general idea: =INDIRECT("Sheet1!A3") =INDIRECT("Sheet1!C3") =INDIRECT("Sheet1!E3") =INDIRECT("Sheet1!H3")...
  10. WildBurrow

    Worksheet_Change(ByVal Target As Range) to include offset and allow user to drag cell value to subsequent rows, and countif formula

    Two parts to this query: 1) I've written the following code to address each row within range (F15:F44), aka table "WellList[ERP Group ID Number]". It works, but it's rather ridiculous and repetitive. I'm looking for something more concise/elegant which will still allow the user to drag the...
  11. W

    offset, match index?

    I have a output from an accounting product and I need to reformat the table. At the bottom starting at row 23 you can see an example of the new worksheet I am trying to create. Can I get help with a formula to help automate this? The original report has 742 more accounts like this I need to...
  12. R

    Find matching string and get string of data in offset cell from raw dataset

    Attached is a screen cap of the imported dataset. Please note how the data is grouped with multiple labels and values in a single cell. I need to find the match JSN code which is located in a single cell along with miscellaneous data. The "JSN" text has (4) spaces after followed by the (5)...
  13. WildBurrow

    Pasting select columns to another sheet using LastRow and Offset

    I've found code that will copy select data from (Sheet1) to another (Sheet2) ; using only values found in columns "B:E" and "I:K". Columns "I:K" will always have data on each row pasted on to Sheet 2. Columns "B:E" may not have data on each row. Code from Sheet1...
  14. S

    Dynamic Named Range based on values in multiple columns

    Is there way to create a dynamic named range based on cell values from other columns? The cells in the named range may not be contiguous. For example, I have a list of holidays or other observed days. I want a dynamic named range of the Day column to reference only those marked as Type...
  15. D

    Offset + Indirect / Index + Match

    Dear Mr.Excel, I am struggling with OFFSET function when source row is deleted. Here below is basic example: 1) Sheet2.Range("A2:R50") is my table with data 2) Sheet1.Range("A5:R20") is my table with offset data + scrollbar. If I remove any row in table 1), I automatically get #REF in Offset...
  16. D

    How to count the number of cells that are greater than the cell above each?

    These cells are conditionally formatted to be red if the cell directly above is less than it and green if it's greater than. Each cell contains a simple function of SUM the row to the left plus the cell directly above it. I'm pretty new to Excel so I've been testing functions out, and now I'm...
  17. rudogg

    Macro to copy selected column data and always paste into same Column

    Hello: I am looking to create a macro that will be a ribbon button, that will copy values that have already been selected by the user, in either columns C,D or E and always paste them in column B. See table below for an example. The user selects cells 1, 2 and 3 of data in col C and runs the...
  18. T

    Translating a horizontal data set to a pivot table friendly vertical format

    So I'm working with a horizontal table that tracks a count of calls per week per employee, currently formatted as Name 1/3-1/7 1/10-1/14 1/17-1/21 1/24-1/28 1/31-2/4 2/7-2/11 EMPLOYEE A 249 292 107 212 226 218 EMPLOYEE B 425 447 214 203 419 352 EMPLOYEE C 429 459 397 382 362 372...
  19. 2

    OFFSET formula to act upon value in the cell, not upon cell's address

    If cell L1 contains value A20, how do I get OFFSET formula to act upon the value A20 and not upon L1 address?
  20. G

    VBA Loop through column range and Offset the output

    Hi All, I have the following data in column A of a worksheet starting from cell A1: Column A Column B ====== ============ xxx ABC xxx 123 4567 876 I am looking for VBA code that will colour the cell in column B in yellow if the cell to the left in column A contains "xxx" or...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top