Formula Relacement in excel

[nishpearl123]

Hi,

I have a table i.e. is dynamic (range may vary). I wish to write a Cell reference of my table in 2 cells & then wish to replace the value of this cells in the formula, so that i dont have to amend all formulas with new table ranges.

Example:

Table is from A1 to C10

Formula
=sumifs(C1:C10,A1:A10,">=1-Apr-2011",A1:A10,"<=30-Apr-2011")

now next time the table becomes A1 to C20

i have to change all formulas & replace 10 with 20.

I tries using "&" in the formula but it is not working with reference in the formula.

Kindly assist,

regards,

ni****h

 

[mikerickson]

Have you looked at Dynamic Named Ranges. A typical example for your situation might be

Name:myRange
RefersTo: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 3)

As data is added to column A, the size of myRange increases. Blank rows are not allowed in this formulation, but there are others.

 

[T. Valko]

Hi,

I have a table i.e. is dynamic (range may vary). I wish to write a Cell reference of my table in 2 cells & then wish to replace the value of this cells in the formula, so that i dont have to amend all formulas with new table ranges.

Example:

Table is from A1 to C10

Formula
=sumifs(C1:C10,A1:A10,">=1-Apr-2011",A1:A10,"<=30-Apr-2011")

now next time the table becomes A1 to C20

i have to change all formulas & replace 10 with 20.

I tries using "&" in the formula but it is not working with reference in the formula.

Kindly assist,

regards,

ni****h

One way...

Create this dynamic named range:

• Goto the Formulas tab
• Defined Names>Define name
• Name: MyTable
• Refers to: =$A$1:INDEX($C:$C,MATCH(1E100,$C:$C))
• OK

Then your formula becomes:

=SUMIFS(INDEX(MyTable,,3),INDEX(MyTable,,1),">=1-Apr-2011",INDEX(MyTable,,1),"<=30-Apr-2011")

I would use cells to hold the date criteria:

• E1 = 4/1/2011
• F1 = 4/30/2011

=SUMIFS(INDEX(MyTable,,3),INDEX(MyTable,,1),">="&E1,INDEX(MyTable,,1),"<="&F1)

 

[Aladin Akyurek]

Hi,

I have a table i.e. is dynamic (range may vary). I wish to write a Cell reference of my table in 2 cells & then wish to replace the value of this cells in the formula, so that i dont have to amend all formulas with new table ranges.

Example:

Table is from A1 to C10

Formula
=sumifs(C1:C10,A1:A10,">=1-Apr-2011",A1:A10,"<=30-Apr-2011")

now next time the table becomes A1 to C20

i have to change all formulas & replace 10 with 20.

I tries using "&" in the formula but it is not working with reference in the formula.

Kindly assist,

regards,

ni****h

Since you are on a recent version of Excel, you could transform the relevant data area into a table (by means of Insert|table). This allows the formulas referencing this area adjust automatically to changes.

 

[nishpearl123]

Thanx a ton.

Lot of issues simplified

Regards,

ni****h