I have a script that exports raw data from a SQL DB and creates an excel file (with formatting). One of my tabs I have formulas that will show if a user has connected to the database in 30, 60, 90 days. The formula I used does not produce the correct data. SO, changed the formula. Up until now, I have simply copied the formula into the excel file after the scrips creates it. Well, it is time to modify the formula in the script. The script I am using (for simplicity sake, I will limit it to just the 30 days)
=IF(ISNA(VLOOKUP(A2,‘30Days’!A$2:‘30Days’!A$694,1,FALSE)),“No”,“Yes”)
I simply copy the formula below over the above formula in the excel file and it returns the data I need.
=IF(ISNUMBER(A2),IF(ISNA(VLOOKUP(A2,‘30Days’!A$2:‘30Days’!A$694,1,FALSE)),“No”,“Yes”),IF(COUNTIF(‘30Days’!A$2:A$676,““&A2&””),“Yes”,“No”)).
Now mind you the formula in the script has variables to account for rows counts . Below I will give you the powershell script for the current formula, as well as the formula I have tried to modify along with the error.
Current script snippet
$30DayVlookup = "=IF(ISNA(VLOOKUP(A2,30Days!A`$2:30Days!A`$$rows30,1,FALSE)),""No"",""Yes"")"
Modified script snippet that is failing with included error
$30DayVlookup = "=IF(ISNUMBER(A2),IF(ISNA(VLOOKUP(A2,30Days!A`$2:30Days!A`$$rows30,1,FALSE)),""No"",""Yes""),IF(COUNTIF('30Days'!A`$2:30Days!A`$$rows30,"*"&A2&"*"),""Yes"",""No""))"
The following is the Error I recieve. Line 260 is the modified script snippet. Any help will be appreciated.
Cannot convert value “&A2&” to type “System.Int32”. Error: “Input string was not in a correct format.”
At C:\Scripts\VDIUSage\VDIUsage_Report.ps1:260 char:1
- $30DayVlookup = "=IF(ISNUMBER(A2),IF(ISNA(VLOOKUP(A2,30Days!A`$2:30Da …
-
+ CategoryInfo : InvalidArgument: (:) [], RuntimeException + FullyQualifiedErrorId : InvalidCastFromStringToInteger