1

Let's say I have a data table in Excel that consists statistics of a population:

enter image description here

In percentage column, each cell is calculated by taking the ratio of "counts / total counts", in which the total counts is fixed to B$8.

Now, if I copy this table to another location, the total counts would still point to the original cell B$8, while all other relative references update their locations automatically.

Is there an automated/smart way that I can make all formulas in the copied table to know the new location of total counts and use that new cell location instead?

6
  • 1
    I'm sure you'll probably receive an answer here in the next few minutes, but keep in mind that Stack Overflow is for programming questions - and sometimes questions relating to complex formulas. This would have been much better suited for Super User. – K.Dᴀᴠɪs Jul 10 '19 at 21:38
  • For better understanding share Screen Shot with us,, also the error message if getting any !! – Rajesh S Jul 11 '19 at 7:08
  • Cont,, are you pasting in another Sheet? – Rajesh S Jul 11 '19 at 7:15
  • I can't replicate this, Excel simply changes the references accordingly for me. – JvdV Jul 11 '19 at 7:34
  • I think what @GTwu meant is moving the entire table to a different place in the same sheet i.e moving A1:C8 to D10:F17, that all relative references will update but B$8 which is absolute. – Moshe Gross Jul 11 '19 at 12:34
0

Try using this formula in place of B$8

VLOOKUP("Total",A1:B99,2,0)
1
  • Put some illustration like sample data to justify the Solution,, will attract more readers to appreciate all this!! – Rajesh S Jul 11 '19 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.