Can I get my array formula to update automatically using a macro?

**jamiepounder** · 02-21-2019, 10:10 AM

Hello.

I am currently trying to develop a spreadsheet that reports to me the last day a specific staff member completed a specific task so I can keep my staff upskilled etc...

I have set this spreadsheet up (See TEST sheet attached) with the Array Formulas, and they work perfectly. The only issue is, I have to enter into each cell and press Crtl+Shift+Enter. On my full sheet, I have 60 staff members with 6 tasks I need reporting. I do not want to have to do this Ctrl+Shift+Enter 360 times a day.

Is it possible to enter a radio button so that it carries out this process for me, and by the click of a button all array formulas on that tab will update?

Thanks in advance for the assistance!

Jamie.

**6StringJazzer** · 02-21-2019, 12:48 PM

I am not following why you would have to re-enter the formulas every day, once they are set up the first time.

Also, I think your format is working against you. But if you change the reference to the cell holding the staff name (e.g., $E$4) from absolute to relative, then copy one of those blocks, you can paste/formulas to all the rest without having to enter a single formula individually.

**jamiepounder** · 02-22-2019, 05:17 AM

Hi Jeff,

Sorry for the confusion. I don't have to put the formula into each cell every day, that stays put. But with it being an array I do have to click into the cell and press Ctrl+Shift+Enter for it to return the new and correct value.

The list of tasks on the left will be added to each week, so the list becomes bigger and bigger therefore the dates for each task by each staff will become newer. For it to return a new value, I need to click into each cell and press Ctrl+Shift+Enter. This is the part I want to avoid.

Hopefully that makes more sense!

Jamie.