Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using INDIRECT(ADDRESS(...))
=ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8.
=T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE! =T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the appropriate calculation. Why? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using INDIRECT(ADDRESS(...))
What result except #VALUE! did you expect when you tried to subtract the
text string "AK8" from the value in T8? Surely you'd fathomed that out from the fact that INDIRECT did the trick? -- David Biddulph "Dave F" wrote in message ... =ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8. =T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE! =T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the appropriate calculation. Why? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
using INDIRECT(ADDRESS(...))
Because, as HELP explains, ADDRESS() returns a text string, not an address. INDIRECT() converts a text string into an address.
-- Kind regards, Niek Otten Microsoft MVP - Excel "Dave F" wrote in message ... | =ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8. | | =T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE! | | =T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the | appropriate calculation. | | Why? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
using INDIRECT(ADDRESS(...))
Perhaps that's the appropriate way to write it with a desired result.
-- regards "Dave F" wrote: =ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8. =T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE! =T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the appropriate calculation. Why? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
using INDIRECT(ADDRESS(...))
The ADDRESS function returns the cell address, AK8 in this case as text. You
cannot do the second formula because you are saying =T8-"AK8". Using the INDIRECT function gets the value at address AK8 and subtracts it from T8. "Dave F" wrote in message ... =ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8. =T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE! =T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the appropriate calculation. Why? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Indirect(Address(... | Excel Discussion (Misc queries) | |||
Array reference using indirect address | Excel Worksheet Functions | |||
INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions | |||
Summing a range using INDIRECT & ADDRESS | Excel Worksheet Functions |