If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple worksheets & duplicating formula
I have 35 worksheets; one w/s for each volunteer Each w/s has the exact
document to capture the number of hours a volunteers works and the type of service they perform (ex: office work, patient care). I created a work sheet listing the name of each vol (running vertically) and a row of cells (horizontially) to capture the total for each month for each vol. How do I change the formula to between w/s without having to do the = sign for each cell from all worksheets? I'm using Excel 2003 Thank you for saving my mind from exploding! Jackie |
#2
|
|||
|
|||
Multiple worksheets & duplicating formula
Experiment with the Indirect function, you will be able to reference
another worksheet by what ever worksheet name in in a cell Here is a very simple Sample http://sites.google.com/site/davesex...attredirects=0 More on Indirect Functions can be found here... http://www.contextures.com/xlFunctions05.html#RefSheet |
#3
|
|||
|
|||
Multiple worksheets & duplicating formula
Use Indirect as suggested combined with an *array formula* if your layouts are consistent e.g. =INDIRECT(A30 & "!B$10:P$10") where A30 contains the Sheet Name and B10:P10 on each sheet contains the required data -- mdmackillop ------------------------------------------------------------------------ mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64285 |
#4
|
|||
|
|||
Multiple worksheets & duplicating formula
If the structure of your sheets are same (the columns, rows may have
different size), another solution for getting a summary sheet of all vol. is to use consolidation under data menu. Consolidate will summarize the data from different sources to one. -- R. Khoshravan Please click "Yes" if it is helpful. "Jackie" wrote: I have 35 worksheets; one w/s for each volunteer Each w/s has the exact document to capture the number of hours a volunteers works and the type of service they perform (ex: office work, patient care). I created a work sheet listing the name of each vol (running vertically) and a row of cells (horizontially) to capture the total for each month for each vol. How do I change the formula to between w/s without having to do the = sign for each cell from all worksheets? I'm using Excel 2003 Thank you for saving my mind from exploding! Jackie |
#5
|
|||
|
|||
Multiple worksheets & duplicating formula
You are my HERO. Thank you!
Jackie "CurlyDave" wrote: Experiment with the Indirect function, you will be able to reference another worksheet by what ever worksheet name in in a cell Here is a very simple Sample http://sites.google.com/site/davesex...attredirects=0 More on Indirect Functions can be found here... http://www.contextures.com/xlFunctions05.html#RefSheet |
#6
|
|||
|
|||
Multiple worksheets & duplicating formula
Thank You for saving my brain from emploding!
Jackie "mdmackillop" wrote: Use Indirect as suggested combined with an *array formula* if your layouts are consistent e.g. =INDIRECT(A30 & "!B$10:P$10") where A30 contains the Sheet Name and B10:P10 on each sheet contains the required data -- mdmackillop ------------------------------------------------------------------------ mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64285 |
#7
|
|||
|
|||
Multiple worksheets & duplicating formula
All my worksheets; each tab having the individual name of a volunteer has the
exact same format and cell reference. I looked at the data - consolidate option and I would greatly appreciate it if you could provide a simple sample of how to feed all the information from the 35 worksheets to the single worksheet that captures the total hours for each month. The INDIRECT formula has saved me many hours...not having to enter "=" and clicking on each worksheets specific cell I want information from. I'd love to learn of another option. Thank you so much for your interest and time. Jackie "Khoshravan" wrote: If the structure of your sheets are same (the columns, rows may have different size), another solution for getting a summary sheet of all vol. is to use consolidation under data menu. Consolidate will summarize the data from different sources to one. -- R. Khoshravan Please click "Yes" if it is helpful. "Jackie" wrote: I have 35 worksheets; one w/s for each volunteer Each w/s has the exact document to capture the number of hours a volunteers works and the type of service they perform (ex: office work, patient care). I created a work sheet listing the name of each vol (running vertically) and a row of cells (horizontially) to capture the total for each month for each vol. How do I change the formula to between w/s without having to do the = sign for each cell from all worksheets? I'm using Excel 2003 Thank you for saving my mind from exploding! Jackie |
#8
|
|||
|
|||
Multiple worksheets & duplicating formula
On Feb 14, 1:13*pm, Jackie wrote:
You are my HERO. *Thank you! * Jackie "CurlyDave" wrote: Experiment with the Indirect function, you will be able to reference another worksheet by what ever worksheet name in in a cell Here is a very simple Sample http://sites.google.com/site/davesex...ples/UsingIndi... More on Indirect Functions can be found here... http://www.contextures.com/xlFunctions05.html#RefSheet It's hard to describe, not knowing what the layout of your worksheet is. You can loop through the sheets with VBA to get the sheet names and values from all the other sheets. Check out this example, enable macros when you open the Workbook. http://sites.google.com/site/davesex...attredirects=0 |
Thread Tools | |
Display Modes | |
|
|