absolute reference

  1. L

    VBA Absolute reference not working...

    Today has been one of those days! Can someone help?!? I am referencing a pivottable on another sheet... This formula: ActiveCell.FormulaR1C1 = "=iferror(index('" & PSheet.Name & "'!r4c3:r" & LastRow & "c" & LastCol & ",match(rc[-1],'" & PSheet.Name & "'!r4c2:r" & LastRow & "c2,0)" &...
  2. I

    absolute structured references in excel table formulas?

    HY, I am stucked with the absolute structured references in excel table formulas, currently i am using this formula for calculation as......... =ROWS($Z$8:Z20) .......while in case of table calculation this looks like as.........=ROWS([@[SR NO.]]) ...just unable to to lock thse cell. and i need...
  3. C

    Formula to return FALSE/TRUE based on dynamic absolute referencing in dynamic table

    Good day experts, I have great need for a formula that outputs either TRUE/FALSE based on some criteria, the sample table is as follows: <tbody> (A1)EMP ID HRS WEEKDAY OUTPUT 0001 9 1 FALSE 0001 9 2 TRUE 0001 9 3 TRUE 0001 9 4 TRUE(D5) 0001 6 5 FALSE 0002 6 1 FALSE 0002 7 3 NOT...
  4. S

    Sorting with Conditional Formatting - reverts to Absolute

    I'm using Excel to keep track of various projects (one row per project). Some of the projects have 3 or 4 things to do, so I chose open cells a few columns over and used conditional formatting to have them turn green when I type "X" in them. The first column for that row has the same...
  5. S

    Absolute Reference in INDEX MATCH doesn't work!?

    Hi, PLEASE HELP! In my spreadsheet I have the ID of the product I am searching for in column "A" and the Dates across row 7. there are thousands of dates and hundreds of products. I typed this formula to return a specific attribute (in array column #13) and it works in the first cell. but...
  6. 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...
  7. J

    Replace text within formula - with "dynamic" absolute reference to cell 1 row above

    Okay.. this is kind of a tongue twister but... I need to replace the text "$D$26" within column D with an absolute reference to the cell address of *the cell 1 row above* the cell containing this formula. The formula will appear every 10 rows. I have the code below, that seems to work but... I...
  8. PritishS

    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...
  9. 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...
  10. T

    Absolute References

    I'm having a difficult time working with absolutes when referencing other sheets. I want Workbook1 to reference a sheet in Workbook2, but it needs to be an absolute reference because there are filters that throw everything around and Workbook1 isn't tracking those changes. So if I can absolute...
  11. T

    Relative reference not working on INDEX/MATCH with Multiple Criteria

    Hello, This is my first time posting so even though I read the rules, please forgive me if I accidentally break one or two. I am currently using excel 2013 and am trying to complete a lookup using Index/Match on two criteria. I am able to complete one row, but the formula does not...
  12. A

    OFFSET Function not extending to new rows

    Hello, I am trying to add more rows to my chart, but when I extend the rows with the formulas I am getting " ##### " instead of the numbers I'm expecting. Here's my formula that starts the chart: =OFFSET('Raw Data'!$A$6,Performance!$B6,MATCH(Performance!E$5,'Raw Data'!$6:$6,0)-1) Here's the...
  13. Smokeyham

    Create a List of Absolute Reference in multiple worksheet

    I have a spreadsheet and would like to create a list of the value in an absolute reference from multiple worksheets. Here is what the formulas for the final result might look like: ='Management Analyst'!B2 ='Human Resources'!B2 The spreadsheet is quite large (lots of worksheets), so the...
  14. F

    Help: Custom Error Bars and Absolute Cell Reference!

    Hi Everyone, I have a line graph with 8 series of data, all of which have custom error bars which I have calculated. I have to create one of these graphs each time I collect data. What I have been doing is copying the graph I made previously and dragging over the series to the new set of data...
  15. B

    Replace variable Absolute Column Reference in formulas by a static numeric factor

    Hi, I'm using Excel 2010. I have Sheet1 that contains eleven columns. Let's call this group of eleven columns LocationData. On a Sheet2, I have one column for each LocationData group that summarizes the data using various formulas, referencing various columns in the group. All my formulas are...
  16. R

    Cell references/linking between workbooks

    Hi All Here's a doozy for you. I have 2 workbooks, one an inventory overview of kit, the other a detailed version. Obviously some info is duplicated. I've made a straight forward link (see below), but it needs improvement, What I want to achieve: In the detailed inventory, I type in a row...
  17. K

    Absolute Referencing

    Hello, I am trying to develop a template for my company and have just started using VBA. However, after some trial and error I have realized that my macros all use relative referencing. Since my template is constantly changing I would like to use absolute referencing but I do not know how to...
  18. C

    Function Procedure Path Issue

    This is a frequent problem for me. When I save a macro-enabled workbook that contains function procedures into a new file name, when the workbook attempts to use the function procedures, it is looking for the function procedures in the old workbook name, not the new one. Borrowing some...
  19. S

    Change formula macro

    This one is tricky, Each time I copy selected ROW. I have to change this formula in various columns of row no. 259 =BDP($D$259,"interval_aVG","calc interval=30D","market data override=px_volume",G$204) to =BDP($D$270,"interval_aVG","calc interval=30D","market data override=px_volume",G$204)...
  20. E

    Defined Table Absolute In Formula

    Hello - I am working with some dynamic tables. I have named the ranges and graphed them. I am naming the axis with the table name and it is working beautifully: for example: Axis label Range= ='SheetName'!TableName However - I need to make several versions of these and the tables need to...

Watch MrExcel Video

This Week's Hot Topics

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