EXCEL GURUS - help needed

[MikeyIs4Dcats]

Okay, I have a workbook set up as follows:

MONTHLY
1
2
3
4
...

So there is a monthly summary, and then 31 worksheets (one for each day of a month)

On each worksheet, there is a series of about a dozen cells I need to summarize on the MONTHLY sheet. Call them cells A10, A11, A12...A22.

On the MONTHLY sheet I need a formula to put in the column for each days value of cells A10....A22.

So to recep we have the following:

Sheets
MONTHLY
1
2
3
etc.

Cells on each numbered sheet above
A10 through A22

Given that, what would the formula need to be to update the cell on MONTHLY for each day's total in cell A10. I don't want to have to hand-link all 372 cells.

 

[esun]

I don't quite understand exactly what you want to do, but the gist of it seems to be grabbing data from one sheet and displaying it (or doing some computation on it and displaying it) in another sheet.

That can be done with a command like this: ='Sheet_Name'!Cell

For example, if wanted some cell in MONTHLY to point to cell A10 in sheet 1, I could go to that cell in MONTHLY and type ='1'!A10.

 

[mayest]

I agree, you aren't explaining this too well. Do you want to add (or maybe average) all of the A10's from each sheet? In that case, you can make a 3D reference like =sum(sheet1:sheet31!a10). That would calculate the total for the data in each day's cell A10.

 

[MikeyIs4Dcats]

Sorry, you know how it is when you know what you want, but it's hard to explain.

I have 31 sheets, each with 12 values on it for sales in 12 categories. I need the monthly sheet to pull each of the 12 values from each sheet into cells in a row for each day.

DAILY

TYPE1 SALE1
TYPE2 SALE2
TYPE3 SALE3
and so on



MONTHLY

Day TYPE1 TYPE2 TYPE3 TYPE 4
1 SALE 1 SALE 2 SALE 3 SALE 4
2 SALE 1 SALE 2 SALE 3 SALE 4
3 SALE 1 SALE 2 SALE 3 SALE 4
4 SALE 1 SALE 2 SALE 3 SALE 4
and so on

I know how to link worksheets, what I don't know how to do is write a formula that will adjust for the sheet name and cell location as I copy it across the matrix on the MONTHLY sheet.

 

[yuchai]

Look into the indirect and address functions.

For example:
=indirect(address(30,3,,,"1")) will point to cell reference '1'!$C$30

Adjust the "30" and "3" for each of the 12 specific locations and link the worksheet name to your "Day" column on your summary page.

I hope this helps. Let me know if you need more help to figure it out.

 

[MikeyIs4Dcats]

Originally posted by: yuchai
Look into the indirect and address functions.

For example:
=indirect(address(30,3,,,"1")) will point to cell reference '1'!$C$30

Adjust the "30" and "3" for each of the 12 specific locations and link the worksheet name to your "Day" column on your summary page.

I hope this helps. Let me know if you need more help to figure it out.

from what i can tell, I think that would do it, but I'm too dumb today to figure it out. Can you help me figure at least the first one out?

Here's screencaps of the sheets. Should have just done this earlier...

Daily

Monthly

 

[yuchai]

In B7 in the monthly tab, enter:
=indirect(address(8,7,,,$A7))
In C7, enter:
=indirect(address(9,7,,,$A7))
D7, enter:
=indirect(address(10,7,,,$A7))
etc etc

Once you complete the first row, just copy & paste the whole row down to complete all the days.

 

[MikeyIs4Dcats]

Originally posted by: yuchai
In B7 in the monthly tab, enter:
=indirect(address(8,7,,,$A7))
In C7, enter:
=indirect(address(9,7,,,$A7))
D7, enter:
=indirect(address(10,7,,,$A7))
etc etc

Once you complete the first row, just copy & paste the whole row down to complete all the days.

you rock man! I really appreciate it...