Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Set function In excel

Copper Contributor

I have a column with flags (1,0) and in another column I'm looking to set the value to say 1000 after 'n' number of row of flag occurrence.


I have this =IF(F9=1,INDIRECT("H"&A9+n)="1000",0), I know this doesn't work but it allows me to access the row I want to set to a 1000 using indirect by itself. The issue is then how to set the value in that cell to 1000. I cant find any set or append functions I would have in Python or R. Is it just possible via VBA?

1 Reply


Excel can be regarded as an implementation of the Functional Programming paradigm and, as such, does not allow variables (here cell values) to be set other than by the code that defines them.  Even UDFs written in VBA (an imperative programming language) are blocked from having such 'side effects'.


Your choices would appear to be

1. writing code in VBA that reads from the worksheet but does not use the functionality of the worksheet or

2. writing worksheet formulas in the 'other' column to read the flags and determine whether a value (1000) is to be displayed or not in the formula cell.