Copying formulas with incremental worksheet references

[Pharmacisticus]

Hi Everyone,

I'de really like some help with incremental formulas and worksheets, please.

Currently, I have a formula =SUM(Jul!K36:P36) in cell M9 - this references cells I need summed from my "Jul" worksheet (obviously right!)

I have a series of work sheets named by month Jul,Aug,Sep... (you get the drift)

I'm looking for a formula so that in cell M10 I can have =SUM(Aug!K36:P36) and in M11 =SUM(Sep!K36:P36) and so on... without having to do the manual handling.

Can anyone help?

PS I'm a total noob, still just grasping referencing between worksheets! - So I'm going to need things explained real S L O W

Thanks

Josh

 

[steve the fish]

Hi. You may want to think about your spreadsheet design because using these type of formulas isnt ideal. Are you sure you need 12 tabs for months? Anyway this will do the Jul one:

=SUM(INDIRECT(TEXT(DATE(2018,6+ROWS($A$1:A1),1),"mmm")&"!K36:P36"))

Drag down for Aug, Sep etc.

 

[Pharmacisticus]

Hi,

Thanks for the brilliant answer!

Unfortunately, on re-reading, I've misworded the question...

"Currently, I have a formula =SUM(Jul!K36:P36) in cell M9 - this references cells I need summed from my "Jul" worksheet (obviously right!)

I have a seriesofwork sheets named by month Jul,Aug,Sep... (you get the drift)

I'm looking for a formula so that in cell M10 I can have =SUM(Aug!K36:P36) and in M11 =SUM(Sep!K36:P36) and so on... without having to do the manual handling."

I'm actually looking for a formula so that in cell N9 I can have =SUM(Aug!K36:P36) and in O9 =SUM(Sep!K36:P36) and so on... without having to do the manual handling. Sorry, I should probably have had a little less wine before posting...

Also, could anyone recommend a good beginners training course so I don't make these design faults again?

 

[steve the fish]

Ok if you want to go across rather than down change the ROWS in the formula to COLUMNS.