Vlookup from series of tabs

[Kramer]

Hello,
I have a workbook which has worksheets for each day of the month (i.e. "1",
"2").
I would like to retreive the figure in cell C164 of each worksheets (i.e.
for each day of the month) on to another workbook. The other workbook where i
want the figure to show, i have the dates running horizontally at the top and
i want the figure in cell C164 of the other worksheet to show up in the
corresponding date in this workbook.

thanks,

 

[Sean Timmons]

OK, so if you want cell in sheet named 1 in cell A1,

=INDIRECT(COLUMN()&"!C164")

And paste across. If you are starting in column B, just do

=INDIRECT(COLUMN()-1&"!C164")

 

[Jacob Skaria]

Suppose Row 1 is with the dates in excel date format; the below formula will
pick the cell value c164 of the corresponding sheet. Sheets are numbered in
the format 1,2,3,..31.

=INDIRECT("'[workbook.xls]" & DAY(A$1) & "'!C164")

If this post helps click Yes

 

[pshepard]

Hi Kramer,

Another trick to allow you to keep the file closed in the future that you
are referring to -

In the file with the worksheets - one for each day of the month -

=LEFT(CELL("filename"),FIND("]",CELL("filename"),1))

This can be in any worksheet or cell. For purposes of this explanation -
lets say it is on Sheet1!A1

Will give you the file's current name and path.

In the summary workbook while the workbook above is still open,

=indirect([WorkbookName]Sheet1!$A$1&DAY(A$1)&"'!C164")

When you close the first workbook, now the formula will keep track of where
the workbook is that is being referred to.
--
If this post helps click Yes
---------------
Peggy Shepard

Suppose Row 1 is with the dates in excel date format; the below formula will
pick the cell value c164 of the corresponding sheet. Sheets are numbered in
the format 1,2,3,..31.

=INDIRECT("'[workbook.xls]" & DAY(A$1) & "'!C164")

If this post helps click Yes
---------------
Jacob Skaria

Hello,
I have a workbook which has worksheets for each day of the month (i.e. "1",
"2").
I would like to retreive the figure in cell C164 of each worksheets (i.e.
for each day of the month) on to another workbook. The other workbook where i
want the figure to show, i have the dates running horizontally at the top and
i want the figure in cell C164 of the other worksheet to show up in the
corresponding date in this workbook.

thanks,