Define range as formula:formula

[ManuGrim]

Hello all,

Where am I going wrong with this?

=ROW('Sheet1'!&(COUNTIF($C$1:C30,C30)&":"&COUNTIF($C$1:C30,C30))

Thank you for your support!

 

[Ramesh]

Hi Manu,

Can provide some more insights where you are writing this formula and what is the expected output plz


Thanks,
Ramesh

 

[Hui]

I think you want:
=ROW(INDIRECT("'Sheet1'!"&COUNTIF($C$1:C30,C30)&":"&COUNTIF($C$1:C30,C30)))

But depending on values if either of the Countif evaluates to zero you will get an error

However this formula will evaluate to the row number of the lowest row :
eg:
Row(Sheet1!2:3) will return 2
Row(Sheet1!12:30) will return 12
Row(Sheet1!30:9) will return 9

So I don't know if that is really want you are trying to achieve

You possibly want to sum the numbers in those columns and if that is the case
try:
=Sum(ROW(INDIRECT("'Sheet1'!"&COUNTIF($C$1:C30,C30)&":"&COUNTIF($C$1:C30,C30))))

 

[Hui]

If this doesn't help you can you please post a sample file with data and explain the result you want

 

[ManuGrim]

Thank you guys,
This really helped.

Explanation of what I wanted:
1. Define a range which is normally A1:A10, or ROW(1:1) as formula:formula

This helps one return the value of a match. When the reference value is the same it works fine, but when the reference column is a list with various values it will not. So instead of going on and on, it restarts with the first value for the next occurrence.

Please see example.

Thank you guys once again!

 

[NARAYANK991]

Hi ,

I am not clear about what you wish to do , but if you want the results in column G , then the standard formula to do this , given the data in column F , would be the following array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(Table1[Information in Sheet1],SMALL(IF(Table1[Column1]=F3,ROW(Table1[Information in Sheet1]) - ROW(Table1[[#Headers],[Information in Sheet1]])),COUNTIF(F$3:F3,F3)))

Narayan

 

[NARAYANK991]

Hi Hui ,

A small correction :

In the formula :

=ROW(INDIRECT("'Sheet1'!"&COUNTIF($C$1:C30,C30)&":"&COUNTIF($C$1:C30,C30)))

the COUNTIF function can never return 0 , since the second parameter C30 is a part of the first parameter C1:C30. The lowest value possible would be 1.

Narayan