autofill and autocopy of cells from other worksheets

[monere]

Hey everyone,

Can anyone please tell me how to do this? Just bear with me until I explain what I need to do. Thanks and... here we go!

I have 1 MAIN worksheet that is named TOTALS and 12 secondary worksheets (one for each month of the year) that are called.... January, February, etc.

Now, the main worksheet has 12 columns (each one corresponding to each month, thus to each of the secondary worksheets that I will pull data from to populate the main sheet's cells). This main worksheet also has 34 rows, one for each campaign I am running in Adwords.

I will update this excel file every month for the year 2014 as it follows. At the beginning of each month I will manually fill the worksheet for the previous month with data pulled from my Adwords account, and I want each time I do this that the main worksheet (the one labeled TOTALS) updates itself with the aggregated (sums, or averages where appropriate) data, OR (and this is actually the reason I am asking here) with data copied ad-literam from the corresponding worksheets.

I know how to use functions in excel (sums, averages, etc.) so that's not where I'm stuck at. What I need to know is how to make the main sheet update itself with the data from other sheets exactly like I put it in those secondary sheets.

Here's an example of what I mean. Say that in January I spent $50 on campaign no.1 and $100 on campaign no. 2. Then, in February I spent $40 on campaign 1 and $20 on campaign 2.

Now, when I go to the worksheet for January to enter $50 for campaign 1, and $100 for campaign 2 I want these numbers to automatically appear in the main worksheet (the one labeled TOTALS) in their corresponding fields. So, as you can see, this is not a "=SUM()" type of function that I need, but rather a simple copy cell type of function.

Here's the thing. I googled how to do this for one cell and I found the answer very quickly. The answer was this:

1) go to the worksheet you want to pull data from
2) select the cell you want to copy
3) click "copy"
4) go to main sheet (the one named TOTALS)
5) click "paste"
6) a "paste options" icon appears, click on it and select "link cell"
7) DONE!

So yeah, I did this without a problem. And doing so, I looked at what the naughty Excel put into that cell for me and I saw this: "=January!$E$3"

Still with me? Ok

Now, with all other functions (sums, averages, etc.) when I select the cell containing the formula above (=January!$E$3) and drag the "autofill" handle to the right/down all other cells autopopulate as they should. But not this time and this is where I'm stuck at.

More specifically, when I drag that handle over the remaining cells, instead of the cells autofilling with their correspondent data, they just copy the above formula into ALL cells. Again, this is where I'm stuck at and what I need help with. I'm sure that such a cool program like excel can do this somehow, I just don't know how (yeah, I'm beginner, don't bash me please!). And what really bothers me is that I didn't find anything in Google either, probably because I don't know the correct search query that I need to type into big G.

So, anybody in here that knows how to do this and can help me? I would appreciate it.

Eventually, I could just copy that formula into all cells and then replace "January" with all other months, and "$E$3" with "$E$4", "$E$5", etc. 12 columns x 35 campaigns equals 420 cells that I'll have to manually fill. It will probably take me 15-20 minutes. That's not exhausting I know, but I wanna learn how to do it the easy way, if that's possible.

So yeah, let me know if you know how to do this

Thanks and awaiting for your answers

 

[AlphaFrog]

Remove the $ from the formula then drag-copy.
=January!E3

Do a search for something like Excel relative and absolute cell references.


Instead of Paste special: Link, this technique works as well

Select the destination cell
Enter an equal sign =
select the source cell and keyboard:Enter​

 

[monere]

Remove the $ from the formula then drag-copy.
=January!E3

Do a search for something like Excel relative and absolute cell references.


Instead of Paste special: Link, this technique works as well
Select the destination cell
Enter an equal sign =
select the source cell and keyboard:Enter

Hey AlphaFrog! Thanks for the fast reply...

Now, I did remove the $ sign and then dragged the fill handle across the entire table (the 12 columns x 35 rows) and it didn't do anything good. BUT, doing so I figured out what I had to do to autofill the columns one by one. So, it's a progress

So, removing the $ sign from before E, as well as before 3 didn't do anything good, but I kept it before E (since I need to reference the column E in each of the 12 sheets) and then dragged that handle down, until I covered all 35 rows. I did this for December only (cause that's what made sense to do with a formulat that references the December spreadsheet only) and it finally did exactly what I wanted to do. So, keeping the formula like this "=December!$E3" instead of "=December!E3" like you said helped me autofill the column corresponding to the December worksheet. When using the same formula (=December!$E3) and dragging the fill handle accross the entire spreadsheet it didn't work (which was obvious since the formula used only referenced one spreadsheet, not all of them). But like I said, I will use the correct formula but replace December! with the names of the other 11 sheets and will do this manually for each column left and stick to this method until I find out how to automate the whole thing in 1-2 clicks.

Also, I did search about the relative and absolute cell references like you recommended and didn't learn anything I didn't know. I appreciate your help though, don't worry

And last but not least, your third recommendation is pretty basic and again, it doesn't solve my problem. But thanks again!

Now, since I'm at it, could you please help me with one more thing? I don't know how to ask this in as few words as possible since I ain't no native English speaker so, please bear with me while I try to explain as clear as I can. Here it goes:

I need one of the cells in the main sheet to automatically calculate the average costs, but in real time. Yeah, I know that you don't understand what I mean by "real time" and this is where it gets tricky for me because I don't know how to explain it. But here's what I mean:

right now, I have this formula typed into the cell that I want to display the averages:

=AVERAGE(December:January!B1)

This formula calculates the averages indeed. But, it calculates them by adding the value of the B1 cells in EACH worksheet (january through december) and then diving this total value by 12. BUT - and this is where I need help with - we are in July right now and I only have January to June sheets filled since these are the only months I have data for. So, the average should be calculated by adding the values for January through June, and then dividing by 6, instead of 12 since January to June are 6 months. In August when I'll fill the July worksheet with its corresponding data, I need the averages to be calculated by diving the total value to 7. And so on. I hope you understand what I mean.

So yeah, that would be all for now. If you could only tell me how to do this (apparently simple) function that would take so much burden off my shoulder. It's the only thing that stops me from completing this excel report and seding it to the company I work for.

Thanks again and I'm waiting for your reply. Or for anybody else's reply, I don't mind

Cheers

 

[AlphaFrog]

If B1 had blanks for the non-filled months, the AVERAGE function will ignore them (not include them in the count). If B1 had zeros for non-filled months, they are included.

If B1 is a formula, you could maybe construct it to return blank instead of zero.
e.g
=IF(SUM(B2:B10)=0,"",SUM(B2:B10))

 

[monere]

If B1 had blanks for the non-filled months, the AVERAGE function will ignore them (not include them in the count). If B1 had zeros for non-filled months, they are included.

If B1 is a formula, you could maybe construct it to return blank instead of zero.
e.g
=IF(SUM(B2:B10)=0,"",SUM(B2:B10))

Cool! This actually worked :D

I ended up with this: =IF(SUM(December:January!B1)=0,"",AVERAGE(December:January!B1))

Which works!

May I know however what the thing with those quotation marks (the ones between the commas) is? Why are they empty and why do they have to be there in the first place??

Other than these, thanks a lot for your help! You have no idea how much headache you saved me

Cheers!

 

[AlphaFrog]

I don't think you got it correct.

What is in cell B1 on the monthly sheets? If it's a formula, what is the formula?

 

[monere]

I don't think you got it correct.

What is in cell B1 on the monthly sheets? If it's a formula, what is the formula?

I may not gotten it correctly, but the funny thing is that it works LOL.

the formula in the monthly sheets is a simple "=SUM(B3:B49)" where B3:B49 are all the Adwords campaigns. So, this formula repeats itself on each monthly spreadsheet at the top of the worksheet, and I want the main sheet (the one that displays the totals/averages) to make an average out of all these sums.

 

[AlphaFrog]

I may not gotten it correctly, but the funny thing is that it works LOL.

the formula in the monthly sheets is a simple "=SUM(B3:B49)" where B3:B49 are all the Adwords campaigns. So, this formula repeats itself on each monthly spreadsheet at the top of the worksheet, and I want the main sheet (the one that displays the totals/averages) to make an average out of all these sums.

That's how I understood it originally. I still don't see how the way you did it could work.

If a month-sheet has no data, its B1 SUM formula returns a zero. That zero would be included in your summary average of all the B1s.

If you put this formula in B1 on each month-sheet, the sheets with no data would have blanks in B1 (not zeros) and they would not be included in the summary Average.
=IF(SUM(B3:B49)=0,"",SUM(B3:B49))