Powershell and Excel

Copper Contributor

I need to automate an Excel workbook.  I have structured the bones of my workbook with Powershell. It will contain the following sheets: Dashboard (to be used later), Entitled Users, 30 Days, 60 Days, 90 Days, Last Login. Entitled Users sheet has Column headers ID, Name Manager, 30 Days, 60 Days, 90 Days. I need to insert a formula into the 30 Days, 60 Days, 90 Days column (it's Column D) for the range of the worksheet. I created the 30 / 60 / 90.

 

In Entitled Users sheet, the ID, Name, Manager columns (A, B, C, D)  have data.  I need to insert a formula in column D that compares Column A to a Column in the other worksheets.  I have included the formula for 30 Days below.  How would I use powershell to insert that formula into that column (D) for the entire range within  in Entitled Users.

=IF(ISNA(VLOOKUP(A2,'30 Days'!$A$2:'30 Days'!$A$10000,1,FALSE)),"No","Yes")

 

 

1 Reply