Excel Formula Issue

[rcolving]

I have a Lotus spreadsheet that was created before I started here. I am attempting to recreate it in Excel. I have a Field I a dynamic field G6 which the user puts a date in. When they do this Field B10 needs to be populated with the number in the corresponding date field that is on sheet1 of my data input. For example: if nov 30 2004 is typed into G6 I want B10 to be populated by the field on my input sheet for Nov 2004. If 12/31/08 is typed into G6 I want B10 to be populated by the corresponding number in the field on my input sheet for that date.

 

[SteveO59L]

Have a look at vlookup()

 

[rcolving]

I'll give it a look. Thanks

 

[rcolving]

I've attempted to use vlookup but it is giving me a #N/A. My formula is as follows

=VLOOKUP(G6,Sheet2!A2:B121,2,FALSE)

G6 is the date entered
Sheet2! A2:B121, is the data and date columns, Column A being the dates, Column B the data.

2 is the column number the data is in

False because I want an exact match.

What am I missing?

 

[swinglow]

first thing you should do when you get an error on a vlookup is do a manual search to ensure that what you're looking up is in the data

 

[SteveO59L]

Doesnt appear to be anything wrong with your formula or application of it.


Perhaps your dates look like dates, but are actually text.


If you send me a p.m with your e-mail, I'll send you a sample sheet.

 

[Jon von der Heyden]

What format does the date take in column A? Let's say as an example they are dd-mm-yyyy, then check to see if this works:

=VLOOKUP(TEXT(G6,"dd-mm-yyyy"),Sheet2!A2:B121,2,FALSE)

 

[rcolving]

All is working now thanks everyone for your help. Now does anyone know how to obtain a total to date based on a fiscal year starting in July?

 

[Jon von der Heyden]

Try

=SUMPRODUCT(--(A2:A121>=DATE(2010,07,01)),B2:B121)

 

[rcolving]

I have a formula that is supposed to retrieve numbers from a data sheet that will look at the date entered, find all fields that fall within the dates of that fiscal year. for example

If 5/2008 is entered, the fiscal year is 7/2007 to 6/2008. the formula should retrieve and sum all numbers entered on the data sheet from 8/2007 to 5/2008, however, it is not working correctly.

The data sheet has records from 2001 through to current. I've got a formula that works for Jul through Dec. but won't work for Jan through Jun. The formula is

{=SUM(IF('Input Sheet'!$B$2:$DQ$2>DATE(YEAR(F7),MONTH(4567),),IF('Input Sheet'!$B$2:$DQ$2<DATE(YEAR(F7),MONTH(F7)+1,),'Input Sheet'!$B$5:$DQ$5)))}

Any ideas would be appreciated.