references

  1. M

    Keep all references unchanged after sorting

    Take a look at the spreadsheet. The references will change after sorting and break the calculations. Absolute referencing is not an option. The table has to be copied periodically to another sheet and placed it at a different row (the columns are identical). Absolute referencing will break the...
  2. R

    VLOOKUP to the Right

    I have this formula: =IFNA(VLOOKUP(1,$C26:$F33,4,),"") I would like to swap 1 for a 2 and the 4 for a 1. Changing the formula to: =IFNA(VLOOKUP(2,$F26:$G33,1,),"") I am having to use "column 1" as a slave that references another column Is this possible?
  3. S

    Limit to Non Contiguous range selection workaround

    Below I have posted my code that updates a fill down list, but The highlighted section in red gives me an error if I make the range selection bigger. The range has non contiguous cells that need the fill down list updated all the way down to cell 467. Is there a work around for this? Maybe...
  4. L

    Copying cross workbook nested IF formulas

    Hi. Got what is hopefully an easy problem - Have created a nested IF formula that references multiple worksheets within the workbook and from external workbooks. All this is working fine. The problem arises when I try to copy the formula - the references within the workbook update to...
  5. F

    Absolute Table References

    Hello everyone! I am working with a table, which is new to me, and I'm trying to create absolute references to identify the first instance of a duplicate value. I found the formula to identify the first duplicate value from Exceljet found here...
  6. Joneye

    VLOOKUP VS INDEX & MATCH | DATA Matching

    Hello all, Using a mac so cannot post my data sadly. (Excel for Mac latest version). Brief * One workbook, data extract needs to be checked against another list. One source of data is from a CRM another from excel. All data has been cleaned and sat in two tabs. Objective * Check if items in...
  7. B

    Open Most Recent Document in a Folder AND Have Dynamic Vlookups

    I am working to create a document that using VBA code can open the most recent document in a folder each month when the report is run and then do vlookups from that document. I have a code that opens the most recent document in a file folder location but when I record the code for the vlookups...
  8. BenElliott

    Indirect summation

    Hello, all. I can't find this question referenced in the forum before. I have a sheet where Column 'A' always remains the same each year I use it. But I always add columns between column 'A' and 'C' each year, like this: Year 1 <tbody> 2017 2016 Turnover </tbody> Year 2 <tbody>...
  9. V

    INDIRECT RC Style cell addressing

    I have taken over an Excel workbook with some complex macros. I am having difficulty working out which cell(s) a particular formula references. The extract from the macro is: Range("F2").Select ActiveCell.FormulaR1C1 = _...
  10. James_Latimer

    Sheet names in formulae

    Win 10 / Excel 2016 Hi all, I was wondering if someone could give me some pointers please... Long story short, i have a 'tool' that i created that will search a workbook for external links and list them in a text file. I've recently found an external link that my 'tool' wasn't taking into...
  11. E

    create dynamic array based on cell references, number of times to repeat and length

    Hi, I'm trying to create an array (which will be ultimately used in a formula e.g. NPV) of cell references which repeat themselves (which will be dependent on another cell reference). The range of cell references to be repeated will also be dependent on another cell reference. Sorry if I didn't...
  12. V

    Macro that uses Cells in Column A being automatically reused for columns B,C,D...

    Hi All, I have a macro that uses cell references from Column B, it uses a lot of cell references in Column B. Is it possible for me to set it up a macro or adjust it, so I could re-use that code for columns B, C, D ect keep the row references. Below is a hugely simplified version of what I...
  13. P

    Supress Save 'XYZ' with references to unsaved documents message

    Hi all, I have an annoying problem that must not be present for a file I am making for end users with little Excel expertise. I have a file that copies a worksheet from it to another workbook. If the user saves the originator file (the file from which the worksheet was copied from), the...
  14. P

    Removing duplicate and original entries from a string within a single cell

    Hi all, I'm hoping someone can help me! I'm working with some data where each cell contains multiple entries (which are unique references). Essentially I want to compare A2 and B2 to show how many entries should still be live. I can get a numerical view of this in C2 by using...
  15. H

    spreadsheet won't run macro's

    I've tried all the usual solutions. Checking if automation is on. cells are not set to text. Check for Circular References not an issue The strange thing is if I restart the computer it will sometimes work. Corrupt excel or something else?
  16. H

    Changing Cell References based on value in drop down box.

    Good Afternoon, I have come unstuck with an Excel sheet we have after trying to automate part of the document. This spreadsheet tracks costs over a year based on what week it is. Each member of staff has a spreadsheet seperate to this one where they fill in data, and that data is referenced in...
  17. D

    Fixing cell references

    I have a very large worksheet (68 meg) that I have split into 4 smaller logical worksheets. How to get the cell references in the carious sheets to update to the new sheet names? Thanks
  18. C

    Autofill Down in VBA

    I really don't understand why Excel is so complicated when you autofill down in picking the correct range. Even if I record a macro and use Relative cell references, it still hard codes a range into the command. I have a formula in cell P2 that I want autofill to the bottom of the table. I...
  19. G

    Parse sheet names from within existing formulas

    I am working with an existing financial model workbook that has a whole mess of formulas and cross references. I want to try to get my arms around the dependencies and links between worksheets in the model. I am capable of writing some parsing formulas in excel, but they wouldn't be able to...
  20. S

    How to ensure formulas references move when cell is moved without redoing formula

    I have multiple formulas that reference a certain cell but now I have to move that cell to a new location. I may have to move it again in the future. I want to have all the references to that cell reference the new location when I move the cell. I know that one way I can do it is to name the...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top