Google sheets help

Worried_of_wakefield

Two problems that I'm sure are easily resolved!!
1. Two page workbook, with a summary sheet collecting new monthly data 1st of the month.As I only want the data from the latest month what formula is needed to auto collect on the 1st of each month ( or when a new month cell is populated with new data) At the moment I have to manually change summary sheet cell formula to collect a new month / column reference. - =SUM('Summary 2021'!E5+'Summary 2021'!E8+'Summary 2021'!E9+'Summary 2021'!E10)  - Auto change 'E' to 'F' on 1st of feb
2. Monthly accounts Jan-Dec with a running formula with each month/column, can I retain one specific cell in the formula rather than everything changing by one column reference. =SUM(E20+1565+1000)-D98 - retain D98 cell whilst copying formula left to right
Hope I've explained ok

Neil_Jones

1) What do you mean "auto collect"?  What are you trying to do?

2) Use the dollar sign = $A$1 means you can copy it relentlessly and it will not change from cell A1.

Worried_of_wakefield

see edit may make more sense

Le_Kirk

You could use an IF statement, whereby you say (in words) If the date is later than a certain date carry out the sums.

Worried_of_wakefield

I'm aware of IF,  Can I instruct the formula to jump a column by doing this?

Le_Kirk

Why do you want to jump a column?  I think what you are trying to do would be better achieved by using visual basic as you seem to getting beyond the bounds of formulae.

Worried_of_wakefield

Sorry probably me showing my limited spreadsheet skills, I just want a cell (say A1) on sheet 1 currently reading data in sheet 2 'January' column Cell E5, to read F5 when the date changes to 1st February. is this simply done?

Neil_Jones

Worried_of_wakefield said:

Sorry probably me showing my limited spreadsheet skills, I just want a cell (say A1) on sheet 1 currently reading data in sheet 2 'January' column Cell E5, to read F5 when the date changes to 1st February. is this simply done?

With all due respect if it was "simply done" you wouldn't be asking the question

It can be done but I think you would have to get today's date, find out what month it is (so Feb would be month number 2 for example), store that figure in a "white on white" cell if you like (so it looks blank but it isn't) and then use a conditional statement.  So you could build that to look at F1 for Jan, F2 for Feb, etc.  Could be done the other way (E5 for Jan, F5 for Feb etc) but it'll get complicated and messy.

You might get some idea from looking at the "build a calendar in Excel" templates/guides.  These use date conversion techniques to automatically work out how many spaces need to be at the top of the calender.  That's sort of similar to what you need, just with a different outcome.

tizerbelle

Could you just do a Find and Replace to look for 2021'!E and change it to 2021'!F 

ETA: Just spotted you want an auto change on 1st of month.  Don't know answer to that!

tizerbelle

Would this work?  Add an extra column on the second page of your two page workbook (ideally as column A) and in it use an IF/IFS command to say if there is data in the December column (assuming your year end is December) then put December's data in column A, if there isn't any data in December, check November's column and if there is data there, put it in A etc etc - so column A is always populated with the most current value.  Then on the summary sheet you change your formula to look at column A and never have to change it again.

WaywardDriver

Can't say I fully understand what you're trying to do but if January results are in E5, Feb's in F5 etc then
=INDIRECT(CHAR(68+MONTH(TODAY())) & 5) will return value in E5 if run today, F5 if next month etc.