too many levels of nesting

[Guest]

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,"")))))))

 

[Guest]

Try:

=CHOOSE(MONTH(B4),'Jan Draws'!G4,'Feb Draws'!G4,'Mar Draws'!G4,'April
Draws'!G4,'May Draws'!G4,'June Draws'!G4,'July Draws'!G4)

add the rest of the sheet references you need.

 

[T. Valko]

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")

 

[Guest]

If you use a consistent format for the month names, i.e. always the first 3
letters of each month, e.g. "Apr draws" or "Jun draws" etc. then you could
use the formula

=INDIRECT(TEXT(B4,"'mmm")&" draws'!G4")

 

[T. Valko]

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

Or, more simply:

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

 

[Guest]

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]

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

 

[Harlan Grove]

If you use a consistent format for the month names, i.e. always the first
3 letters of each month, e.g. "Apr draws" or "Jun draws" etc. then you
could use the formula

=INDIRECT(TEXT(B4,"'mmm")&" draws'!G4")

....

Yeah, but INDIRECT is a volatile function, so it recalculates whenever
anything triggers recalculation. If there'd be a lot of these calls, the
CHOOSE formula, though longer, would produce much faster recalculation
generally. Also, the CHOOSE formula would be easy to fill or copy/paste.
Yours isn't because you've hardcoded the cell address.