Vlookup and #N/A

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
91
Office Version
  1. 2013
Platform
  1. Windows
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:-

VBA Code:
=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:-

Code:
=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:-

Code:
=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
Code:
=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

Well-known Member
Joined
Oct 24, 2015
Messages
7,417
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
 
Last edited:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,398
Members
412,590
Latest member
Velly
Top