offset

  1. O

    Sum Monthly Data to Quarterly

    Trying to Sum monthly data from a column in a different sheet to a quarterly output in a row on my summary page. Anybody know of a formula that I could use to do this? Thanks!
  2. E

    Clear Contents two cells to the left if input date is <

    Attempting to modify a MACRO that deletes rows based on input box date < criteria - that works great! Now need to just clear contents of cell two cells to the left and not an entire row. Not sure what needs to be done on this line: ActiveCell.Offset(0, -2).ClearContents - not working. Dates...
  3. A

    COUNTIF, MATCH, and OFFSET in Array

    Hi all, I hoping for some advice here. I am trying to calculate the success rate of members of staff. I have each student's score in a number of subjects (A2:J6) and there teacher for each subject (L:U) I want to COUNT the total number of student scores over a certain amount (e.g. >5) for the...
  4. R

    Offset and goalseek

    hi, I am struggling to get an offset to work on a goal seek i am trying to do. Essentially i want to goal seek a value in row1, column1 to zero by changing the value of the cell in row 3, column 1. In the next round of calculations, ie when i apply the offset, I want to goalseek row1, column2 to...
  5. P

    Find last used cell within For Each Loop

    Hello! I am yet to figure out how the following works, but use it all of the time to find the last used cell in a column and offset 1 row: .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Cel.Value I am trying to apply the same logic to the following: Cel2.Offset(0, 5).Value =...
  6. D

    resize for copy

    how do i adjust the following code to change the copy and paste from Range("A" & i) to ("E" & i ). i get a object doesnt support the property when i change A to E. I tried to do offset(0, 4) and get a "You cannot past this here because the Copy area and paste area are not the same size...
  7. C

    Dynamic Data Validation

    Hi All is it possible to do a dynamic validation list using data from multiple sheets. I have a front page and 4 identical sheets, just with different sheets names. I need to be able to have the list of all names in B3:B75 from each sheet in 1 list, also needs to ignore any blanks. Working on...
  8. H

    Populate Formula based on Reference Value + Sumifs w/ Offset (?)

    Hello - I'm hoping for some help on two formulas/use cases I can't seem to figure out. Unfortunately, I couldn't quite figure out how to attach the date set but have included here with reference rows/columns for what I hope is an easier explanation: Q1: I would like to populate cells based on...
  9. M

    Selecting a block of cells using offset

    Hi I know how to select a block of cells using VBA. But for this task, I have to be able to do it using the offset function, because data is inserted using the offset function. So my question is this: in the code below, assuming that C3 (or C4, or C5 (depending on where the offset function...
  10. N

    Help with VBA!

    Good day to all, As I’m fairly new in VBA.. Appreciate if you could help me on this! So I have this excel workbook with a set of raw data (workbook1) which i wanna automate to import/copy&paste the data to another workbook (to open workbook2 with dialogbox). Eg: Cell (A1:J1) (Workbook1) copy...
  11. D

    Concatenate with Offset

    Hello all, I am trying to use concatenation formula with VBA and need to use offset in order to complete it, but doesn't seem to work. The point is:I have 2 lists of names in 2 columns, which i need to add some stuff. What i need to do is select a column A item, and macro will insert 29...
  12. M

    VBA Code to change the font in a block of cells to white

    Hi I'd like to change the colour of the font in column A in specific cells to white, depending on the cells which are populated in column C. I wrote a simple macro which copies data from Sheet 2 to Sheet 1 - please find below the scenario: Sheet 2 has the values 4, 5 and 6 in cells A1, A2...
  13. B

    use next available cell in row (xltoright)

    i need help getting this code to paste in next empty cell to right. the code only paste in column B. i need-if column B is not blank then use next cell to right. Dim ThisCell1 As Range Dim ThisCell2 As Range Application.ScreenUpdating = False For Each ThisCell1 In...
  14. D

    VBA for setting cell value equal to the found value in a range offset by two columns

    Hi, I was trying to come up with a code for setting cell value equal to the found value in a range offset by two columns, but I can't get it to work. Noting the code I tried out, please let me know how it could be done. Thank you, David P. Private Sub Worksheet_Change(ByVal Target As Range)...
  15. M

    Find matching code & Find first negative value & Define date of the negation & Write it down

    Hello all, I ran into a problem with my excel file. I am trying to calculate when I will need to add some additional quantity of material, but the table I am taking data from is not exactly helping. Fig. 1 shows how the data are approximately displayed. You can see production for each weak...
  16. V

    Easier Way to Write Nested IFs and Iterating Offsets

    What is a less rigorous way or writing this? Sub COSTPLANBREAKDOWN() Dim rngstart As Range Dim tst As Range, lastrow As Long Dim cl As Object Dim L As Long, strcells As String, lsr As String, toplvl As String, comm As String, flat As String Dim CLT As String, PLT As String...
  17. MannStewart

    Need macro for OFFSET whcih derives ROWOFFSET from a variable in another cell

    Hi I have a worksheet with a working range of B5:B5000, G2:G5000, H2, V5:V5000. where, V5:V5000 is just =ROW($B5)...downwards, $G$2 contains a function formula that returns a daily overall air-pollution level in decimal value that I make it recalculate each time the worksheet is...
  18. T

    Sumproduct and offset issues

    Hi, I managed to have certain cells of my sheet1 reference others from sheet2 If a condition was met by using sumproduct. I managed to get this reference by using offset as I wanted to automate it by having it index numbers every 4 columns. it went smoothly while I was using no column offset...
  19. G

    Dynamic Range that expands with new columns

    I am trying to create a dynamic range that expands with new columns added. I created a defined name called ("DynamicCompList") and the range for this is D7:R57. When I enter information into S7, I want the range to expand to D7:S57. My data in the range has some blanks that need to stay. I tried...
  20. B

    cut/paste scripting - copy a range then paste to a destination range that matches to row/column address criteria - INDEX/MATCH but cut n paste

    I'm struggling with some simple copy/paste scripting in Excel/Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets. I have a "Today" data entry sheet. I have a "Program" sheet that stores data in a column under a Date header. I want a macro/script that will: cut data from an...

Some videos you may like

This Week's Hot Topics

Top