relative reference

  1. A

    from sheet1 Copy w relative reference and paste as a value to sheet2 but next empty row

    Hi, My code copy and paste well, but it's pasting Formula. (i need to paste as value) i'm missing the spot where to insert the snipped to paste as a value to sheet2 Please can someone help me? This is my code ActiveCell.Range("C1:AE2").Copy...
  2. G

    Use relative reference in a formula to replace cell contents?

    I want to replace values (1-9) in the adjacent column on the left with another value. This formula is not working: =IF(AND(E1>=1,E1<=10),RC[-1]"*","") I think it's the RC portion of the formula that is expressed incorrectly but I am not sure.
  3. D

    Unable to create relative reference to another workbook

    I am trying to create a simple relative cell reference to a cell in another workbook which copies the content from that cell to my current worksheet. When I do, it creates an absolute cell reference, which works for that particular cell. But if I copy the formula down to the next cell, it is...
  4. J

    Adjusting Named Ranges Dynamically

    Good morning, everybody! I have to update a *ton* of tables in an Excel spreadsheet, rolling them forward for the new year. For example, if WRSState is in cells A6:H15, I need to copy rows A7:H15 into A6:H14, clear A15:H15, and then make a couple of other edits that i should be able to figure...
  5. T

    relative reference in an array formula that refers to a table

    I'm sure someone else must have run into this issue before me, but so far I can't find the answer. So here's my problem. I've got an array formula that includes a reference to a table: =SUM(IF(MyTable[[#All],[Column72]:[Column72]]=Sheet2!$A2,MyTable[[#All],[Column10]])) If I put this in...
  6. M

    Default Macro using Advanced Filter with Relative Reference

    Hello, I am looking for a VBA sentence which make possible to copy a full data base which can change (Relative Reference) using the advance filter: Sub MacroTest() ' ' MacroTest Macro ' ' Workbooks("Other Excel file containing the Data Base.xlsx").Sheets("Data").Range("A3:EN3897"). _...
  7. J

    Convert Array Formula to R1C1 style

    Hello, does anyone know how to insert an array formula using R1C1 style? I am attempting to convert the array formula (with absolute/relative references as shown below). Sub Insert_Rows() Dim Sh As Worksheet Dim End_Row As Long Dim N As Long Dim Ins As Long For...
  8. S

    VBA copy formulas without the clipboard and get updated relative references?

    OK, I've got it that I can directly copy a row of formulas without using Select/Copy/Paste - With range1 and range2 each set to a row: range2.formula = range1.formula This is clearly more efficient and cleaner that using the clipboard. Problem is, the formulas are copied verbatim, with...
  9. P

    Absolute Reference Formula giving wrong value while copying from another worksheet

    Dear Sir/Madam, I'm new to VBA. Trying to learn from internet and previously got best response here at Mr. Excel. I have got a wired kind of problem. a. I have a worksheet named 'Template'. there I have a table template like below shown. b. Here 'TOTAL QTY' column have a formula...
  10. B

    Cell references when inserting and deleting rows

    Hi all! I'm relatively new to more advanced Excel functions, so this question may be relatively easy to answer. I have a spreadsheet with a list of employees that is copy and pasted for every month of the year. As of now, the spreadsheet only goes through June. There are four columns, employee...
  11. C

    Copying Relative References to Multiple Sheets

    I have an Excel workbook with 21 worksheets. 20 of the sheets are an identical grade/scoresheet, with the sheets named numerically 1-20. The other sheet is a summary tab for the information in the other 20 sheets, and it is named "Summary". On sheets 1-20, cell B7 will contain the name of the...
  12. M

    Excel VBA Relative Table Help

    Hi, I'm trying to create a macro that will work dynamically with the number of rows of data. To try and combat this I inserted a table to house the data, so that all of the references after would be more dynamic, but I've found myself stuck with the creation of the table, as it wants to use...
  13. C

    How to loop through all rows of a range?

    Hi, I have coded a macro that effectively copies a dynamic named range to a sheet called Sales chart. The working named range, called ItemsSold, is a single column consisting of uniquely filtered data. My problem is the second named range; I want it to look up the source data, called Sales...
  14. The_Rock

    Copy formula depending on a different column

    Hi Hoping you guys can help with the below. So what the code is doing is it is searching for 'Global Name' in Col M and then moves down a row to where the formula's are stored. I then want to copy the formula's down for the number of rows in Col L. I can't find the right code to do as it is...
  15. M

    Macro to copy and paste stuff...and more

    Hi all, I need help coding some stuffs in Excel 2013 I have a table set up for monthly salary info input. My boss wants it to be copied and moved down the sheet for every new month. At the same time, I need to delete the non-fixed content, so that the table can be available for a new money...
  16. R

    Subtotal like autosum with macro button; Selection as relative range in VBA

    Folks, Subtotal is superior to Sum, yadda yadda yadda. But Sum has a convenient button. When you click it, it knows to highlight the contiguous range above or to the left. I want to create a macro button for Subtotal that does the same thing, without the subtotal groupings. How can I make the...
  17. F

    sheet references VBA help

    Hi all, I'm a complete, total and utter novice where VBA & Macros are concerned. I can record I macro, and I can go look at the supporting data - tweak the odd bit here and there but that is where my knowledge falls flat on it's proverbial face. My issue: I've recorded a macro that I want to...
  18. U

    Relative Reference - Formula to Goal Seek Using Cell on Different Weooksheet

    I am using Excel 2010. I want to use a relative reference to goal seek using a "ChangingCell" that is in a different worksheet... ActiveCell.GoalSeek Goal:=0, ChangingCell:=Sheets("Volumes").Range("O11") ... but I am getting an error message that the reference is not valid. What...
  19. J

    VLOOKUP when Table Array Changes

    Hi, I'm new here, and I have a question regarding a problem I ran into today, at work. I am basically using the VLOOKUP function in a sheet (sheet1), to pull out data from another sheet(sheet2). But on a regular basis, the Table Array from sheet2 is changed, because the data from sheet2 is...
  20. J

    relative sheet reference? is it possible?

    I have multiple tabs names Rep1, Rep2, Rep3, etc. On a summary sheet I have a cell set as =Rep1!D11 If I click on that and drag it down to fill series it goes to Rep1!D12, Rep1!D13 etc. Is there a way to make it remain D12 and change to Rep2, Rep3, Rep4 etc? I would like to change the...

Some videos you may like

This Week's Hot Topics

Top