1

I have a defined table of several rows and columns containing data and formulae. Some of the columns' cells' formulae are:

=[@[Weight]]*1000

Where 'Weight' is the name of a column heading.

I want to change =[@[Weight]]*1000 into something like =C3*1000.

I've read converting to range does the trick (in the tools section of the Design tab menu). I've tried this however, it changes my formula to the following:

='Sheet3'!$C3*1000

So, is there a way to change =[@[Weight]]*1000 to =C3*1000 for all cells of this format?

...(Not 'Sheet3'!$C3*1000, as the cell I'm editing is already in Sheet 3, so there shouldn't be a reason to reference Sheet 3)

Any help/advice is appreciated. Thanks in advance.

|improve this question
  • When 'Converting to Range' I lose the benefit of having a defined table...So, can I do the above without converting to range? - I'm assuming after converting to range you can't reverse the conversion to keep the table but remove the structured labels from the formulae. – questioner56 Jun 18 at 14:16
1

I don't know what reason you would actually want to do this, but follow these steps:

  • First convert to range as described,

  • Then press CTRL +A, CTRH + H,

  • In the find box put 'Sheet3'!

  • In the replace box put ''

This will remove all reference to sheet 3,

|improve this answer
  • Thank you for your response. Two things: If I 'convert do range', wouldn't I loose the benefits of having a 'defined table'? Secondly, I tried to edit your answer, as with your current values it didn't work as you say. shouldn't the find box be 'Sheet3'!$ and the replace box be empty? – questioner56 Jun 18 at 13:49
  • the replace box should be empty, I was trying to show that with '', but the $ is attached to the Cell ref not the sheet name. if you don't want to change the table, just re-write your formula to look to cell C3 instead, and drag down etc. – PeterH Jun 18 at 14:02
  • The reason why I want to do this is as follows: Having Sheet 3 in every formula is distracting. =C3*1000 is much cleaner than ='Sheet3'!$C3*1000. – questioner56 Jun 18 at 14:04
  • Having the formula with a structured label like =[@[Weight]]*1000 is confusing when trying to locate and difficult to edit. Also, I'm unable to move the highlighted [@[Weight]] whereas when it's formatted as =C3*1000 I can easily move (with my cursor by clicking and dragging) C3 to D3 or C1 or F5 etc. – questioner56 Jun 18 at 14:06
  • Thank you again for your response. I think so far, in order to keep the defined table, we can only do it as you mentioned in your last comment ("just re-write your formula to look to cell C3 instead, and drag down etc") which is pretty manual (not automatic)...- I'm assuming after converting to range you can't reverse the conversion to keep the table but remove the structured labels from the formulae. I've added a comment to my question to reflect this. – questioner56 Jun 18 at 14:23

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.