Results 1 to 8 of 8

Thread: Indirect??

  1. #1
    Board Regular
    Join Date
    Dec 2013
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Indirect??

    I have multiple sheets that I want to create a master tab. It needs to pull one cell value (J55) from each sheet. Not sure the easiest way to go about it. See example: column B,D,F,H....have to pull the day of month (each sheet is named for each day of month) Column C,E,G,I..... have to pull cell (J55) from each sheet. I'm sure I need a formula in each of those cells and I need to be able to just copy it down for all the days. See example sheet here

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect??

    actual sheet is here

  3. #3
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,792
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Indirect??

    try this

    ABCDEFG
    1
    2MayJuneJuly
    312414117341
    422467222712
    532494325403
    642045420444
    752295520505
    862414622446
    972047724737
    1082224819038
    1192551910529
    1210245910113310
    1311207311011
    1412253912012
    1513304313102813
    1614244814014
    1715241515229715
    1816262616252316
    1917281517265117
    2018018250918
    2119267019255419
    2220296120250920
    2321244421021
    2422259622022
    2523258923023
    2624282924024
    2725212125025
    2826126326026
    2927201927027
    3028272228028
    3129204129029
    323021033030
    3331228231

    Sheet2



    Worksheet Formulas
    CellFormula
    C3=INDIRECT("'"&TEXT(DATE(2019,MONTH(1&LEFT(B$2,3)),B3),"ddd mmm ")&TEXT(B3,"00")&" 2019'!$J$55")


  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,966
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Indirect??

    This should work, fiddly to test properly since your sheet is protected and I didnt fancy copying all the sheets across to a new spreadsheet.

    in Sheet1!C3
    =INDIRECT("'"&TEXT(B3&"/"&B$2,"ddd")&" "&TEXT(B3&"/"&B$2,"mmm dd yyyy")&"'!J55")
    copy down the column
    copy to other columns

  5. #5
    Board Regular
    Join Date
    Dec 2013
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect??

    Thank You Very Much. Just one last question, If the sheets don't exist I get a #REF ! error. How can I avoid this error or have the cell show a blank value. I don't mind the "0" value for sheets that exist and there is in fact a "0" value in that cell. Just trying to avoid #REF ! errors for my columns for the future months.

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,792
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Indirect??

    can use this to trap the error

    Code:
    =iferror(INDIRECT("'"&TEXT(DATE(2019,MONTH(1&LEFT(B$2,3)),B3),"ddd mmm ")&TEXT(B3,"00")&" 2019'!$J$55"),"")

  7. #7
    Board Regular
    Join Date
    Dec 2013
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Indirect??

    PERFECT, Thank You Very Much!!!

  8. #8
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,792
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Indirect??

    you're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •