1

very much a beginner to excel...

i need to get a cells value J1 from the first worksheet of the month named 2018-04-01 through to the lastest worksheet 2018-04-25

am able to do that no problem using below formula

=SUM('2018-04-01:2018-04-25'!J1)

however, each day I have to create a new worksheet that increments by one day in this format(year-month-day) aka 2018-04-25

thats fine, I do that manually and move it to the right of the previous day 2018-04-24 so visually the tabs looks like this
... | 2018-04-24 | 2018-04-25 | april |

in my april worksheet - where I run the very first formula I posted - I have several instances of the formula that use the same worksheet range, but reference different cells

e.g.

  • =SUM('2018-04-01:2018-04-25'!J1)
  • =SUM('2018-04-01:2018-04-25'!C1)
  • =SUM('2018-04-01:2018-04-25'!A1)

to get various totals for each day into the month worksheet

but I have to go into each cell on the month worksheet after creating a new daily worksheet and change the last date in the range 2018-04-25 to the current day

what I am ideally wanting to do is as below

in my april worksheet;
instead of this
=SUM('2018-04-01:2018-04-25'!J1)

something like this =SUM('2018-04-01:PrevSheet()'!J1)

(which i am hoping should automatically reference the latest worksheet and update all of the formulas in the april workbook to save manually altering them)

so mixing a literal worksheet name with a kind of variable

have spent MANY hours trying examples on the internet but nothing close so far

p.s. i have also added the =PrevSheet formula to a cell in my april worksheet cell M1 which returns the text 2018-04-25 which i then tried to reference in that first formula eg =SUM('2018-04-01:M1'!J1) , but still no dice

any help would be gratefully received

Edit: have added screenshots as requested

original formula:
original formula

new suggested formula:
new suggested formula

0

You're close, just need to add INDIRECT() function:

=SUM(INDIRECT("'2018-04-01:" & M1 & "'!J1")) 
  • Máté, thanks so much for the answer, upon trying that i get #REF! instead of the expected cell result. maybe you can't mix a literal worksheet name(2018-04-01) with a cell that is general text in cell M1 derived from =prevsheet in one range formula? – ddahmer Apr 25 '18 at 13:24
  • please evaluate your formula (formulas - evaluate) and see how Excel understands it. Hopefully that will help. If not, then please update your question with a screenshot of the formula. – Máté Juhász Apr 25 '18 at 13:50
  • i tried to evaluate the formula and it did return M1 as being 2018-04-28 which looks correct, but other than that am not sure how to further use that tool. have included screenshot links as requested, thanks a lot. – ddahmer Apr 25 '18 at 14:38
  • *mistake in above comment, meant 2018-04-25 – ddahmer Apr 25 '18 at 14:46
  • Your links doesn't seem to work:( – Máté Juhász Apr 25 '18 at 18:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.