Vlookup and #N/A

[Kayslover]

Hi,

I have 12 workbooks called 01 Jan, 02 Feb, 03 Mar etc which are created from a template. (i.e. one for each month of the year).

Within the workbook I have individual sheets called Sheet1 thru to Sheet5 which hold weekly dates for the individual weeks in the month. Sheet1 always starts of being the first Sunday of month, sheet2 will be the next Sunday within that month etc.

I then have a sheet called “Monthly Totals” which by using the following array formula extracts details from Sheet1, Sheet2 etc. The formula is:-

=VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A2)>0),0))&"'!$B$4:$V$10"),2,FALSE)

and it is present rows B2 to A32 in columns B to V.

Col A2 in Monthly Totals has a date starting as the first Sunday of the month, A3:A32 has the following formula:-

=IFERROR(IF(Formula!F$2-(A2+1)>=0, A2+1,""),"")

, where Formula!F$2 is the end of the month for the month in question.

The above formula will populate A2:A32 with just the date with the number of days in the month. Months like Feb and those that contain 30 days in the month, in Column A will only be populated with the exact number of days for the month

The problem I have, is as some months only have 30 days, the Vlookup reports #N/A for the 31st of those months and obviously February will report the 29th, 30th and 31st as #N/A.

In Row 33, I use the following formula:-

=SUMIF($A$2:$A$32,"<>",B2:B32)

to sum all the data for the month.

Is there any way to automate that for months with less than 31 days, the Vlookup in the appropriate rows for columns B to V either remove the formula or do not give #N/A? as it is hampering the formula

=SUMIF($A$2:$A$32,"<>",B2:B32)

to sum all the data for the month in row 33. People have to remember to remove the Vlookup in rows that do not have a date to get the totals in Row 33.

Thanks.

 

[sandy666]

the ISNA and IFERROR and both work) and bingo it works.

IFNA not ISNA
if you will use a number or digit you don't need quotes, eg. ,0) , only for text, eg. ,"anytext")

You are welcome