Excel - batch convet dynamic cell references to static
Excel - batch convet dynamic cell references to static
(OP)
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
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





RE: Excel - batch convet dynamic cell references to static
Cheers
Greg Locock
New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Excel - batch convet dynamic cell references to static
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.
RE: Excel - batch convet dynamic cell references to static
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/
RE: Excel - batch convet dynamic cell references to static
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
RE: Excel - batch convet dynamic cell references to static
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
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.
RE: Excel - batch convet dynamic cell references to static
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel - batch convet dynamic cell references to static
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel - batch convet dynamic cell references to static
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
CODE --> Formula for sheet 2 ULHC cell E19
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel - batch convet dynamic cell references to static