Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
Does anyone know how to reference data on multiple worksheets or all of the worksheets in the same workbook? I know you can reference all data in a given column by using A:A for example. How can you do the same thing with worksheets? -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
enter the formula as you normally would, and when you have to reference
another worksheet, simply select that worksheet, navigate to the cell, select it, and then go back to your formula. -- Brevity is the soul of wit. "ChrisPrather" wrote: Does anyone know how to reference data on multiple worksheets or all of the worksheets in the same workbook? I know you can reference all data in a given column by using A:A for example. How can you do the same thing with worksheets? -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
=IF(ISNUMBER(MATCH(A2,sheet2!A:A!sheet3!A:A,0)),"S ame","New To The Report") I don't think you can simply click on the worksheet and proceed as you indicated. When I do that (not the results above), the formula becomes invalid due to a syntax error. So, how do you correctly reference multiple worksheets in the same formula without causing a syntax error? -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
*EXACTLY* how many sheets do want to include in this?
What are some of their names? Are you *REALLY* using the default sheet names, Sheet2,Sheet3 ? do you *REALLY* need to reference the entire column, sheet2!A:A ? You'll more than likely need a completely different formula. Biff "ChrisPrather" wrote in message news:ChrisPrather.2cwllt_1156186513.3879@excelforu m-nospam.com... =IF(ISNUMBER(MATCH(A2,sheet2!A:A!sheet3!A:A,0)),"S ame","New To The Report") I don't think you can simply click on the worksheet and proceed as you indicated. When I do that (not the results above), the formula becomes invalid due to a syntax error. So, how do you correctly reference multiple worksheets in the same formula without causing a syntax error? -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
There could be as few as three worksheets or as many as I feel necessary. It is completely possible that another function may work better and I wouldn't know because I'm pretty new at this. Is there something wrong with the sheet1, sheet2, etc.. reference names? I know they are vanilla, but I don't really mind at this point. If I could figure out how to reference more than 1 worksheet in a formula, I would probably change the name of the worksheets to reflect the date of the data. = ) I reference the entire A column because sometimes there may be more or less items in that column and I don't really want to have to change the range each time. Seaching A:A covers any number of items and empty cells are ignored so it seems to work. I would totally agree that I may be using a less than efficient formula due to not knowing better. I really appreciate your help though Biff. -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
Ok......
Make a list of the sheet names that you want to include in the formula: H1 = Sheet2 H2 = Sheet3 H3 = Sheet4 H4 = Sheet5 =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&H$1:H$4&"'!A:A "),A1)),"Same","New to report") Is there something wrong with the sheet1, sheet2, etc..reference names? No, not at all! Sometimes you can "build" the references to sheets that have a sequential naming convention without having to dedicate a range to list them. (but I wouldn't do it in this case) I reference the entire A column because sometimes there may be more or less items in that column Ok, that's fine. But sometimes you might have to make your file as efficient as possible and referencing large unused portions can cause things to slow down. Biff "ChrisPrather" wrote in message news:ChrisPrather.2cwsbi_1156195212.4755@excelforu m-nospam.com... There could be as few as three worksheets or as many as I feel necessary. It is completely possible that another function may work better and I wouldn't know because I'm pretty new at this. Is there something wrong with the sheet1, sheet2, etc.. reference names? I know they are vanilla, but I don't really mind at this point. If I could figure out how to reference more than 1 worksheet in a formula, I would probably change the name of the worksheets to reflect the date of the data. = ) I reference the entire A column because sometimes there may be more or less items in that column and I don't really want to have to change the range each time. Seaching A:A covers any number of items and empty cells are ignored so it seems to work. I would totally agree that I may be using a less than efficient formula due to not knowing better. I really appreciate your help though Biff. -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing data on multiple worksheets
Hi Chris,
What you're trying to do is called "3D Referencing". Keep in mind that Excel will only allow you to do this with certain functions. Look up "Refer to the same cell or range on multiple sheets" in the Excel 2003 help for a list of formulas that you can do this with (it's quite limited.) "Biff" wrote: Ok...... Make a list of the sheet names that you want to include in the formula: H1 = Sheet2 H2 = Sheet3 H3 = Sheet4 H4 = Sheet5 =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&H$1:H$4&"'!A:A "),A1)),"Same","New to report") Is there something wrong with the sheet1, sheet2, etc..reference names? No, not at all! Sometimes you can "build" the references to sheets that have a sequential naming convention without having to dedicate a range to list them. (but I wouldn't do it in this case) I reference the entire A column because sometimes there may be more or less items in that column Ok, that's fine. But sometimes you might have to make your file as efficient as possible and referencing large unused portions can cause things to slow down. Biff "ChrisPrather" wrote in message news:ChrisPrather.2cwsbi_1156195212.4755@excelforu m-nospam.com... There could be as few as three worksheets or as many as I feel necessary. It is completely possible that another function may work better and I wouldn't know because I'm pretty new at this. Is there something wrong with the sheet1, sheet2, etc.. reference names? I know they are vanilla, but I don't really mind at this point. If I could figure out how to reference more than 1 worksheet in a formula, I would probably change the name of the worksheets to reflect the date of the data. = ) I reference the entire A column because sometimes there may be more or less items in that column and I don't really want to have to change the range each time. Seaching A:A covers any number of items and empty cells are ignored so it seems to work. I would totally agree that I may be using a less than efficient formula due to not knowing better. I really appreciate your help though Biff. -- ChrisPrather ------------------------------------------------------------------------ ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743 View this thread: http://www.excelforum.com/showthread...hreadid=573836 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having a formula provide a running total from multiple worksheets | Excel Worksheet Functions | |||
How do i update multiple data ranges across multiple worksheets? | Excel Discussion (Misc queries) | |||
Merging data from Multiple Worksheets | Excel Worksheet Functions | |||
How to summarise data in the same place in multiple worksheets? | Excel Discussion (Misc queries) | |||
Putting data from multiple worksheets into one | Excel Discussion (Misc queries) |