Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL.
Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
If you have something like:
=INDIRECT("'"&A1&"'!C2") (the apostrophes allow for having spaces in the sheet names) and A1 contains Tom then this will return the value from Tom!C2. However, if A1 is empty then it will return #REF. So, you will need to check for this, like: =IF(A1="",0,INDIRECT("'"&A1&"'!C2")) So, you will need to build up your composite formula like this: =IF(......) + IF(......) + IF(.....) and so on, where each IF term is looking at a different sheet. Hope this helps. Pete On Nov 11, 9:59*am, Paul Moles wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT*(A5)!f18+INDIRECT(A6)!g1 0. And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I *appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank.. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
Just test if you little table has a blank:
=IF(A1="",0,INDIRECT("Sheet1!C1"))+IF(A2="",0,INDI RECT("Sheet2!C1"))+IF(A3="",0,INDIRECT("Sheet3!C1" )) Just change the names in the indirect to match the names in the table. You can also adjust the cell addresses. -- Gary''s Student - gsnu200908 "Paul Moles" wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
Hi Paul
With data arranged in the below way in ColA in Total Sheet; try this Col A TOM DICK HARRY PETER BILL JOHN --Make sure the sheet names do not have a space after or before the entry --Check whether the cell references for the names are in the order C2,B20,D8,M19,F18,G10. If not correct the formula --The formula sheet is named as 'Total' and I assume Total!Z65536 do not have any entries. 'The contribution piece ="Contribution by " & TRIM(A1&" " & A2& " " & A3&" " & A4& " " & A5& " " & A6) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Total!Z65536" )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Paul Moles" wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
Since you are using this formula in Total the sheet reference to 'Total' is
not needed. =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Z65536")),"< 0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Paul With data arranged in the below way in ColA in Total Sheet; try this Col A TOM DICK HARRY PETER BILL JOHN --Make sure the sheet names do not have a space after or before the entry --Check whether the cell references for the names are in the order C2,B20,D8,M19,F18,G10. If not correct the formula --The formula sheet is named as 'Total' and I assume Total!Z65536 do not have any entries. 'The contribution piece ="Contribution by " & TRIM(A1&" " & A2& " " & A3&" " & A4& " " & A5& " " & A6) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Total!Z65536" )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Paul Moles" wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
Had a look at it again..You can avoid referring to an odd cell.
'Again array entered...Use Ctrl+Shift+Enter to apply the formula. =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"}, CELL("address",A1:A6) )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Since you are using this formula in Total the sheet reference to 'Total' is not needed. =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Z65536")),"< 0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Paul With data arranged in the below way in ColA in Total Sheet; try this Col A TOM DICK HARRY PETER BILL JOHN --Make sure the sheet names do not have a space after or before the entry --Check whether the cell references for the names are in the order C2,B20,D8,M19,F18,G10. If not correct the formula --The formula sheet is named as 'Total' and I assume Total!Z65536 do not have any entries. 'The contribution piece ="Contribution by " & TRIM(A1&" " & A2& " " & A3&" " & A4& " " & A5& " " & A6) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Total!Z65536" )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Paul Moles" wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
Don't think I made myself quite clear,
I can see how this checks if the referred cell is blank but not how it then gets the Sheet Name (TOM DICK or HARRY etc) into the formula if it is not blank. I expected the function to be able to use sheet names INDIRECTLY, from the list in cells A1, A2, A3 etc of the Total sheet to generate the completed formula, with the cell reference from that sheet. Effectively =indirect("TOTAL!a1"))c19 is equivalent to =+Tom!C19. Your IF statement allows for cell TOTAL A1 to be blank but where am I going wrong with the INDIRECT formula? Appreciate the help Thanks Paul "Gary''s Student" wrote: Just test if you little table has a blank: =IF(A1="",0,INDIRECT("Sheet1!C1"))+IF(A2="",0,INDI RECT("Sheet2!C1"))+IF(A3="",0,INDIRECT("Sheet3!C1" )) Just change the names in the indirect to match the names in the table. You can also adjust the cell addresses. -- Gary''s Student - gsnu200908 "Paul Moles" wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
With the sheet names and cell references in ColA andCol B as below you can try
Col A Col B TOM C2 DICK B20 HARRY D8 PETER M19 BILL F18 JOHN G10 (array formula: entered using Ctrl+Shift+Enter) =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"","'"&A1:A6& "'!" & B1:B6, CELL("address",A1:A6) )),"<0")) Thanks Pete for reminding. I thought I have put the single quote but that was for the above version which I tried earlier (using Col B for cell references). The original formula modified to handle sheet names with spaces. (array formula: entered using Ctrl+Shift+Enter) =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"","'" & A1:A6&"'!"& {"C2","B20","D8","M19","F18","G10"}, CELL("address",A1:A6) )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Had a look at it again..You can avoid referring to an odd cell. 'Again array entered...Use Ctrl+Shift+Enter to apply the formula. =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"}, CELL("address",A1:A6) )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Since you are using this formula in Total the sheet reference to 'Total' is not needed. =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Z65536")),"< 0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Paul With data arranged in the below way in ColA in Total Sheet; try this Col A TOM DICK HARRY PETER BILL JOHN --Make sure the sheet names do not have a space after or before the entry --Check whether the cell references for the names are in the order C2,B20,D8,M19,F18,G10. If not correct the formula --The formula sheet is named as 'Total' and I assume Total!Z65536 do not have any entries. 'The contribution piece ="Contribution by " & TRIM(A1&" " & A2& " " & A3&" " & A4& " " & A5& " " & A6) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(SUMIF(INDIRECT(IF(A1:A6<"",A1:A6&"!"& {"C2","B20","D8","M19","F18","G10"},"Total!Z65536" )),"<0")) If this post helps click Yes --------------- Jacob Skaria "Paul Moles" wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT(A5)!f18+INDIRECT(A6)!g10 . And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
Excellent Thank You
Does just what I wanted Paul "Pete_UK" wrote: If you have something like: =INDIRECT("'"&A1&"'!C2") (the apostrophes allow for having spaces in the sheet names) and A1 contains Tom then this will return the value from Tom!C2. However, if A1 is empty then it will return #REF. So, you will need to check for this, like: =IF(A1="",0,INDIRECT("'"&A1&"'!C2")) So, you will need to build up your composite formula like this: =IF(......) + IF(......) + IF(.....) and so on, where each IF term is looking at a different sheet. Hope this helps. Pete On Nov 11, 9:59 am, Paul Moles wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECTÂ*(A5)!f18+INDIRECT(A6)!g 10. And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank.. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles . |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT Function
You're welcome, Paul - thanks for feeding back.
Pete On Nov 11, 2:55*pm, Paul Moles wrote: Excellent Thank You Does just what I wanted Paul "Pete_UK" wrote: If you have something like: =INDIRECT("'"&A1&"'!C2") (the apostrophes allow for having spaces in the sheet names) and A1 contains Tom then this will return the value from Tom!C2. However, if A1 is empty then it will return #REF. So, you will need to check for this, like: =IF(A1="",0,INDIRECT("'"&A1&"'!C2")) So, you will need to build up your composite formula like this: =IF(......) + IF(......) + IF(.....) and so on, where each IF term is looking at a different sheet. Hope this helps. Pete On Nov 11, 9:59 am, Paul Moles wrote: I have a workbook with 7 sheets, TOM DICK HARRY PETER BILL JOHN TOTAL.. Simple point and click calculation provides =+Tom!C2+Dick!B20+Harry!D8+Peter!M19+Bill!F18+John !G10, however this allows no flexibility to add in only part of the team. eg combined results for DICK PETER JOHN I had expected to be able to write =INDIRECT(A1)!c2+INDIRECT(A2)!b20+INDIRECT(A3)!d8+ INDIRECT(A4)!m19+INDIRECT**(A5)!f18+INDIRECT(A6)!g 10. And then by temporarily deleting the unwanted names from the listing in A1 - A6, provide a revised Total. NB: As each sheet is different I *appreciate the names need to be in the correct order A1 - A6 for the cells to be correctly referenced. Formula ="Contribution by "&A1&" " &A2&" " &A3&" " &A4&" " &A5&" " &A6 nicely provides a heading for the revised report but I am struggling with the INDIRECT function and does INDIRECT allow for some references to be blank.. A2 has been temporarily deleted. I have tried the Help file but it doesn't seem to allow for a scenario using sheet names. Any help appreciated. Many Thanks Paul Moles .- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
Using SUM with INDIRECT function | Excel Worksheet Functions | |||
indirect function | Excel Discussion (Misc queries) | |||
using the INDIRECT function | Excel Discussion (Misc queries) | |||
INDIRECT function inside AND function | Excel Worksheet Functions |