Need another pair of eyes - Know there is an easier way

JSpears

I have 2 sheets (soon to be 3) where I am pulling metrics from 25+ different sheets to get monthly data. It is working, but every month I have to manually edits every row (over 300) to add the next month of data. So I would love some ideas on how to make this easier and less manual. Here is an example of my sheet and formula as it stands today:

I would love to just select the column and drag to the right for the dates to automatically update. But I'm having a brain freeze when I try. I'd rather not have to add more columns to the 25+ individual sheets and be able to just use some clever formulas in my metrics sheets.

Any ideas would be greatly appreciated.

JSpears

@SteyJ and @Andrée Starå -

Thank you all! I was able to get this work. Here's what I did:

So now all I have to do is select the cells for January and drag across to the next column (February) and all dates are automatically updated. So it went from taking me close to 2 hours to update all of my formulas to just a couple of minutes to get all of the sheets updated. So Merry Christmas to me!

SteyJ

https://community.smartsheet.com/discussion/114636/need-another-pair-of-eyes-know-there-is-an-easier-way

Put a ! in front of the = in one of the formulas.

you should have just the formula in plain text with a ! in front

Once you do this, you can paste it down the entire column next to it.

Now, select all of the rows with the plain text formula, and CTRL-F. Search for the date you need to change on all cells

In this instance, it would be 2023, 12, 1 and 2023, 12, 31

Now, in the replace with text box, enter the new date "2024, 1, 1" and "replace all". Do this for the end of the month as well.

Finally, select the entire column and search for the ! and replace all with nothing in the text box for replace all

SIDE NOTE: Sometimes when I have to do this (which is quite often), I actually use formulas to update formulas in plain text, and then just control-F to convert them back without the ! before. There's a lot of functionality you can do with this method.

JSpears

Thanks Jacob - the problem is that every row has different sheet references. So I have to edit every row, add the ! and then I can do the find/replace. But again, I have over 500 rows so I'd like to find a way to do this so that I don't have to touch every row. If I need to change my formulas, I'm open to that...not sure if there is an easy way to just reference the month...right now I'm using two dates to capture all rows that fall on or between those two dates, but I really just need a count of all that are for each month...but I also want to make sure they are for the correct year. Maybe Santa will bring me a great idea this Christmas!

Jennifer

JSpears

@SteyJ So here is an idea but I'm not able to get it to work yet. My idea is to have the Month in Row1 and Year in Row2 and to be able to just drag all formulas from say column 7 to column 8 and the month and year change automatically. For instance, if I drag from column 6 (December) to column 7 the dates will automatically look at the month/year for that new column, in this case it would look for January.

However, I've not been able to get this work yet. Any ideas?

SteyJ

https://community.smartsheet.com/discussion/comment/410792#Comment_410792

I really like the new idea your implementing and I think i'm going to test some things with that format...

If possible, can you share with me the formula you're using? I see some things that look a little strange.

Thanks!

Andrée Starå

Hi @JSpears

I hope you're well and safe!

To add to Stey's excellent advice/answer.

I recommend adding the year and dates, similar to the sheet summary, which is referenced in the formulas in the sheet, so it's easy to update the values. It can be combined with referencing the top rows with the year, month, etc.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JSpears

@SteyJ and @Andrée Starå -

Thank you all! I was able to get this work. Here's what I did:

So now all I have to do is select the cells for January and drag across to the next column (February) and all dates are automatically updated. So it went from taking me close to 2 hours to update all of my formulas to just a couple of minutes to get all of the sheets updated. So Merry Christmas to me!

Andrée Starå

@JSpears

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!