formula auditing

I have a massive file with dozens of tabs and several hundred charts. Frequently, I will check to see what uses a cell before I delete it, but I keep running into this: I will click the cell, select trace precedents, and it says it goes to a chart (plot area). But how do I figure out which...

I am using Excel 2016. I have the following formula in cell A3 =A1+A2. If I select A3 and click Trace Precendents then it highlights A1 and A2. If I select either A1 or A2 and click Trace Dependents I get a message saying "The Trace Dependents command found no formulas that refer to the active...

Hi! Writing Excel 2010 formulas is trial and error experimentation. The Function Argument dialog box is helpful and so is the tool Formula Auditing Evaluate Formula option in writing Excel formulas. However, none of those choices, including the F9 key, fully reveal the formula argument...

Hi, i'm in the midst of developing KPI dashboard. My table is as follow: <tbody> KPI Objectives weightage (%) Target Target Actual Achievement KPI Score (%) Actual Score (weightage) Base Stretch 1. New business 90 8 5 6 78% 8% 2. Get agreement signed 10 12 11 12 50% 8% </tbody> My...

Hi all, Situation: I have a column of data 'reference column' which changes length on a daily basis. In other columns 'formula columns' I have formulas which are dependent on the 'reference column' data. Current Solution: My data length will not be longer than 1000 rows. For all my 'formula...

I have a number of sheets, which are uniform in there formats and calculation metholodgy. The only thing that differs is the reference for data. For instance: Sheet 1 has formulas like =VLOOKUP(a1,'[Data File 1.xlsx]Maine'!$B$1:$c$10,2,FALSE) Sheet 1 has formulas like =VLOOKUP(a2,'[Data File...

I am trying to write a self-marking assessment for classes of young school pupils I teach and I have struggled to find a solution to a key issue: How can I automatically detect if a student has used a formula or worked out the correct values to posed problems rather than solved it in their head...

Hi folks, I am using Excel 2003. Looking for a way to find out whether some data I want to delete in a sheet is used by a formula somewhere else within the file. Is there a way to check that? In other words, I have the number 500 in A1, and I want to delete it, but I don't know whether it will...

Hi -- I am using the INDEX function to pull values from a named range on another tab with matching row and column headings. Example formula is: =INDEX(DebtOther,MATCH($A12,INDEX(DebtOther,,1),FALSE),MATCH(CM$2,INDEX(DebtOther,1,),FALSE)) Where: DebtOther = named range on another tab $A12 =...

This may not be possible but it has bothered me for quite some time. I'm looking for a way to lock part of a formula with multiple structured references. I'm aware that when using structured referencing that filling or dragging will shift all of the references and that copying and pasting will...

Hi I have a fairly large (33Mb) and complex workbook which has stopped allowing me to trace dependents on some formulas. Trace precendents is still working however. Note that this isn't that the functionality is 'dimmed', it is that the application states that there are no dependents when...