Data Calling from sheets not yet created

[Ed Davis]

I have a workbook that has several sheets. Some sheets are created after
data is entered into another sheet where I copy the sheet to a new sheet.
The new sheet name is in date format (31-05-09) dd-mm-yy. I am trying to
have one sheet grab info from the sheet created. Example this data
collection sheet has dates in column A and I want to pull data from the
sheet created with that date.
Thank you in advance for your help.

 

[Ron Rosenfeld]

I have a workbook that has several sheets. Some sheets are created after
data is entered into another sheet where I copy the sheet to a new sheet.
The new sheet name is in date format (31-05-09) dd-mm-yy. I am trying to
have one sheet grab info from the sheet created. Example this data
collection sheet has dates in column A and I want to pull data from the
sheet created with that date.
Thank you in advance for your help.

Here's one method.

Assume your date is in A2, as a regular Excel date that matches the name of the
desired worksheet. (The format of the date in A2 is irrelevant, so long as it
is really a date).

Assume that you want to return the contents of Cell B2 on the "new sheet".

So:

A2: 5/30/2009
B2: =INDIRECT(ADDRESS(2,2,,,TEXT(A2,"dd-mm-yy")))

B2 will return the contents of "'30-05-09'!$B$2"
--ron

 

[Pete_UK]

If your sheet names are in column A (the dates), and you want to pull
information from D1 (say) of every sheet, then you can use this:

=INDIRECT("'"&TEXT(A1,"dd-mm-yy")&"'!D1")

then copy this down.

If you want to trap errors for sheets not yet created, then you can do
it like this:

=IF(ISERROR(INDIRECT("'"&TEXT(A1,"dd-mm-yy")&"'!D1")),"",INDIRECT
("'"&TEXT(A1,"dd-mm-yy")&"'!D1"))

Hope this helps.

Pete

 

[Ed Davis]

This did not work.
In the master sheet in cell a4 I have the date 06/01/09.
In the sheet named 06-01-09 I want to import the value in cell j11 into the
master sheet cell b4
I have the following in cell B4
=INDIRECT(ADDRESS(2,2,,,TEXT(A4,"dd-mm-yy")))
I get a #REF! ERROR.

 

[Ed Davis]

This did not work.
In the master sheet in cell a4 I have the date 06/01/09.
In the sheet named 06-01-09 I want to import the value in cell j11 into the
master sheet cell b4
I have the following in cell B4
it does not have the beginning ' before the =

'=INDIRECT("'"&TEXT(A4,"dd-mm-yy")&"'!J11")





If your sheet names are in column A (the dates), and you want to pull
information from D1 (say) of every sheet, then you can use this:

=INDIRECT("'"&TEXT(A1,"dd-mm-yy")&"'!D1")

then copy this down.

If you want to trap errors for sheets not yet created, then you can do
it like this:

=IF(ISERROR(INDIRECT("'"&TEXT(A1,"dd-mm-yy")&"'!D1")),"",INDIRECT
("'"&TEXT(A1,"dd-mm-yy")&"'!D1"))

Hope this helps.

Pete

 

[Bernie Deitrick]

You seem to be using mm-dd-yy not dd-mm-yy

=INDIRECT(ADDRESS(2,2,,,TEXT(A4,"dd-mm-yy")))

so try

=INDIRECT(ADDRESS(2,2,,,TEXT(A4,"mm-dd-yy")))

HTH,
Bernie
MS Excel MVP

 

[Ed Davis]

The newly created sheet name is as follows 1-06-09
The date in A4 of the master sheet is as follows: 01/06/09

 

[Ron Rosenfeld]

This did not work.

That may be because you did not provide accurate information, and did not read
HELP for the ADDRESS function.

In the master sheet in cell a4 I have the date 06/01/09.

And that should represent, according to what you wrote previously (dd-mm-yy), 6
Jan 2009. Does it?

In the sheet named 06-01-09 I want to import the value in cell j11 into the master sheet cell b4

I have the following in cell B4

Then you need to change the Row and Column arguments in the ADDRESS function to
reflect J11, and not B2 as I had used in my example.

=INDIRECT(ADDRESS(2,2,,,TEXT(A4,"dd-mm-yy")))

I get a #REF! ERROR.

If your date in A4 represents 1 Jun 2009, then the sheet name will be rendered,
in "dd-mm-yy" format (which is what you specified) as 01-06-09. On the other
hand, if the date in A4 represents 6 Jan 2009, then there is something else
going on.

The bottom line is that the format in the TEXT function has to match the format
that you are using for your sheet name; and the date in column A needs to be a
proper Excel date, (and not, for example, a text string).

From what you've written, I suspect there is confusion here.

Also, the ROW and COLUMN arguments in the ADDRESS function need to match the
Address from which you want to pull your data. Otherwise, how would Excel know
which cell you want?


--ron

 

[Ed Davis]

I have just found that if I change the new sheet name to 01-06-09 it works
fine.
Therefore can someone help to convert this code to force the day to have a
leading zero.
Here is the code I use to create the new sheet.

Sub CopySheet1andRename()
With Range("B1")
myname = Day(.Value) & "-" & _
Format(Month(.Value), "00") & "-" & Right(.Value, 2)
End With
Sheets("Caixa").Copy After:=Sheets(Sheets.Count)

With ActiveSheet
..Name = myname

End With
End sub

 

[Ed Davis]

I was able to figure out what I need to do.
Thank you all for your help.

It is nice to see that there are people out there that are willing to help
others.

 

[Ron Rosenfeld]

I have just found that if I change the new sheet name to 01-06-09 it works
fine.
Therefore can someone help to convert this code to force the day to have a
leading zero.
Here is the code I use to create the new sheet.

Sub CopySheet1andRename()
With Range("B1")
myname = Day(.Value) & "-" & _
Format(Month(.Value), "00") & "-" & Right(.Value, 2)
End With
Sheets("Caixa").Copy After:=Sheets(Sheets.Count)

With ActiveSheet
.Name = myname

End With
End sub

To just generate myname:

Sub MakeSheetName()
Dim myname As String
myname = Format(Range("B1").Value, "dd-mm-yy")
Debug.Print myname
End Sub


--ron