Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
I have a workbook that has 20 or so different worksheets, one per department.
Each worksheet contains the department budgets. I need to consolidate the department budgets onto one worksheet so I can upload it into our ledger. For example lets say there are 5 accounts listed on each worksheet. When I consolidate the data onto one worksheet I would then have 100 rows (5 accounts/worksheet x 20 departments) of data. Is there a formula that will allow me to change one cell, which would have a worksheet name in it, and it will then reference that departments data? Is there a function that will facilitate this process? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
Check the help of the INDIRECT() formula, it may be what you are looking for.
"QC Coug" wrote: I have a workbook that has 20 or so different worksheets, one per department. Each worksheet contains the department budgets. I need to consolidate the department budgets onto one worksheet so I can upload it into our ledger. For example lets say there are 5 accounts listed on each worksheet. When I consolidate the data onto one worksheet I would then have 100 rows (5 accounts/worksheet x 20 departments) of data. Is there a formula that will allow me to change one cell, which would have a worksheet name in it, and it will then reference that departments data? Is there a function that will facilitate this process? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
Look in HELP for the INDIRECT() function.
But maybe you should consider redesigning the structure of your workbook and have just one sheet, with the department as an identifier. 20 Sheets with identical layouts look like a maintenance nightmare to me... -- Kind regards, Niek Otten "QC Coug" wrote in message ... |I have a workbook that has 20 or so different worksheets, one per department. | Each worksheet contains the department budgets. I need to consolidate the | department budgets onto one worksheet so I can upload it into our ledger. | For example lets say there are 5 accounts listed on each worksheet. When I | consolidate the data onto one worksheet I would then have 100 rows (5 | accounts/worksheet x 20 departments) of data. | | Is there a formula that will allow me to change one cell, which would have a | worksheet name in it, and it will then reference that departments data? | | Is there a function that will facilitate this process? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
I agree it is a nightmare. I have adopted this and need to get through the
next couple of months until I can redeign our budget tool. I will be doing exactly what you mentioned. Thanks for the tip. "Niek Otten" wrote: Look in HELP for the INDIRECT() function. But maybe you should consider redesigning the structure of your workbook and have just one sheet, with the department as an identifier. 20 Sheets with identical layouts look like a maintenance nightmare to me... -- Kind regards, Niek Otten "QC Coug" wrote in message ... |I have a workbook that has 20 or so different worksheets, one per department. | Each worksheet contains the department budgets. I need to consolidate the | department budgets onto one worksheet so I can upload it into our ledger. | For example lets say there are 5 accounts listed on each worksheet. When I | consolidate the data onto one worksheet I would then have 100 rows (5 | accounts/worksheet x 20 departments) of data. | | Is there a formula that will allow me to change one cell, which would have a | worksheet name in it, and it will then reference that departments data? | | Is there a function that will facilitate this process? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
I can't figure out how to make this work for what I am trying to do. In the
example I'm playing with I have 3 worksheets, Sheet1, Sheet2, and Sheet3. Each of these sheets has a different value in cell A1. On a 4th worksheet I am inputing the name of one of the worksheets (Sheet1, Sheet2, or Sheet3) and then when I want to be able to create a formula that will pull the data out of cell A1 from the worksheet I specify. "Miguel Zapico" wrote: Check the help of the INDIRECT() formula, it may be what you are looking for. "QC Coug" wrote: I have a workbook that has 20 or so different worksheets, one per department. Each worksheet contains the department budgets. I need to consolidate the department budgets onto one worksheet so I can upload it into our ledger. For example lets say there are 5 accounts listed on each worksheet. When I consolidate the data onto one worksheet I would then have 100 rows (5 accounts/worksheet x 20 departments) of data. Is there a formula that will allow me to change one cell, which would have a worksheet name in it, and it will then reference that departments data? Is there a function that will facilitate this process? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
Try this: =INDIRECT(B5&"!A1") The B5 is the cell containing the sheet name you specify So if you type Sheet2 in B5 you will get the value from Sheet2!A1 -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=532764 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
Hi,
Can you please elaborate more on what you suggested below. How do you design one sheet with the department as an identifier? Is there an example I can see? Thanks. "Niek Otten" wrote: Look in HELP for the INDIRECT() function. But maybe you should consider redesigning the structure of your workbook and have just one sheet, with the department as an identifier. 20 Sheets with identical layouts look like a maintenance nightmare to me... -- Kind regards, Niek Otten "QC Coug" wrote in message ... |I have a workbook that has 20 or so different worksheets, one per department. | Each worksheet contains the department budgets. I need to consolidate the | department budgets onto one worksheet so I can upload it into our ledger. | For example lets say there are 5 accounts listed on each worksheet. When I | consolidate the data onto one worksheet I would then have 100 rows (5 | accounts/worksheet x 20 departments) of data. | | Is there a formula that will allow me to change one cell, which would have a | worksheet name in it, and it will then reference that departments data? | | Is there a function that will facilitate this process? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I NEED A FLEXIBLE FORMULA
To make it simpler for your users (or yourself) use this: =INDIRECT("Sheet"&B5&"!A1") if you haven't given the sheets custom names With this formula you just need to type in the sheet number in cell B5 -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=532764 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |