Data Calling from sheets not yet created

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

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.
 
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.
Click to expand...

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
 
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
 
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.
 
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
 
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
 
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
 
This did not work.
Click to expand...

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.
Click to expand...

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
Click to expand...
I have the following in cell B4
Click to expand...

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")))
Click to expand...
I get a #REF! ERROR.
Click to expand...

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
 
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
 
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.
 
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
Click to expand...


To just generate myname:

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


--ron
 
Back
Top