| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the same cell, but in different worksheets?
Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
Create a helper column, say D1, D2, etc. that you will put all of your sheet
names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
Why not cycle thru the Worksheet collection? Then if sheets are added or
deleted, the code always works. Glenn "David Billigmeier" wrote in message ... Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
i guess i am also trying to link the names of each of the worksheets to a column in my master summary sheet. Basically, i am trying to make a template that updates the names of each of the sheets based on whatever the value is in the master sheets column. -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
This is exactly what I'm trying to do, but I'm missing something somewhere.
My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
What is your formula which works with the original sheet names?
"dd" wrote in message ... This is exactly what I'm trying to do, but I'm missing something somewhere. My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
Thanks for the response. I'm just getting a cell reference that contains a
sum of one of the columns in each worksheet. I want each of those totals in a worksheet where I can total all of them. I can manually reference each cell, but I was hoping there was a faster way to do it. "PCLIVE" wrote: What is your formula which works with the original sheet names? "dd" wrote in message ... This is exactly what I'm trying to do, but I'm missing something somewhere. My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
I'm not sure I understand your question, but some worksheet names need to be
surrounded by apostrophes when used as a reference: ='sheet 2'!a1 So maybe: =INDIRECT("'" & D1 & "'!B5") is what you're looking for. dd wrote: This is exactly what I'm trying to do, but I'm missing something somewhere. My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
Thank you for the help. I have a worksheet named Total. In cell C1, I want
the total from sheet Nov 1 am, cell D10. In cell C2, I want the total from sheet Nov 1 pm, cell D10. In cell 3, I want the total from sheet Nov 2 am, cell D10, etc. "Dave Peterson" wrote: I'm not sure I understand your question, but some worksheet names need to be surrounded by apostrophes when used as a reference: ='sheet 2'!a1 So maybe: =INDIRECT("'" & D1 & "'!B5") is what you're looking for. dd wrote: This is exactly what I'm trying to do, but I'm missing something somewhere. My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
Try this in C1:
=INDIRECT("'Nov "&ROWS($1:2)/2&" am'!D10") And try this in C2: =INDIRECT("'Nov "&ROWS($1:2)/2&" pm'!D10") Then, select *both* C1 and C2, and click on the fill handle of that 2 cell selection, and drag down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "dd" wrote in message ... Thank you for the help. I have a worksheet named Total. In cell C1, I want the total from sheet Nov 1 am, cell D10. In cell C2, I want the total from sheet Nov 1 pm, cell D10. In cell 3, I want the total from sheet Nov 2 am, cell D10, etc. "Dave Peterson" wrote: I'm not sure I understand your question, but some worksheet names need to be surrounded by apostrophes when used as a reference: ='sheet 2'!a1 So maybe: =INDIRECT("'" & D1 & "'!B5") is what you're looking for. dd wrote: This is exactly what I'm trying to do, but I'm missing something somewhere. My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I reference the same cell, but in different worksheets?
Thank you. That's way beyond anything I would ever figure out. What does it
mean? Can you translate it for me? I am getting an "Inconsistent Formula" warning on every other row, but it seems to be working fine. Thanks again. This is really helpful. "RagDyer" wrote: Try this in C1: =INDIRECT("'Nov "&ROWS($1:2)/2&" am'!D10") And try this in C2: =INDIRECT("'Nov "&ROWS($1:2)/2&" pm'!D10") Then, select *both* C1 and C2, and click on the fill handle of that 2 cell selection, and drag down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "dd" wrote in message ... Thank you for the help. I have a worksheet named Total. In cell C1, I want the total from sheet Nov 1 am, cell D10. In cell C2, I want the total from sheet Nov 1 pm, cell D10. In cell 3, I want the total from sheet Nov 2 am, cell D10, etc. "Dave Peterson" wrote: I'm not sure I understand your question, but some worksheet names need to be surrounded by apostrophes when used as a reference: ='sheet 2'!a1 So maybe: =INDIRECT("'" & D1 & "'!B5") is what you're looking for. dd wrote: This is exactly what I'm trying to do, but I'm missing something somewhere. My tab names are Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc. Assume I'm trying to reference cell D10 from each worksheet. What should my formula be? It worked for me as long as my tab names were Sheet1, Sheet2, Sheet3, etc.; but when I created a column and entered Nov 1 am, Nov 1 pm, Nov 2 am, Nov 2 pm, etc., I got the #REF error. Help, please. Thank you... "David Billigmeier" wrote: Create a helper column, say D1, D2, etc. that you will put all of your sheet names in. In E1, then, use this formula and copy down: =INDIRECT(D1&"!B5") Does that help? -- Regards, Dave "marcon" wrote: Hi, I am trying to reference a specific cell address (say B5) from each sheet in my workbook, and produce a column that contains B5's value from each sheet. Hopefully this is enough info for someone to correctly analyse the issue i am having. Thanks. Al -- marcon ------------------------------------------------------------------------ marcon's Profile: http://www.excelforum.com/member.php...o&userid=36992 View this thread: http://www.excelforum.com/showthread...hreadid=567156 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I anchor a cell reference? | Excel Discussion (Misc queries) | |||
| Reference Cell Color From Other WorkSheets | Excel Worksheet Functions | |||
| Cell reference in diff worksheets | Excel Worksheet Functions | |||
| Cell Reference locking | Excel Discussion (Misc queries) | |||
| how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
Linear Mode
