Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
Hi there.
I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
Hi,
Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
I believe the OP wants to see individual month numbers as well.
The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
ok - this is the actual formula I'm using:
=INDIRECT(D$2&"!"&$AI4) where D2$ is the heading at the top of the column on the summary sheet (e.g. contains text 'JAN') and $AI4 is the cell on each sheet I need to total. I was getting a formula error but I tinkered and added a " which seemed to fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to the text on the sheet tab for JAN. any thoughts? ----- Original Message ----- From: "Sean Timmons" Newsgroups: microsoft.public.excel.newusers Sent: Tuesday, May 26, 2009 1:38 PM Subject: referencing another sheet and using auto fill I believe the OP wants to see individual month numbers as well. The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
You did do the right thing there. Sorry for messign that up on you.
Click the fx to the left of the formula bar where you have the =INIDRECT formula. What is it showing as the values? "merlin" wrote: ok - this is the actual formula I'm using: =INDIRECT(D$2&"!"&$AI4) where D2$ is the heading at the top of the column on the summary sheet (e.g. contains text 'JAN') and $AI4 is the cell on each sheet I need to total. I was getting a formula error but I tinkered and added a " which seemed to fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to the text on the sheet tab for JAN. any thoughts? ----- Original Message ----- From: "Sean Timmons" Newsgroups: microsoft.public.excel.newusers Sent: Tuesday, May 26, 2009 1:38 PM Subject: referencing another sheet and using auto fill I believe the OP wants to see individual month numbers as well. The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
invalid cell reference error.
"Sean Timmons" wrote in message ... You did do the right thing there. Sorry for messign that up on you. Click the fx to the left of the formula bar where you have the =INIDRECT formula. What is it showing as the values? "merlin" wrote: ok - this is the actual formula I'm using: =INDIRECT(D$2&"!"&$AI4) where D2$ is the heading at the top of the column on the summary sheet (e.g. contains text 'JAN') and $AI4 is the cell on each sheet I need to total. I was getting a formula error but I tinkered and added a " which seemed to fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to the text on the sheet tab for JAN. any thoughts? ----- Original Message ----- From: "Sean Timmons" Newsgroups: microsoft.public.excel.newusers Sent: Tuesday, May 26, 2009 1:38 PM Subject: referencing another sheet and using auto fill I believe the OP wants to see individual month numbers as well. The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
In the sheet called Jan I have the following with `Absences` in A1, the
names in A3, A4...., and the numbers are in column B ---------------- AbsencesPeter 12 James 10 The Feb sheet is the same (different numbers) This is what I have in my summary sheet (Sheet1). The word `Summary` is in A1 The words `Jan`and `Feb`are in B3 and B4 The names are in A4, A5 .... The formula in B4 is =INDIRECT(B$3&"!B"&ROW()-1) This is copied down and across to pickup the other values ------------------ Summary Jan Feb Peter 12 3 James 10 4 I did not use a lookup since the names in all sheets are in the same order but you could alter the formula to use VLOOKUP best wishes "merlin" wrote in message ... Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
the evaluator says we get as far as INDIRECT("JULY!") and then the next step
causes the error. "merlin" wrote in message ... invalid cell reference error. "Sean Timmons" wrote in message ... You did do the right thing there. Sorry for messign that up on you. Click the fx to the left of the formula bar where you have the =INIDRECT formula. What is it showing as the values? "merlin" wrote: ok - this is the actual formula I'm using: =INDIRECT(D$2&"!"&$AI4) where D2$ is the heading at the top of the column on the summary sheet (e.g. contains text 'JAN') and $AI4 is the cell on each sheet I need to total. I was getting a formula error but I tinkered and added a " which seemed to fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to the text on the sheet tab for JAN. any thoughts? ----- Original Message ----- From: "Sean Timmons" Newsgroups: microsoft.public.excel.newusers Sent: Tuesday, May 26, 2009 1:38 PM Subject: referencing another sheet and using auto fill I believe the OP wants to see individual month numbers as well. The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
cracked it!
the ROW function was the last piece of the puzzle for me. Thanks everyone! "Bernard Liengme" wrote in message ... In the sheet called Jan I have the following with `Absences` in A1, the names in A3, A4...., and the numbers are in column B ---------------- AbsencesPeter 12 James 10 The Feb sheet is the same (different numbers) This is what I have in my summary sheet (Sheet1). The word `Summary` is in A1 The words `Jan`and `Feb`are in B3 and B4 The names are in A4, A5 .... The formula in B4 is =INDIRECT(B$3&"!B"&ROW()-1) This is copied down and across to pickup the other values ------------------ Summary Jan Feb Peter 12 3 James 10 4 I did not use a lookup since the names in all sheets are in the same order but you could alter the formula to use VLOOKUP best wishes "merlin" wrote in message ... Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
What do you have in cell AI4?
"merlin" wrote: the evaluator says we get as far as INDIRECT("JULY!") and then the next step causes the error. "merlin" wrote in message ... invalid cell reference error. "Sean Timmons" wrote in message ... You did do the right thing there. Sorry for messign that up on you. Click the fx to the left of the formula bar where you have the =INIDRECT formula. What is it showing as the values? "merlin" wrote: ok - this is the actual formula I'm using: =INDIRECT(D$2&"!"&$AI4) where D2$ is the heading at the top of the column on the summary sheet (e.g. contains text 'JAN') and $AI4 is the cell on each sheet I need to total. I was getting a formula error but I tinkered and added a " which seemed to fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to the text on the sheet tab for JAN. any thoughts? ----- Original Message ----- From: "Sean Timmons" Newsgroups: microsoft.public.excel.newusers Sent: Tuesday, May 26, 2009 1:38 PM Subject: referencing another sheet and using auto fill I believe the OP wants to see individual month numbers as well. The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
referencing another sheet and using auto fill
Wait.. stop.. You want it to be =INDIRECT(D$2&"!$AI4")
INDIRECT causes those values outside the quotes to be changed to the cell value ("July"), and those within the " to stay as is.. =July!$A14 sorry again!! "merlin" wrote: the evaluator says we get as far as INDIRECT("JULY!") and then the next step causes the error. "merlin" wrote in message ... invalid cell reference error. "Sean Timmons" wrote in message ... You did do the right thing there. Sorry for messign that up on you. Click the fx to the left of the formula bar where you have the =INIDRECT formula. What is it showing as the values? "merlin" wrote: ok - this is the actual formula I'm using: =INDIRECT(D$2&"!"&$AI4) where D2$ is the heading at the top of the column on the summary sheet (e.g. contains text 'JAN') and $AI4 is the cell on each sheet I need to total. I was getting a formula error but I tinkered and added a " which seemed to fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to the text on the sheet tab for JAN. any thoughts? ----- Original Message ----- From: "Sean Timmons" Newsgroups: microsoft.public.excel.newusers Sent: Tuesday, May 26, 2009 1:38 PM Subject: referencing another sheet and using auto fill I believe the OP wants to see individual month numbers as well. The first question is, where on the other sheets is the data we're pulling? Let's assume the data is in the same place for every sheet, cells B2 through D2 for the below example, and in the same order: Peter, John, then Jane. Please ensure your month names on the summary tab exactly match the names of the tabs in your workbook. in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and paste across and down. "Eduardo" wrote: Hi, Assuming that all the sheets has the same format in the summary one go to the first cell and enter the formula as follow =SUM(Sheet2:Sheet3!A1) Replace Sheet2 with the name of the first sheet in your workbook in your case maybe January Replace sheet3 with the last tab in your workbook Replace A1 with the cell where you want to pull the information then copy the formula If this helps please click yes, thanks "merlin" wrote: Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 12 jane's leave totals for month and year: 4 0 0 4 my sheets are labelled "jan" "feb" etc and what I'm trying to do is use a formula which allows me to drag and autofill across and down on the summary sheet to avoid clicking all the workbook for everyone of 12x84 cells by using the labels at the top of each column in the summary sheet. I've considered INDIRECT and ADDRESS but I don't really understand how to apply them. Please could someone help? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-fill a sheet reference | Excel Worksheet Functions | |||
Auto fill Certain data from Sheet 1 to Sheet 2 | Excel Worksheet Functions | |||
Auto Fill Different Sheets same cell on each sheet? | Excel Discussion (Misc queries) | |||
Data referencing and auto fill across worksheets in one workbook.. | Excel Worksheet Functions | |||
auto fill from another sheet in excel | Excel Worksheet Functions |