=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmm")&" Draws'!G4")

Or, more simply:

=INDIRECT("'"&TEXT(A1,"mmm")&" Draws'!G4")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Another way. Much shorter but uses a volatile function.

You'd have to standardize your sheet names, though. Some use the short
month name: Jan, Feb, Mar. And some use the long month name: April, June,
July. You'd have to make them all the same, either short or long. Then:

For the short month name:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmm")&" Draws'!G4")

For the long month name:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmmm")&" Draws'!G4")

--
Biff
Microsoft Excel MVP


"Rookie" wrote in message
...
I know that I am exceeding my nesting levels. But can anyone provide a
fomula
that would help me make all 12 months work? Here is the first 6 months
formula that I have:

=IF(MONTH(B4)=1,'Jan Draws'!G4,IF(MONTH(B4)=2,'Feb
Draws'!G4,IF(MONTH(B4)=3,'Mar Draws'!G4,IF(MONTH(B4)=4,'April
Draws'!G4,IF(MONTH(B4)=5,'May Draws'!G4,IF(MONTH(B4)=6,'June
Draws'!G4,IF(MONTH(B4)=7,'July Draws'!G4,"")))))))

Thanks for the formula. It seems to work for the first column. Do you have
any advice for a formula to insert the following months automatically
dpendant on the start date?

For Example: If May is the starting month and I want it to pull data from
all 12 months, then in column two I want June Data, then July Data etc.

I currently have a table made but it is ver time consuming and can get
confusing to other users (and myself:)
"T. Valko" wrote:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmm")&" Draws'!G4")

Or, more simply:

=INDIRECT("'"&TEXT(A1,"mmm")&" Draws'!G4")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Another way. Much shorter but uses a volatile function.

You'd have to standardize your sheet names, though. Some use the short
month name: Jan, Feb, Mar. And some use the long month name: April, June,
July. You'd have to make them all the same, either short or long. Then:

For the short month name:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmm")&" Draws'!G4")

For the long month name:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmmm")&" Draws'!G4")

--
Biff
Microsoft Excel MVP


"Rookie" wrote in message
...
I know that I am exceeding my nesting levels. But can anyone provide a
fomula
that would help me make all 12 months work? Here is the first 6 months
formula that I have:

=IF(MONTH(B4)=1,'Jan Draws'!G4,IF(MONTH(B4)=2,'Feb
Draws'!G4,IF(MONTH(B4)=3,'Mar Draws'!G4,IF(MONTH(B4)=4,'April
Draws'!G4,IF(MONTH(B4)=5,'May Draws'!G4,IF(MONTH(B4)=6,'June
Draws'!G4,IF(MONTH(B4)=7,'July Draws'!G4,"")))))))

If I understand what you want:

A1 = 5/1/2007

=INDIRECT("'"&TEXT((MONTH($A1)+COLUMNS($A:A)-1)*30,"mmm")&" Draws'!G4")

Copied across, will return: May Draws!G4, Jun Draws!G4, Jul Draws!G4, Aug
Draws!G4, etc

--
Biff
Microsoft Excel MVP


"Rookie" wrote in message
...
Thanks for the formula. It seems to work for the first column. Do you have
any advice for a formula to insert the following months automatically
dpendant on the start date?

For Example: If May is the starting month and I want it to pull data from
all 12 months, then in column two I want June Data, then July Data etc.

I currently have a table made but it is ver time consuming and can get
confusing to other users (and myself:)
"T. Valko" wrote:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmm")&" Draws'!G4")

Or, more simply:

=INDIRECT("'"&TEXT(A1,"mmm")&" Draws'!G4")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Another way. Much shorter but uses a volatile function.

You'd have to standardize your sheet names, though. Some use the short
month name: Jan, Feb, Mar. And some use the long month name: April,
June,
July. You'd have to make them all the same, either short or long. Then:

For the short month name:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmm")&" Draws'!G4")

For the long month name:

=INDIRECT("'"&TEXT(MONTH(A1)*30,"mmmm")&" Draws'!G4")

--
Biff
Microsoft Excel MVP


"Rookie" wrote in message
...
I know that I am exceeding my nesting levels. But can anyone provide a
fomula
that would help me make all 12 months work? Here is the first 6 months
formula that I have:

=IF(MONTH(B4)=1,'Jan Draws'!G4,IF(MONTH(B4)=2,'Feb
Draws'!G4,IF(MONTH(B4)=3,'Mar Draws'!G4,IF(MONTH(B4)=4,'April
Draws'!G4,IF(MONTH(B4)=5,'May Draws'!G4,IF(MONTH(B4)=6,'June
Draws'!G4,IF(MONTH(B4)=7,'July Draws'!G4,"")))))))