| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in calculating # of columns dynamically
Hi,
Assume I have Y # cells of data in row X. (i am using x and y because i am calculating it dynamically using Offset and Match) I want to calculate Y in in the cell B9 with the formula =COUNTA(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6)) ~ this does not work and returns the value 1 however if I put the Formula =MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6) in Cell C10 and the Formula = Counta(Indirect (C10) in the Cell B10 ~ it works fine and returns the # of columns in the matching row. Can someone help me in finding, why it is happening, is there a way to calculate the # of columns by using one formula only. Thanks, Avinash |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help in calculating # of columns dynamically
Avinash,
Try: =COUNTA(INDIRECT(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6))) -- Hope that helps. Vergel Adriano "Avi" wrote: Hi, Assume I have Y # cells of data in row X. (i am using x and y because i am calculating it dynamically using Offset and Match) I want to calculate Y in in the cell B9 with the formula =COUNTA(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6)) ~ this does not work and returns the value 1 however if I put the Formula =MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6) in Cell C10 and the Formula = Counta(Indirect (C10) in the Cell B10 ~ it works fine and returns the # of columns in the matching row. Can someone help me in finding, why it is happening, is there a way to calculate the # of columns by using one formula only. Thanks, Avinash |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help in calculating # of columns dynamically
Yes, It did work.. How did I miss it :(
On Aug 9, 5:30 pm, Vergel Adriano wrote: Avinash, Try: =COUNTA(INDIRECT(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6))) -- Hope that helps. Vergel Adriano "Avi" wrote: Hi, Assume I have Y # cells of data in row X. (i am using x and y because i am calculating it dynamically using Offset and Match) I want to calculate Y in in the cell B9 with the formula =COUNTA(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6)) ~ this does not work and returns the value 1 however if I put the Formula =MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6) in Cell C10 and the Formula = Counta(Indirect (C10) in the Cell B10 ~ it works fine and returns the # of columns in the matching row. Can someone help me in finding, why it is happening, is there a way to calculate the # of columns by using one formula only. Thanks, Avinash |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2000 formula to dynamically sum adjacent rows and columns whererows inserted/deleted | Excel Worksheet Functions | |||
| Excel 2000: Dynamically Generating Data Driven Sheet Columns | Excel Programming | |||
| Sum columns dynamically | Excel Worksheet Functions | |||
| Dynamically Resize Columns? | Excel Programming | |||
| Dynamically add Checkboxes to worksheet columns | Excel Programming | |||
Linear Mode
