SandsB
Well-known Member
- Joined
- Feb 13, 2007
- Messages
- 705
- Office Version
- 365
- Platform
- Windows
I have rows of data from a VLOOOKUP that gets data from daily source files except the files aren't created on Sundays or for the dates we haven't reached yet. (So on 8/1 I get data for 8/1 but every other day for the current month shows #N/A)
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-01-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-02-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-03-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-04-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
etc
I have monthly tabs to get all this data and it all works fine but every month I have to edit the file and remove the formulas for Sundays and even then I still have an ugly report for the days still in the future. How can I code the same formula so if the source file doesn't exist yet the formula just returns a blank?
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-01-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-02-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-03-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-04-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
etc
I have monthly tabs to get all this data and it all works fine but every month I have to edit the file and remove the formulas for Sundays and even then I still have an ugly report for the days still in the future. How can I code the same formula so if the source file doesn't exist yet the formula just returns a blank?