×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel - batch convet dynamic cell references to static

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

RE: Excel - batch convet dynamic cell references to static

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.

RE: Excel - batch convet dynamic cell references to static

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/
 

RE: Excel - batch convet dynamic cell references to static

(OP)
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

 

RE: Excel - batch convet dynamic cell references to static

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))

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

Clarification in bold:

Quote (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.

RE: Excel - batch convet dynamic cell references to static

Whoops. That was wrong. Let me try again.

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

RE: Excel - batch convet dynamic cell references to static

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

CODE --> Formula for sheet 2 ULHC cell E19

=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.

RE: Excel - batch convet dynamic cell references to static

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources