| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarise Multiple Worksheets
I have created a sheet and duplicated this sheet on multiple sheets as it is
a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
Try this in A1:
=INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33*am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
Hi Pete,
Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
Try it this way, then:
=INDIRECT("Sheet"&ROW(A1)&"!C5") ROW(A1) will return 1, so the text in the bracket will evaluate to Sheet1!C5 and the INDIRECT function will bring the data from that calculated cell. When the formula is copied down it will change to ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in the next row it will be Sheet3!C5, and so on. This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc - the #REF error implies that they are not. Hope this helps. Pete On Oct 3, 10:36*am, Louise wrote: Hi Pete, Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
I should have pointed out that you put the formula:
=INDIRECT("Sheet"&ROW(A1)&"!C5") in cell A10, and then copy down. Pete On Oct 3, 10:55*am, Pete_UK wrote: Try it this way, then: =INDIRECT("Sheet"&ROW(A1)&"!C5") ROW(A1) will return 1, so the text in the bracket will evaluate to Sheet1!C5 and the INDIRECT function will bring the data from that calculated cell. When the formula is copied down it will change to ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in the next row it will be Sheet3!C5, and so on. This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc - the #REF error implies that they are not. Hope this helps. Pete On Oct 3, 10:36*am, Louise wrote: Hi Pete, Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
Hi Pete,
That's my problem, my sheets are not named Sheet1, Sheet2 i actually name it RFP001, RFP002 for my own reference. Is it possible to do so without the sheets being named Sheet1, Sheet2? Please tell me it is its a long process to do them individually? I appreciate you help! Louise "Pete_UK" wrote: Try it this way, then: =INDIRECT("Sheet"&ROW(A1)&"!C5") ROW(A1) will return 1, so the text in the bracket will evaluate to Sheet1!C5 and the INDIRECT function will bring the data from that calculated cell. When the formula is copied down it will change to ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in the next row it will be Sheet3!C5, and so on. This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc - the #REF error implies that they are not. Hope this helps. Pete On Oct 3, 10:36 am, Louise wrote: Hi Pete, Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
Well as long as you have them sequentially numbered you can do it -
amend the formula to: =INDIRECT("RFP"&TEXT(ROW(A1),"000")&"!C5") Put this in A10 and then copy down as required - it will bring the values from C5 in sheets RFP001, RFP002, RFP003 etc. on consecutive rows in your summary sheet. Hope this helps. Pete On Oct 3, 11:37*am, Louise wrote: Hi Pete, That's my problem, my sheets are not named Sheet1, Sheet2 i actually name it RFP001, RFP002 for my own reference. Is it possible to do so without the sheets being named Sheet1, Sheet2? Please tell me it is its a long process to do them individually? I appreciate you help! Louise "Pete_UK" wrote: Try it this way, then: =INDIRECT("Sheet"&ROW(A1)&"!C5") ROW(A1) will return 1, so the text in the bracket will evaluate to Sheet1!C5 and the INDIRECT function will bring the data from that calculated cell. When the formula is copied down it will change to ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in the next row it will be Sheet3!C5, and so on. This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc - the #REF error implies that they are not. Hope this helps. Pete On Oct 3, 10:36 am, Louise wrote: Hi Pete, Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
Hey Pete
THANK YOU SOOOOO MUCH, it works. You have saved me alot of time exept i should have asked earlier i did one spreadsheet manually . .hehehe . silly me Seeing that you seem to be quite the spreadsheet expert is it possible to rename worksheets as i do RFP001, RFP002 all at the same time? Appreciate your help! Louise "Pete_UK" wrote: Well as long as you have them sequentially numbered you can do it - amend the formula to: =INDIRECT("RFP"&TEXT(ROW(A1),"000")&"!C5") Put this in A10 and then copy down as required - it will bring the values from C5 in sheets RFP001, RFP002, RFP003 etc. on consecutive rows in your summary sheet. Hope this helps. Pete On Oct 3, 11:37 am, Louise wrote: Hi Pete, That's my problem, my sheets are not named Sheet1, Sheet2 i actually name it RFP001, RFP002 for my own reference. Is it possible to do so without the sheets being named Sheet1, Sheet2? Please tell me it is its a long process to do them individually? I appreciate you help! Louise "Pete_UK" wrote: Try it this way, then: =INDIRECT("Sheet"&ROW(A1)&"!C5") ROW(A1) will return 1, so the text in the bracket will evaluate to Sheet1!C5 and the INDIRECT function will bring the data from that calculated cell. When the formula is copied down it will change to ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in the next row it will be Sheet3!C5, and so on. This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc - the #REF error implies that they are not. Hope this helps. Pete On Oct 3, 10:36 am, Louise wrote: Hi Pete, Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summarise Multiple Worksheets
You're welcome, Louise - thanks for feeding back.
There are many others more expert than me who contribute to these newsgroups !! There is not a formula that could be used to help you to rename the worksheets - you would need a macro to do that. Pete On Oct 3, 12:31*pm, Louise wrote: Hey Pete THANK YOU SOOOOO MUCH, it works. You have saved me alot of time exept i should have asked earlier i did one spreadsheet manually . .hehehe *. *silly me Seeing that you seem to be quite the spreadsheet expert is it possible to rename worksheets as i do RFP001, RFP002 all at the same time? Appreciate your help! Louise "Pete_UK" wrote: Well as long as you have them sequentially numbered you can do it - amend the formula to: =INDIRECT("RFP"&TEXT(ROW(A1),"000")&"!C5") Put this in A10 and then copy down as required - it will bring the values from C5 in sheets RFP001, RFP002, RFP003 etc. on consecutive rows in your summary sheet. Hope this helps. Pete On Oct 3, 11:37 am, Louise wrote: Hi Pete, That's my problem, my sheets are not named Sheet1, Sheet2 i actually name it RFP001, RFP002 for my own reference. Is it possible to do so without the sheets being named Sheet1, Sheet2? Please tell me it is its a long process to do them individually? I appreciate you help! Louise "Pete_UK" wrote: Try it this way, then: =INDIRECT("Sheet"&ROW(A1)&"!C5") ROW(A1) will return 1, so the text in the bracket will evaluate to Sheet1!C5 and the INDIRECT function will bring the data from that calculated cell. When the formula is copied down it will change to ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in the next row it will be Sheet3!C5, and so on. This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc - the #REF error implies that they are not. Hope this helps. Pete On Oct 3, 10:36 am, Louise wrote: Hi Pete, Thanx so much for that but it does not seem to work. It gives me #REF error. I checked to see the explanation for the INDIRECT formula but honestly do not understand what they are saying. I should mention that the cell i want from Sheet 1 will not be in the same cell on the Summary sheet. I was just using an example but in actual fact it will be A10=Sheet1!C5, maybe this is why this formula does not work. Can you help? Louise "Pete_UK" wrote: Try this in A1: =INDIRECT("Sheet"&ROW(A1)&"!A1") then you can copy this down as required. Hope this helps. Pete On Oct 3, 9:33 am, Louise wrote: I have created a sheet and duplicated this sheet on multiple sheets as it is a standard form that needs to be completed for payment requests. I then created another sheet in the same spreadsheet where i want to summarize all these individual sheets, meaning i want to extract exactly the same information from each sheet into the summary sheet. I have set up the first line by saying for instance A1=Sheet1A1 now how do i copy this formula down so that the next formula is A2=Sheet2A1 and so forth. This must be possible its such a simple thing but i cannot seems to find it on the help menu. Anybody out there that can help. Thanx Louise- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summarise multiple sheets | Excel Discussion (Misc queries) | |||
| create sheet to summarise invoices on other worksheets | Excel Discussion (Misc queries) | |||
| How to summarise data in the same place in multiple worksheets? | Excel Discussion (Misc queries) | |||
| summarise totals from many worksheets into one final worksheet | Excel Worksheet Functions | |||
| Summarise data on multiple sheets | Excel Worksheet Functions | |||
Linear Mode
