Excel - batch convet dynamic cell references to static

[bbuubb]

Hello,

My goal is to have a master sheet with all my data on it, and secondary worksheets that mimic portions or columns of the master for easy organization and printing purposes.

I can initially accomplish this by filling formula (sheet1!a1=sheet!2a1) across the whole worksheet. In order to fill, the cell references need to be dynamic. I need them all to end up static so excel doesn't try to compensate for row or column deletions/addition on the master sheet.

Is there a way to batch convert a lot of formulas to static? i.e. (sheet1!$a$1=sheet!2$a$1), or propagate/fill formulas so that the cell references change but then end up static? Or maybe a macro to just insert $ at each cell reference.

I've been searching for a solution for a while now and cant seem to find one. Your help is appreciated.

Matt

 

[GregLocock]

Read the xml file into a powerful plain text editor. Then use search and replace. I dare say in Unix you could do it from the command line.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[electricpete]

I'm not sure converting to absolute it going to help.

If you have a reference to a cell A11 and B$11 and insert a row at row 5, then both references will update (to A12 and B$12).... IF the dependent spreadsheet is open when you make the change.

I think that if the dependent files are not open when you are editing the master file, then they will not be affected by addition/deletion of rows columns in the master.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

As pete said, inserting $ in the formulas will convert them from relative to absolute, but they will still change if you insert or delete rows above the cell reference, or columns to the left.

To get cell references that always refer to the same cell you can use the Index() or Indirect() functions.

For instance, Index(A1:Z26,10,10) will always refer to cell J10, as long as you don't delete Row 1 or 26, or Column A or Z.

If you use =INDEX(Sheet1!1:65536,10,10), then it refers to the whole sheet, and you can delete any row or column on Sheet1 without affecting the address the formula refers to.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[bbuubb]

Thanks guys,

Good ideas that I hadn't thought of. It turns out your right about absolute references still being affected when I delete rows.

It looks like the index function will work for me if I populate row and column call numbers around my custom range for the index function to reference as I fill it through the custom range. It seems cumbersome but, I do like how it works. I may be able to use row() and column() to aid in generating call numbers for me.

It would look like this: =INDEX(Sheet1!$B$2:$H$31,Sheet2!$A2,Sheet2!B$1). Where my custom range on sheet2 is bordered by my call references in row 1 and column A.

Thanks for helping me think through this!
Matt

 

[electricpete]

If the data you are linking to is a rectangular block, you can avoid the extra index rows/columns by using row() and column() functions.

For example, let's say
* your maseter data is in B2:H31 of sheet 1 (the referred-to sheet)
* You want the data to display in C3:I32 of sheet 2 (the referring sheet)

You can accomplish this by puting the following formula in cell C3 of sheet2 and copy to the rest of the sheet 2 range C3:I32:

[CODE Sheet 2 C3 Formula]=INDEX(Sheet1!B2:H31,ROW(C3)-ROW(B2), COLUMN(C3)-COLUMN(B2))[/CODE]

Note that C3 is ULHC of data in the referring sheet and B2 is ULHC of data in the referred-to sheet. We might have been tempted to insert "Sheet1!B2" instead of B2 since we are thinking about a cell in Sheet1... but that defeats the purpose.... we would be back to having our formula corrupted upon changes to the referred-to sheet. Leaving off the Sheet1 prefix accomplishes what we want.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Clarification in bold:

Note that C3 is ULHC of data in the referring sheet and B2 is ULHC of data in the referred-to sheet. We might have been tempted to insert "Sheet1!B2" in the row() and column() function arguments instead of B2 since we are thinking about a cell in Sheet1... but that defeats the purpose.... we would be back to having our formula corrupted upon changes to the referred-to sheet. Leaving off the Sheet1 prefix from the row() and column() function arguments accomplishes what we want.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Whoops. That was wrong. Let me try again.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

The previous code worked, by coincidence only. Here is corrected example.

I'm going to change the example problem statement with changes in bold. Let's say:
* your master data is in B2:H31 of sheet 1 (the referred-to sheet)
* You want the data to display in E19:K48 of sheet 2 (the referring sheet)

You can accomplish this by putting the following formula in cell E19 of sheet2 and copy to the rest of the sheet 2 range E19:K48

=INDEX(Sheet1!$B$2:$H$31,ROW(E19)-ROW($E$19)+1, COLUMN(E19)-COLUMN($E$19)+1)

Attached is an example spreadsheet.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[bbuubb]

That was some good formula writing Pete! Many Thanks! It can betiring to deduce your way through formula writing like this.