Excel: Reference row indicated by cell












1















I have a sheet where each row is a month and columns are monthly numbers.



I made a separate sheet for each month where the numbers from each month can be pulled together and added up.



To do this, I've made a template for January and used references like: ='2019'!A8



I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)



Is it possible to pull the number for the reference from a different cell?



Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?










share|improve this question

























  • You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.

    – Doug Glancy
    Nov 24 '18 at 17:51











  • In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas

    – usmanhaq
    Nov 25 '18 at 11:48
















1















I have a sheet where each row is a month and columns are monthly numbers.



I made a separate sheet for each month where the numbers from each month can be pulled together and added up.



To do this, I've made a template for January and used references like: ='2019'!A8



I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)



Is it possible to pull the number for the reference from a different cell?



Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?










share|improve this question

























  • You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.

    – Doug Glancy
    Nov 24 '18 at 17:51











  • In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas

    – usmanhaq
    Nov 25 '18 at 11:48














1












1








1








I have a sheet where each row is a month and columns are monthly numbers.



I made a separate sheet for each month where the numbers from each month can be pulled together and added up.



To do this, I've made a template for January and used references like: ='2019'!A8



I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)



Is it possible to pull the number for the reference from a different cell?



Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?










share|improve this question
















I have a sheet where each row is a month and columns are monthly numbers.



I made a separate sheet for each month where the numbers from each month can be pulled together and added up.



To do this, I've made a template for January and used references like: ='2019'!A8



I went through each month and changed the row number being referenced. ( ='2019'!A8 would change to ='2019'!A9 for the next month.)



Is it possible to pull the number for the reference from a different cell?



Could I have somehow referenced a cell which was just the number and used it for ='2019'!A#` Rather than change all my references by hand?







excel excel-formula






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 24 '18 at 17:33









Pragmateek

9,25685488




9,25685488










asked Nov 24 '18 at 14:42









mrbobdobolinamrbobdobolina

63




63













  • You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.

    – Doug Glancy
    Nov 24 '18 at 17:51











  • In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas

    – usmanhaq
    Nov 25 '18 at 11:48



















  • You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.

    – Doug Glancy
    Nov 24 '18 at 17:51











  • In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas

    – usmanhaq
    Nov 25 '18 at 11:48

















You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.

– Doug Glancy
Nov 24 '18 at 17:51





You might also consider putting all your data in one sheet and using a pivot table in another sheet to analyze it. I'm not clear on your design, but my experience is that designs like this with data spread across multiple sheets is hard to maintain and error-prone.

– Doug Glancy
Nov 24 '18 at 17:51













In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas

– usmanhaq
Nov 25 '18 at 11:48





In my opinion, best solution is the one which is easy to maintain, update or change. I would not go for indirect references, but rather update my file structure to be more simple that does not require such formulas

– usmanhaq
Nov 25 '18 at 11:48












2 Answers
2






active

oldest

votes


















0














With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:



  =INDIRECT("2019!A"&$D1+7)


(Don't forget to ungroup.)






share|improve this answer































    0














    I suppose your data look like:



    Month       Sales
    January 500
    February 1000
    March 1500
    ... ...


    Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.



    Then you could simply use the same VLOOKUP on all these sheets:



    =VLOOKUP(A1;SALES_DATA;2;FALSE)


    And if your monthly sheets name contain the month name in some way, you could even do something like:



    =VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)


    CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".






    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      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',
      autoActivateHeartbeat: false,
      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%2fstackoverflow.com%2fquestions%2f53459284%2fexcel-reference-row-indicated-by-cell%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:



        =INDIRECT("2019!A"&$D1+7)


      (Don't forget to ungroup.)






      share|improve this answer




























        0














        With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:



          =INDIRECT("2019!A"&$D1+7)


        (Don't forget to ungroup.)






        share|improve this answer


























          0












          0








          0







          With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:



            =INDIRECT("2019!A"&$D1+7)


          (Don't forget to ungroup.)






          share|improve this answer













          With a month indicator in each monthly sheet (say in D1 and 03 for March) then with all the monthly sheets grouped together my present understanding of what it is you want should be achieved with:



            =INDIRECT("2019!A"&$D1+7)


          (Don't forget to ungroup.)







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 24 '18 at 15:19









          pnutspnuts

          48.8k76297




          48.8k76297

























              0














              I suppose your data look like:



              Month       Sales
              January 500
              February 1000
              March 1500
              ... ...


              Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.



              Then you could simply use the same VLOOKUP on all these sheets:



              =VLOOKUP(A1;SALES_DATA;2;FALSE)


              And if your monthly sheets name contain the month name in some way, you could even do something like:



              =VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)


              CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".






              share|improve this answer




























                0














                I suppose your data look like:



                Month       Sales
                January 500
                February 1000
                March 1500
                ... ...


                Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.



                Then you could simply use the same VLOOKUP on all these sheets:



                =VLOOKUP(A1;SALES_DATA;2;FALSE)


                And if your monthly sheets name contain the month name in some way, you could even do something like:



                =VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)


                CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".






                share|improve this answer


























                  0












                  0








                  0







                  I suppose your data look like:



                  Month       Sales
                  January 500
                  February 1000
                  March 1500
                  ... ...


                  Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.



                  Then you could simply use the same VLOOKUP on all these sheets:



                  =VLOOKUP(A1;SALES_DATA;2;FALSE)


                  And if your monthly sheets name contain the month name in some way, you could even do something like:



                  =VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)


                  CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".






                  share|improve this answer













                  I suppose your data look like:



                  Month       Sales
                  January 500
                  February 1000
                  March 1500
                  ... ...


                  Moreover somewhere on your monthly sheets I suppose the month will be written in a cell, say A1.



                  Then you could simply use the same VLOOKUP on all these sheets:



                  =VLOOKUP(A1;SALES_DATA;2;FALSE)


                  And if your monthly sheets name contain the month name in some way, you could even do something like:



                  =VLOOKUP(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255);SALES_DATA;2;FALSE)


                  CELL("filename";A1) retrieves the whole sheet path like C:Users...Desktop[Sales.xlsm]March".







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 24 '18 at 17:27









                  PragmateekPragmateek

                  9,25685488




                  9,25685488






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53459284%2fexcel-reference-row-indicated-by-cell%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      404 Error Contact Form 7 ajax form submitting

                      How to know if a Active Directory user can login interactively

                      TypeError: fit_transform() missing 1 required positional argument: 'X'