SOLVED

Formula Copy/Paste Help

Copper Contributor

I think my question is fairly basic, but I don't have the savviest understanding of Excel and I'm completely mystified about where I'm going wrong.  I'm using VLOOKUP and named cells to create a solving model. The formula I'm currently using is =(0.5*namedcell+VLOOKUP(A1,lookup,2,FALSE)+VLOOKUP(B1,lookup,3,FALSE)+namedcell2)

 

For context, my "lookup" is a grouping consisting of three columns, each row has a name and two number values.  The two "namedcell's" are simply number values that my solver should manipulate to produce an average advantage and a mean.  Long story short, I want to be able to apply this to A2 and B2, A3 and B3 etc via copy and paste where it will solve for each specific set.  I've done this with other models, and I even did it with my first attempt at this model about 90 minutes ago.  I made a mental error in the first attempt, and since then my attempts to re-write what I did are not working.  Let's say for the first cell my equation works out to 29.5.  Now when I copy and paste, instead of getting 27.5 for cell2, 15 for cell3, I'm getting 29.5 straight down the line.  How can I copy and paste my formula to make sure each cell is getting its unique, intended value?

2 Replies
best response confirmed by BrianJennings (Copper Contributor)
Solution

@BrianJennings 

  1. Activate the Formulas tab of the ribbon. Make sure that Calculation Options is set to Automatic.
  2. Check the status bar at the bottom of the workbook window. Do you see Circular References near the left-hand side? If so, try to find the cell(s) containing a circular reference and change or delete the formula in that cell/those cells.
Thank you so much you saved the day. It looks like the Calculation Options was set to Manuel which was causing my error.
1 best response

Accepted Solutions
best response confirmed by BrianJennings (Copper Contributor)
Solution

@BrianJennings 

  1. Activate the Formulas tab of the ribbon. Make sure that Calculation Options is set to Automatic.
  2. Check the status bar at the bottom of the workbook window. Do you see Circular References near the left-hand side? If so, try to find the cell(s) containing a circular reference and change or delete the formula in that cell/those cells.

View solution in original post