Function using worksheet name

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

5,835

Office Version

1. 365

Platform

1. MacOS

• Apr 20, 2014

• #2

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 ??

 

Upvote 0

O

Oeldere

Well-known Member

Joined

Dec 29, 2011

Messages

2,213

• Apr 20, 2014

• #3

@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.

 

Upvote 0

V

vogel997

Active Member

Joined

Jan 22, 2010

Messages

412

• Apr 20, 2014

• #4

Alf123 said:

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!!

Click to expand...

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

<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="636" style="width: 477pt; mso-width-source: userset; mso-width-alt: 23259;"> <tbody> </tbody>

 

Upvote 0

A

Alf123

New Member

Joined

Apr 20, 2014

Messages

12

• Apr 20, 2014

• #5

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.

 

Upvote 0

O

Oeldere

Well-known Member

Joined

Dec 29, 2011

Messages

2,213

• Apr 20, 2014

• #6

@alf123

You get responce from 4 members.

Great you only respond on 1 member.

 

Upvote 0

A

Alf123

New Member

Joined

Apr 20, 2014

Messages

12

• Apr 20, 2014

• #7

Hi Oeldere

the spreadsheets will be for certain people only

 

Upvote 0

AliGW

Banned

Joined

Mar 9, 2014

Messages

3,628

• Apr 20, 2014

• #8

Alf123 said:

Hi Oeldere

the spreadsheets will be for certain people only

Click to expand...

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.

 

Upvote 0

A

Alf123

New Member

Joined

Apr 20, 2014

Messages

12

• Apr 20, 2014

• #9

Hi vogel997

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

 

Upvote 0

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

5,835

Office Version

1. 365

Platform

1. MacOS

• Apr 20, 2014

• #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
1	TenDates
2	01/01/2014
3	17/01/2014
4	03/01/2014
5	04/01/2014
6	05/01/2014
7	06/01/2014
8	07/01/2014
9	08/01/2014
10	09/01/2014
11	10/01/2014
Summary

Cell Formulas
Range	Formula
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
	C	D	E
6			17/01/2014
7
8
9
10	Day2 AA data Row 10
11	Day2 AA data Row 11
12	Day2 AA data Row 12
13	Day2 AA data Row 13
14	Day2 AA data Row 14
15	Day2 AA data Row 15
16	Day2 AA data Row 16
17	Day2 AA data Row 17
18	Day2 AA data Row 18
19	Day2 AA data Row 19
20	Day2 AA data Row 20
21	Day2 AA data Row 21
22	Day2 AA data Row 22
23	Day2 AA data Row 23
24	Day2 AA data Row 24
25	Day2 AA data Row 25
26	Day2 AA data Row 26
27	Day2 AA data Row 27
28	Day2 AA data Row 28
29	Day2 AA data Row 29
30	Day2 AA data Row 30
31	Day2 AA data Row 31
32	Day2 AA data Row 32
33	Day2 AA data Row 33
34	Day2 AA data Row 34
Summary

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

Named Ranges
Name	Refers To	Cells
TenDates	=Summary!$M$2:$M$11

Hope that helps.

 

Upvote 0