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
|
|||
|
|||
using worksheet references in formulas
I'm referencing data from one worksheet in a formula: =SUM(IF('1'!F5:'1'!F16 = 6,IF('1'!G5:'1'!G16 ="YES", 1,0),0)) I'd like to reuse this formula for additional worksheets that will be added over time without having to manually type in the worksheet reference '1'! Is there a way to substitute entries in a column for the hard coded worksheet reference '1'! ? Here's an example of what I'm trying to accomplish Column A contains the references to all worksheets Column B contains the results of the formula A B 1 formula referencing sheet 1 derived from cell A1 2 formula referencing sheet 1 derived from cell A2 3 formula referencing sheet 1 derived from cell A3 4 formula referencing sheet 1 derived from cell A4 A second question I have: Is there a way to apply the worksheet reference to the formula itself so that I don't have to apply it to each cell in the range. i.e. is there a way to achieve something along the line of this: ='1'!SUM(IF(F5:F16 = 6,IF(G5:G16 ="YES", 1,0),0)) thanks for your help with this. -- -sb ------------------------------------------------------------------------ -sb's Profile: http://www.excelforum.com/member.php...o&userid=36861 View this thread: http://www.excelforum.com/showthread...hreadid=565731 |
#2
|
|||
|
|||
using worksheet references in formulas
=SUM(((INDIRECT("'"&A1&"'!F5:F16")=6)*(INDIRECT("' "&A1&"'!G5:G16")="YES")))
still an array formula -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "-sb" wrote in message ... I'm referencing data from one worksheet in a formula: =SUM(IF('1'!F5:'1'!F16 = 6,IF('1'!G5:'1'!G16 ="YES", 1,0),0)) I'd like to reuse this formula for additional worksheets that will be added over time without having to manually type in the worksheet reference '1'! Is there a way to substitute entries in a column for the hard coded worksheet reference '1'! ? Here's an example of what I'm trying to accomplish Column A contains the references to all worksheets Column B contains the results of the formula A B 1 formula referencing sheet 1 derived from cell A1 2 formula referencing sheet 1 derived from cell A2 3 formula referencing sheet 1 derived from cell A3 4 formula referencing sheet 1 derived from cell A4 A second question I have: Is there a way to apply the worksheet reference to the formula itself so that I don't have to apply it to each cell in the range. i.e. is there a way to achieve something along the line of this: ='1'!SUM(IF(F5:F16 = 6,IF(G5:G16 ="YES", 1,0),0)) thanks for your help with this. -- -sb ------------------------------------------------------------------------ -sb's Profile: http://www.excelforum.com/member.php...o&userid=36861 View this thread: http://www.excelforum.com/showthread...hreadid=565731 |
#3
|
|||
|
|||
using worksheet references in formulas
Bob, I have a little bit of learning to do regarding how to use the Indirect function, but I did verify that it addresses my needs. thanks. Much appreciated ! -sb -- -sb ------------------------------------------------------------------------ -sb's Profile: http://www.excelforum.com/member.php...o&userid=36861 View this thread: http://www.excelforum.com/showthread...hreadid=565731 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
autofill worksheet references? | nihad | Worksheet Functions | 1 | May 3rd, 2006 09:13 PM |
copy/Paste references other worksheet | Ivano | Worksheet Functions | 4 | April 1st, 2006 01:06 AM |
How do I protect formulas in a worksheet and allow data entry | Gail | Worksheet Functions | 2 | October 8th, 2005 02:23 AM |
Formulas and worksheet linking | amandaa | Worksheet Functions | 1 | September 6th, 2005 12:36 PM |
Printing worksheet (showing formulas instead) | [email protected] | Worksheet Functions | 1 | October 17th, 2003 12:50 PM |