How to drag a formula increasing tab number only?

[Ba1ley]

Hi Guys,

I have a spreadsheet that has a tab for every week of the year, the formula in question is below:

=COUNTA('1'!$C10:$H10)

How can I drag this formula across increasing the tab number without having to manually change it on each cell?

Thanks in advance,

Ba1ley

[kvsrinivasamurthy]

If your formula is in G column

=COUNTA(INDIRECT("'"&Column()-Column($G$1)+1&"'!$C10:$H10"))

[Ba1ley]

Apologies I should have stated, the formula is on a different tab at the start of the sheet titles "2013".

[kvsrinivasamurthy]

Upload dummy file with correct sheet names and where you want formula.

[Tony Valko]

Assuming you want the first formula to appear in cell A2 then copied across:

=COUNTA(INDIRECT("'"&COLUMNS($A2:A2)+2012&"'!C10:H10"))

[Ba1ley]

I'm not very good at explaining this am I, it doesn't help being a complete novice at Excel.

The formula itself will on tab '2013' in cell 'B140' and when devised correctly dragged all the way across to 'BB140', collecting COUNTA data from tab '1' cells ($C10:$H10). What I want to do is drag the formula across the across the sheet increasing the tab number by 1 each time but keeping all other aspects of the formula the same.

Does that make sense? If not I'll have to upload a dummy.

Thanks again.

[kvsrinivasamurthy]

pl upload file your supposed formula typed manually in 3 or more cells.

[Tony Valko]

The formula itself will on tab '2013' in cell 'B140' and when devised correctly dragged all the way across to 'BB140'

OK, try this...

Entered in cell B140 and copied across as needed:

=COUNTA(INDIRECT("'"&COLUMNS($B140:B140)&"'!C10:H10"))

[Ba1ley]

Tony you are a genius That works a treat thank you very much!

I don't suppose you could recommend a book for learning Excel? I've picked up bits and bobs over the years but nothing in comparison to what there is to learn, I must say I'm very keen to learn more.

[Tony Valko]

There's a good list of books here:

http://contextures.com/xlbooks.html

Another good way to learn...

If you can "invest" a few hours every day in forums like this one reading posts and studying the solutions and with lots of experimentation, in 4 or 5 years you'll be an Excel Guru!

[Ba1ley]

Thanks for the link, it's pay day a week on Monday so I'll have to invest some of my hard earned!

I'll be sure to stick around on the forum too, I didn't realise how much I had to learn. I am keen though

[Tony Valko]

Good deal. Thanks for the feedback!

[Ba1ley]

Hey Tony, is it possible to make the formula only count cells with a number greater than 0?

[Tony Valko]

Like this:

=COUNTIF(INDIRECT("'"&COLUMNS($B140:B140)&"'!C10:H10"),">0")

[Ba1ley]

Absolutely superb, thanks again!

I was having a play around with it myself before I asked for help and I wasn't a million miles away. I suppose it will all become clearer once I understand the symbols a little better.

[Tony Valko]

You're welcome!