What wrong is it on using the indirect function?
Does anyone have any suggestions on what wrong it is on using the indirect
function? =SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2)), which work fine, I would like to change B$595:B$664 into a variable using indirect function, =SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2)) but it returns #VALUE, does anyone have any suggestions what wrong this statement is on using indirect function? Thanks in advance for any suggestions Eric Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER |
What wrong is it on using the indirect function?
Both the formulas returns the same result..if B594 is 70.
B$"&594+B$594 The above will refer to the B (594+70) which is 'B664' =SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2)) =SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2)) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Does anyone have any suggestions on what wrong it is on using the indirect function? =SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2)), which work fine, I would like to change B$595:B$664 into a variable using indirect function, =SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2)) but it returns #VALUE, does anyone have any suggestions what wrong this statement is on using indirect function? Thanks in advance for any suggestions Eric Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER |
What wrong is it on using the indirect function?
Q2:Q33 is equal to DAY!B$595:B$626, I don't understand why the second
statement is not working, could you please give me any suggestions? Thanks in advance for any suggestions Eric =SUM(--(TRANSPOSE(Q2:Q33)+A$2:A$90=H2)), which is working =SUM(--(TRANSPOSE(INDIRECT("DAY!B$595:B$626"))+A$2:A$90=H 2)), which return #VALUE "Jacob Skaria" wrote: Both the formulas returns the same result..if B594 is 70. B$"&594+B$594 The above will refer to the B (594+70) which is 'B664' =SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2)) =SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2)) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: Does anyone have any suggestions on what wrong it is on using the indirect function? =SUM(--(TRANSPOSE(B$595:B$664)+A$2:A$90=H2)), which work fine, I would like to change B$595:B$664 into a variable using indirect function, =SUM(--(TRANSPOSE(INDIRECT("B$595:B$"&594+B$594))+A$2:A$9 0=H2)) but it returns #VALUE, does anyone have any suggestions what wrong this statement is on using indirect function? Thanks in advance for any suggestions Eric Note: This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER |
All times are GMT +1. The time now is 05:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com