1. E

    Match & offset or index/match?

    Hi, Trying to match a value and return the a value offset to the left. The values to search in are in the columns B to E (as per the table) I want to return the Level in column C I know two values (these come from another tab) 1. The row heading (Red names) 'Risk template'!F5 2. What I'm...
  2. D

    Summing values across multiple columns, searching for first column in range

    Can anyone help me with the following problem? I have a data structure like below, except that it's a lot wider and goes from the previous Monday to 365 days out across the width of it. There are about 1200 rows of data. 11/5/2020 11/5/2020 12/5/2020 13/5/2020 14/5/2020 15/5 100%...
  3. E

    Move rows of data depending on whether or not they match rows in other columns

    Hi everyone, I'm working with template spreadsheets that get populated via copy/paste and compare volumes of assets in hypothetical what-if scenarios. In the daily workflow, the user manually inserts this information from another tool into Excel and does further analytics. However, the what-if...
  4. G

    Data Validation to not show blanks at the end

    I have below formula, when I click ok after entering the formula into that data validation source, it shows the formula as the options and not the values that should be there. Not sure if anyone else has had an issue like this. Any help would be greatly appreciated. =OFFSET(Data...
  5. A

    Copy and paste data based on text in cell next to it [VBA]

    I have some code that I want to check every cell in in the range “A3:AAA3” for a specific text. If the cell contains that text, I want it to copy the text in the cell on the right, to two rows above (see below for illustration): The copied text will be a date. This is what I have so far...
  6. A

    Repeating macro for each cell in A (with offset)

    Hi everyone! Right now I'm having troubles with how to repeat my macro for each cell in A. I have some offsets so it becomes very confusing. Here is my code. I recorded the macro while ticking 'Use Relative References' on. Hopefully someone can help me. Thank you very much in advance :) Sub...
  7. D

    Loop with Range that requires offset

    Hi all, Hope someone can help, im trying to make a loop that relies on a range, which should be changing every cycle. Range("Q244").Select Do Until ActiveCell.Offset(0, -1) = "" Set RangeLoop = Range("C8:C25") With ActiveCell RangeLoop.Copy...
  8. S

    Finding next TRULY empty row - VBA

    Hello, I've recently been attempting to get the next completely empty row in order to add data to it. Normally this would be pretty straight-forward, however I'm running into an issue where if the last row contains values that AREN'T in the cell on column A, then it will overwrite the last line...
  9. D

    how to use range and calculate offset

    I have the following code : Sub checkit() Dim c As Range Dim z1 As Integer Dim LastRow As Integer With Worksheets(1).Range("A1:O100") z1 = 0 LastRow = UsedRange.Rows.Count For Each c In Range(.Cells(2, 9), .Cells(LastRow, 11)) If c.Interior.Color = RGB(255, 255, 0)...
  10. S

    Formula that pulls from a specific row for each month & "locks" into that row for the month?

    Apologies if this looks/sounds messy... In the middle of my (probably unnecessarily complex/large) formula, I have this function: (OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-ROW()+2,0)) As it stands, this will pull the value of the cell 2 rows from the top, in the same column as the current...
  11. A

    Using offset to copy formula down a column

    Hi I am trying to apply a formula where I need to refer to a few cells to the left of the formula cell and a few cells to the up and apply the formula down the column for quite a few rows. I am trying to achieve the result by using offset function, but I am not getting the desired result...
  12. O

    Matching products with the same ID code

    Hi. I'm struggling with finding how to solve a particular Excel Problem. I have a table that comprises of labelled columns with lists below. The columns are as follows with an example of a single transaction: Unique Transaction Identifier Business Area Category Sub Category Product Code...
  13. 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!
  14. 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...
  15. 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...
  16. 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...
  17. 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 =...
  18. 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...
  19. 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...
  20. 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...

Some videos you may like

This Week's Hot Topics