Thanks:  0
Likes:  0

# Thread: Function using worksheet name

1. ## 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. ## 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 ??

3. ## 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. ## re: Function using worksheet name

Originally Posted by Alf123
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. ## 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. ## re: Function using worksheet name

@alf123

You get responce from 4 members.

Great you only respond on 1 member.

7. ## re: Function using worksheet name

Hi Oeldere

the spreadsheets will be for certain people only

8. ## re: Function using worksheet name

Originally Posted by Alf123
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. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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