SOLVED

Locking reference to another worksheet

Copper Contributor

Hello,

 

I'm setting up a home-made bibliography data system in Excel to use for my PhD dissertation since I can't find anything useful online.

 

I have set up separate, identical, data entry worksheets in a workbook named Bibl. 1, Bibl. 2, etc. that are similar to index cards.

 

Now, I want to link each cell in the "Bibl. X" pages to a separate worksheet, named Section 3, that summarizes the "Bibl. X" so each "Bibl. X" entry is shown in a table of rows, one row per "Bibl. X" page.

 

This works just fine for the first table row, which has simple formulas: ='Bibl. 1'!C6 ; ='Bibl. 1'!G6 ; etc.

 

However, when I want to copy the formulas in the first row to the next row so the cell values are locked: 'Bibl. 2'!$C$6 , just the formula shows up.

 

I am also having an issue referencing to a worksheet cell that contains a URL address. Here, again, just the formula shows up regardless of whether I use $ or not.

 

I realize this is probably a very simple problem, but I can't find a reference to this in "help" either in Excel or online. This is probably because I don't know how to phrase the question that'll get me to the right help page for this issue.

 

Thanks very much for any suggestions regarding where I can find help or how to fix the formula.

 

Tomas

7 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Tomas-Vitek 

First, select the table, and set its number format to General.

 

In A7:

=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!C6")&"","")

 

In B7:

=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!G6")&"","")

 

etc.

 

 

Thank you very much for your reply! I'll make the first thing adjustments first thing tomorrow. @HansVogelaar 

@Tomas-Vitek I have similar issue… Maybe @Hans Vogelaar will know solution as well.
Unfortunatelly I I'm not allowed to share worksheet, but I can tell you, that each individual Workbook represent one month of statistical data and problem is this:

 

I struggle with "formula autocorrection" that cause to crash formulas referencing sheets in the same workbook, if you copy them from Original_Workbook to New_Workbook
I mean, if I copy =IF(('1'!$AS5)="";"";'1'!$AS5)
It becomes =IF(('[Original-Workbook.xlsx]1'!$AS7)="";"";'[Original_Workbook.xlsx]1'!$AS7)

Doesn't matter if I just Copy&Paste or Copy-Sheet, this bogus autocorrection is trigerred.

 

Normaly, I would just open Original_Workbook and "Save as" New_Workbook, but it already contains statistical data within intricate formulas - so copying those would be maybe even more complicated, than manually adjusting this mid-way formula (and other similar ones) workbook by workbook.

Curently I have to do that 4 months (= 4 workbooks with at least 2 new sheets and a lots of formulas each) back, but probably have to do that 12 months (= 12 workbook) back at the end.
Also, there is a risk, that other new features will be requested, creating same problem again, so I need to find more straightforward solution.

@OC_the_tech_priest 

One option:

  • Select the cell with the formula you want to copy.
  • Click in the formula bar.
  • Select the entire formula.
  • Copy it (Ctrl+C).
  • Switch to the other workbook.
  • Select the cell where you want to create the formula.
  • Paste (Ctrl+V).
  • If required, fill down.
Not a solution.

What you propose is just basic Copy&Paste that works only for single cell. But as I mentioned before, it's month-worth data, which means, that I need to copy on average 500 cells per sheet from one or more sources, since I aggregate statistical data from in-worsheet and Out-Worksheet sources - and I need to copy the design of new feature from one Workbook to several historical ones.

In the case I used as an example, I can get around manually through selective replacement of "[Original_Workbook.xlsx]1'" for "'1'", however I would prefer more stable solution - equivalent of $ in the cell reference,

OR

If posible, Ii would be AWESOME if I could disable autocorrect completely - as for example in other workbooks, I can't edit references, if reference doesn't exist at moment and I'm forced to create "fake future" workbooks, just to be able to create reference on them.
E.g. Excel straight up refuse to replace "[January_2023.xlsx]" with [January_2024.xlsx] unless January_2024.xlsx physically exist.

@OC_the_tech_priest 

I'd create a template workbook with all the formulas that you need in place, but no data.

Create a new workbook from this template each time you need it, and start entering data in it.

As I mentioned in my first post, that's how I do it when generating new workbook.

However time to time I need to add new sheet with new feature to already existing workbooks and agreggate quarterly/yearly data through the new feature.

And as I mentioned before, it's nigh impossible to copy template of said new feature without it getting mutilated by "autocorrection".
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Tomas-Vitek 

First, select the table, and set its number format to General.

 

In A7:

=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!C6")&"","")

 

In B7:

=IFERROR(INDIRECT("'Bibl. "&ROW()-6&"'!G6")&"","")

 

etc.

 

 

View solution in original post