0

I created a part of a sheet that refers to other parts of the sheet and to parts of another sheet. There is a mix of partially explicit (A$4, $B2), explicit ($A$4), and non explicit (A4) references within a formula. For example: 

=IF(AB$4>$J8,0,IF(B8=$K8,0,SUM(INDEX(Masterdata!$A$1:$CA$90,MATCH($A8,Masterdata!$A$1:$A$90,0),B8+32):INDEX(Masterdata!$A$1:$CA$90,MATCH($A8,Masterdata!$A$1:$A$90,0),$K8+31))))

There are dozens of this type of formula throughout the sheet.

I want to take that part and move it to another sheet for display purposes. However as you can guess any relative references get skewed and the formula breaks.

Is there a way to convert all cell references to an explicit reference with sheet name, e.g. $k8 -> Input!$k$8 ?
Or do I have a few hours of tedious work ahead of me?

Edit to try and comply with the on hold flag:

I have 50 cells with formulas like

=IF(AB$4>$J8,0,IF(B8=$K8,0,SUM(INDEX(Masterdata!$A$1:$CA$90,MATCH($A8,Masterdata!$A$1:$A$90,0),B8+32):INDEX(Masterdata!$A$1:$CA$90,MATCH($A8,Masterdata!$A$1:$A$90,0),$K8+31))))

I would like to cut and paste these cells and move to another sheet. In doing so, I need to change all references to other cells in the formulas to be explicit and to include sheet name. Above formula would then become:

=IF(Input!$AB$4>Input!$J$8,0,IF(Input!$B$8=Input!$K$8,0,SUM(INDEX(Masterdata!$A$1:$CA$90,MATCH(Input!$A$8,Masterdata!$A$1:$A$90,0),Input!$B$8+32):INDEX(Masterdata!$A$1:$CA$90,MATCH(Input!$A$8,Masterdata!$A$1:$A$90,0),Input!$K$8+31)))).

After the conversion of all the formulae, I will then be able to Cut the range and then paste it anywhere on another page. Using paste as "link" is viable, but would require the original range to stay in place, and that, in my opinion, would be sloppy and feels like cheating.

Can code be made that can look at a cell, see if there is a formula in it, determine if there are ranges referenced, set them as explicit references if not already one, see if there is already a sheet reference and if there isn't add one? Each formula will be unique and will vary in length and type.

Bill Flippen
  • 453
  • 1
  • 4
  • 19
  • no I haven't will have to look into that. – Bill Flippen Oct 08 '15 at 03:54
  • No I haven't, will have to look into that. That appears to do the trick in this case, but still wondering if there is a way to do what I asked because I will have to keep the original material in place. – Bill Flippen Oct 08 '15 at 04:00
  • a bit of a disadvantage, would prefer to cut and paste the whole area. – Bill Flippen Oct 08 '15 at 04:57
  • Possible duplicate of [Does Excel have a built in method for parsing formulas? (ie: to obtain a list of included RANGE references)](http://stackoverflow.com/questions/11320626/does-excel-have-a-built-in-method-for-parsing-formulas-ie-to-obtain-a-list-of) – ivan_pozdeev Oct 08 '15 at 11:48
  • @pnuts whole area= the entire part (range) of a sheet. – Bill Flippen Oct 09 '15 at 04:12
  • @pnuts Simply put, sheet one has two areas that I wish to separate out. with the "paste as link" method, the area I am trying to move would have to stay in its original place. So if A1 has "hi" and E1 has a formula that is =A$1, I would like to cut E1 and place it on Sheet 2 in cell A1. – Bill Flippen Oct 09 '15 at 20:14

1 Answers1

-1

If it's just for (static) display purposes only, you could just paste values, rather than paste references? PasteSpecia|Values - CtrlAlt V

Conor
  • 156
  • 5