Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




How to Use Relative Reference in Array Formula
Hello, Experts,
I wanted to make array formula in multiple cells with relative references, such as: In cell C1: {=SUM(LEN(A1:A5))} In cell C2: {=SUM(LEN(A2:A6))} In cell C3: {=SUM(LEN(A3:A7))} In cell C4: {=SUM(LEN(A4:A8))} In cell C5: {=SUM(LEN(A5:A9))} .... .... .... but when I highlited range C1:C5 and enter the array by presss Control/Shift/Enter, the array formula in these cells became {=SUM(LEN(A1:A5))}. Is there some way to force the array formula to adopt relative reference? thanks. 
#2




Answer: How to Use Relative Reference in Array Formula
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.programming




How to Use Relative Reference in Array Formula
You put the array formula in cell c1
pressing ctrl+shft+enter and then copy down  Hope this helps Martin Fishlock Please do not forget to rate this reply. " wrote: Hello, Experts, I wanted to make array formula in multiple cells with relative references, such as: In cell C1: {=SUM(LEN(A1:A5))} In cell C2: {=SUM(LEN(A2:A6))} In cell C3: {=SUM(LEN(A3:A7))} In cell C4: {=SUM(LEN(A4:A8))} In cell C5: {=SUM(LEN(A5:A9))} .... .... .... but when I highlited range C1:C5 and enter the array by presss Control/Shift/Enter, the array formula in these cells became {=SUM(LEN(A1:A5))}. Is there some way to force the array formula to adopt relative reference? thanks. 
#4
Posted to microsoft.public.excel.programming




How to Use Relative Reference in Array Formula
Don't select C1:C5 and enter the formula, just select C1 and array enter it,
then copy down.   HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ps.com... Hello, Experts, I wanted to make array formula in multiple cells with relative references, such as: In cell C1: {=SUM(LEN(A1:A5))} In cell C2: {=SUM(LEN(A2:A6))} In cell C3: {=SUM(LEN(A3:A7))} In cell C4: {=SUM(LEN(A4:A8))} In cell C5: {=SUM(LEN(A5:A9))} ... ... ... but when I highlited range C1:C5 and enter the array by presss Control/Shift/Enter, the array formula in these cells became {=SUM(LEN(A1:A5))}. Is there some way to force the array formula to adopt relative reference? thanks. 
#5
Posted to microsoft.public.excel.programming




How to Use Relative Reference in Array Formula
Martin and Bob,
Thank you both for your help. Huaming 
#6
Posted to microsoft.public.excel.programming




How to Use Relative Reference in Array Formula
To avoid having to "array enter" the formula, replace SUM by SUMPRODUCT
 Regards, Luc. "Festina Lente" " wrote: Hello, Experts, I wanted to make array formula in multiple cells with relative references, such as: In cell C1: {=SUM(LEN(A1:A5))} In cell C2: {=SUM(LEN(A2:A6))} In cell C3: {=SUM(LEN(A3:A7))} In cell C4: {=SUM(LEN(A4:A8))} In cell C5: {=SUM(LEN(A5:A9))} .... .... .... but when I highlited range C1:C5 and enter the array by presss Control/Shift/Enter, the array formula in these cells became {=SUM(LEN(A1:A5))}. Is there some way to force the array formula to adopt relative reference? thanks. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Referencing relative to a formula reference  Excel Worksheet Functions  
Using an offset formula for the reference in a relative reference  Excel Worksheet Functions  
Formula needs to reference anchored relative cell  Excel Programming  
Using Relative Reference in a Formula  Excel Programming  
Mixing Absolute and Relative Reference in a Formula  Excel Programming 