Reference cell by identifier(not address) between sheets











up vote
1
down vote

favorite












Scenario:
I have a programmatically generated Excel (.xls) document with hundreds of rows of numerical values, in multiple sheets. Each sheet has total/subtotal values (using summation). The last sheet is a summary sheet that adds up these totals using INDIRECT and referencing the sheet and cell addresses (eg "'11-November-2018'.D13").



Problem:
Once generated programmatically, the formulae and functions are set. However, this document must be editable i.e. deletion of rows in any sheet should be possible such that changes in totals and subtotals in respective sheets should reflect in the summary sheet. However, once a row is deleted, the total/subtotal cell address changes, and the INDIRECT function in the Summary sheet fails.



Desired:
Is there a way to reference cells between sheets such that deletion of rows in one sheet reflect the changes correctly in the Summary sheet?






microsoft-excel worksheet-function







share|improve this question




























    up vote
    1
    down vote

    favorite












    Scenario:
    I have a programmatically generated Excel (.xls) document with hundreds of rows of numerical values, in multiple sheets. Each sheet has total/subtotal values (using summation). The last sheet is a summary sheet that adds up these totals using INDIRECT and referencing the sheet and cell addresses (eg "'11-November-2018'.D13").



    Problem:
    Once generated programmatically, the formulae and functions are set. However, this document must be editable i.e. deletion of rows in any sheet should be possible such that changes in totals and subtotals in respective sheets should reflect in the summary sheet. However, once a row is deleted, the total/subtotal cell address changes, and the INDIRECT function in the Summary sheet fails.



    Desired:
    Is there a way to reference cells between sheets such that deletion of rows in one sheet reflect the changes correctly in the Summary sheet?






    microsoft-excel worksheet-function







    share|improve this question


























      up vote
      1
      down vote

      favorite









      up vote
      1
      down vote

      favorite











      Scenario:
      I have a programmatically generated Excel (.xls) document with hundreds of rows of numerical values, in multiple sheets. Each sheet has total/subtotal values (using summation). The last sheet is a summary sheet that adds up these totals using INDIRECT and referencing the sheet and cell addresses (eg "'11-November-2018'.D13").



      Problem:
      Once generated programmatically, the formulae and functions are set. However, this document must be editable i.e. deletion of rows in any sheet should be possible such that changes in totals and subtotals in respective sheets should reflect in the summary sheet. However, once a row is deleted, the total/subtotal cell address changes, and the INDIRECT function in the Summary sheet fails.



      Desired:
      Is there a way to reference cells between sheets such that deletion of rows in one sheet reflect the changes correctly in the Summary sheet?






      microsoft-excel worksheet-function







      share|improve this question















      Scenario:
      I have a programmatically generated Excel (.xls) document with hundreds of rows of numerical values, in multiple sheets. Each sheet has total/subtotal values (using summation). The last sheet is a summary sheet that adds up these totals using INDIRECT and referencing the sheet and cell addresses (eg "'11-November-2018'.D13").



      Problem:
      Once generated programmatically, the formulae and functions are set. However, this document must be editable i.e. deletion of rows in any sheet should be possible such that changes in totals and subtotals in respective sheets should reflect in the summary sheet. However, once a row is deleted, the total/subtotal cell address changes, and the INDIRECT function in the Summary sheet fails.



      Desired:
      Is there a way to reference cells between sheets such that deletion of rows in one sheet reflect the changes correctly in the Summary sheet?






      microsoft-excel worksheet-function




      microsoft-excel worksheet-function






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 1 hour ago

























      asked 1 hour ago









      n00bProgrammer

      16318




      16318



























          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "3"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














           

          draft saved


          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1374962%2freference-cell-by-identifiernot-address-between-sheets%23new-answer', 'question_page');
          }
          );

          Post as a guest























          active

          oldest

          votes













          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















           

          draft saved


          draft discarded



















































           


          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1374962%2freference-cell-by-identifiernot-address-between-sheets%23new-answer', 'question_page');
          }
          );

          Post as a guest






































































          -

          Popular posts from this blog

          Terni

          Image
          Questa voce o sezione sull'argomento Umbria non è ancora formattata secondo gli standard . Contribuisci a migliorarla secondo le convenzioni di Wikipedia. Segui i suggerimenti del progetto di riferimento. Terni comune Localizzazione Stato   Italia Regione Umbria Provincia Terni Amministrazione Sindaco Leonardo Latini (Lega Nord) dal 25-06-2018 Territorio Coordinate 42°34′N 12°39′E  /  42.566667°N 12.65°E 42.566667; 12.65  ( Terni ) Coordinate: 42°34′N 12°39′E  /  42.566667°N 12.65°E 42.566667; 12.65  ( Terni ) Altitudine 130 m s.l.m. Superficie 211,90 km² Abitanti 111 189 [1] (31-12-2017) Densità 524,72 ab./km² Frazioni Vedi elenco Comuni confinanti Acquasparta, Arrone, Colli sul Velino (RI), Labro (RI), Montecastrilli, Montefranco, Narni, Rieti (RI), San Gemini, Spoleto (PG), Stroncone Altre informazioni Cod. postale 05100 Prefisso 0744 Fuso orario UTC+1 Codice ISTAT...
          Read more

          A new problem with tex4ht and tikz

          Image
          3 When run through pdflatex the code snippet below produces: but when I run it through make4ht the following web page, without the numbers, is generated: (The difference in scale is an artifact of my skills in taking screenshots.) Here is the code: documentclass{article} usepackage{tikz} begin{document} begin{tikzpicture} foreach x in {-3,...,3} { draw(x,0.25) --(x,0)node[below]{x}; } foreach x in {-2.5,...,2.5} { draw(x,0.18) --(x,0); } draw[thick,<->](-3.5,0)--(3.5,0); filldraw[blue!50!white](1.5,0) circle (1mm); draw[red] (-3.5,-1) rectangle (3.5,1); % make bounding box higher end{tikzpicture} end{document} Only the first line of the tikzpicture environment is necessary. The rest is just to show that everything else is OK. I am...
          Read more

          Sun Ra

          Image
          .mw-parser-output .avviso .mbox-text-div>div,.mw-parser-output .avviso .mbox-text-full-div>div{font-size:90%}.mw-parser-output .avviso .mbox-image div{width:52px}.mw-parser-output .avviso .mbox-text-full-div .hide-when-compact{display:block} Questa voce o sezione sull'argomento compositori statunitensi non cita le fonti necessarie o quelle presenti sono insufficienti . Puoi migliorare questa voce aggiungendo citazioni da fonti attendibili secondo le linee guida sull'uso delle fonti. Sun Ra Sun Ra nel 1992 al conservatorio del New England Nazionalità   Stati Uniti Genere Fusion Free jazz Hard bop Space music Jazz d'avanguardia Musica sperimentale Big band Periodo di attività musicale 1934 – 1993 Strumento pianoforte, sintetizzatore Modifica dati su Wikidata  · Manuale .mw-parser-output .citazione-table{margin-bottom:.5em;font-size:95%}.mw-parser-output .citazione-table td{padding:0 1.2em 0 2.4em}.mw-parser-output...
          Read more