Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Function using worksheet name

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Function using worksheet name

    I'm using a worksheet with multiple spreadsheets. Spreadsheets named "Day1" to "Day10" and the eleventh spreadsheet is the summary sheet.
    Each spreadsheet has its own date.
    Calculations on the spreadsheets end with an colum ("AA10" to "AA100") with closing values.

    Depending on the date on the "Summery Sheet", i need a formula to lookup the date on the spreadsheets "Day1" to "Day10" and return the values from this spreadsheet to cell "C10" to"C100".

    thanks you!!

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default re: Function using worksheet name

    Alf123,

    Welcome to MrExcel.

    You will need to give us more specific information.

    What cell in Summary will have the date of interest?

    Can you define a relationship or something that will allow us to determine if the date of interest is Day 1, Day 2, or whatever?
    eg Summary has a defined 10 day date range from date in cell ?? to date in cell ??
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    @Alf123

    Why not all data on the summary sheet (maybe with VBA) and after that you can use an pivot table to get the result.

  4. #4
    Board Regular
    Join Date
    Jan 2010
    Location
    New York, USA
    Posts
    407
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    Quote Originally Posted by Alf123 View Post
    I'm using a worksheet with multiple spreadsheets. Spreadsheets named "Day1" to "Day10" and the eleventh spreadsheet is the summary sheet.
    Each spreadsheet has its own date.
    Calculations on the spreadsheets end with an colum ("AA10" to "AA100") with closing values.

    Depending on the date on the "Summery Sheet", i need a formula to lookup the date on the spreadsheets "Day1" to "Day10" and return the values from this spreadsheet to cell "C10" to"C100".


    thanks you!!

    Day2 choose day in C9 to get result from specific tab (e.g.: tab name "Day2")
    101 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A1)-1,)
    202 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A2)-1,)
    303 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A3)-1,)
    404 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A4)-1,)
    505 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A5)-1,)
    606 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A6)-1,)
    707 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A7)-1,)
    808 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A8)-1,)
    909 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A9)-1,)
    1010 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A10)-1,)
    1111 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A11)-1,)
    1212 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A12)-1,)
    1313 =OFFSET(INDIRECT("'"&$C$9&"'!AA10"),ROW(A13)-1,)
    until row 100

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    Hi Snakehips

    The date in cell E6 in the summary sheet is to indicate a closing date for a period that could be anything from 1 to 10 days.

    The spreadsheets "Day1" to "Day10" will have a specific date in cell B4. Depending on the closing date on the summery sheet, i need to find the closing date on one of the days sheets and return the value from cell AA10 to AA100 on the ?day sheet to cell C10 to C100 on the summary sheet.

    hope this helps.

  6. #6
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    @alf123

    You get responce from 4 members.

    Great you only respond on 1 member.

  7. #7
    New Member
    Join Date
    Apr 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    Hi Oeldere

    the spreadsheets will be for certain people only

  8. #8
    .
    Join Date
    Mar 2014
    Location
    Suffolk, UK
    Posts
    3,628
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    Quote Originally Posted by Alf123 View Post
    Hi Oeldere

    the spreadsheets will be for certain people only
    Oeldere was wondering why you addressed your post to only one of the four people here who have tried to help you. Also, please consider changing the title, as requested above: if you were Googling for information about this issue, would the search phrase "Alf123" get you very far? Conversely, would a sensible search string find this thread? No. In order for the thread to be useful in the future, it needs to have a sensible title so that it can be found by anyone who might require similar help.

  9. #9
    New Member
    Join Date
    Apr 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default re: Function using worksheet name

    Hi vogel997

    i do not know how to create the tab you referring to, and to link this to the spreadsheet name eg. "Day1"

  10. #10
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Function using spreadsheet name

    Alf123,

    Insofar as I understand your set up, perhaps try this.......

    Establish a named range e.g. 'TenDates' anywhere it suits, say somewhere out of the way in Summary in a column that you may wish to hide.
    In my example, TenDates is defined as M2:M11

    Excel 2007
    M
    1TenDates
    201/01/2014
    317/01/2014
    403/01/2014
    504/01/2014
    605/01/2014
    706/01/2014
    807/01/2014
    908/01/2014
    1009/01/2014
    1110/01/2014

    Summary



    Worksheet Formulas
    CellFormula
    M2=INDIRECT("Day"&ROWS($A$1:$A1)&"!B4")



    Copy the M2 formula down and that range will show the dates in B4 of your sheets Day1 to Day 10.

    Then in Summary sheet you have your date of interest in E6.

    Copy the below formula in C10 down through C10:C100
    Excel 2007
    CDE
    617/01/2014
    7
    8
    9
    10Day2 AA  data Row 10
    11Day2 AA  data Row 11
    12Day2 AA  data Row 12
    13Day2 AA  data Row 13
    14Day2 AA  data Row 14
    15Day2 AA  data Row 15
    16Day2 AA  data Row 16
    17Day2 AA  data Row 17
    18Day2 AA  data Row 18
    19Day2 AA  data Row 19
    20Day2 AA  data Row 20
    21Day2 AA  data Row 21
    22Day2 AA  data Row 22
    23Day2 AA  data Row 23
    24Day2 AA  data Row 24
    25Day2 AA  data Row 25
    26Day2 AA  data Row 26
    27Day2 AA  data Row 27
    28Day2 AA  data Row 28
    29Day2 AA  data Row 29
    30Day2 AA  data Row 30
    31Day2 AA  data Row 31
    32Day2 AA  data Row 32
    33Day2 AA  data Row 33
    34Day2 AA  data Row 34

    Summary



    Worksheet Formulas
    CellFormula
    C10=IFERROR(INDIRECT("'Day"&MATCH($E$6,TenDates,0)&"'!AA"&ROW()),"")

    Workbook Defined Names
    NameRefers To
    TenDates=Summary!$M$2:$M$11



    Hope that helps.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

Some videos you may like

User Tag List

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
  •