I have a standard COUNTIF query in a spreadsheet that I'm adding a macro to
in order to automate its generation.

At present the formula is =COUNTIF('Original Data'!Y:Y,B3) and it returns a
value of 595, which is correct.

I have some code in my macro which returns the column number (25) according
to set criteria and inserts this into the above formula. I have to determine
the column number each time as some enlightened people keep messing with the
source data format and columns move about in their exports (don't ask!).

So, the revised formula looks like this: =COUNTIF('Original Data'!25:25, B3)
and returns a value of 8. A very incorrect number!

Why does this happen and what's the best way to ensure that I get the
correct result returned? I have about 600 of these formulae to set up for
various parts of the spreadsheet. If I knew that the source data layout was
going to be static, I wouldn't be bothering with a macro at all and would
just leave all the formulae in situ.

Many thanks

Hi,

One way of retaining reference to a particular range is to name the range on
the worksheet and use the named range in lieu of the range address. You can
delete columns and insert columns and the named range moves with the data.
However, if you delete the actual column of data you will get an error.

Your formula would then look like this:-

=COUNTIF(Book1!Col_Y,Sheet2!B1)

Where Col_Y was the name given to the range.

Note that the workbook name preceeds the named range not the sheet number.

To name a range:

Select the range. (In your case column Y)
In xl2007 version: Formulas ribbon and Define Name.

Pre xl2007 version: Menu item Insert-Name-Define.

Regards,

OssieMac





"Ladymuck" wrote:

I have a standard COUNTIF query in a spreadsheet that I'm adding a macro to
in order to automate its generation.

At present the formula is =COUNTIF('Original Data'!Y:Y,B3) and it returns a
value of 595, which is correct.

I have some code in my macro which returns the column number (25) according
to set criteria and inserts this into the above formula. I have to determine
the column number each time as some enlightened people keep messing with the
source data format and columns move about in their exports (don't ask!).

So, the revised formula looks like this: =COUNTIF('Original Data'!25:25, B3)
and returns a value of 8. A very incorrect number!

Why does this happen and what's the best way to ensure that I get the
correct result returned? I have about 600 of these formulae to set up for
various parts of the spreadsheet. If I knew that the source data layout was
going to be static, I wouldn't be bothering with a macro at all and would
just leave all the formulae in situ.

Many thanks

Not sure how this will work. The data in 'Original Data' will be overwritten
with the latest export so the named ranges will need to be redefined each
time. I'll have a thinkg and give it a try.

Thanks for the suggestion

"OssieMac" wrote:

Hi,

One way of retaining reference to a particular range is to name the range on
the worksheet and use the named range in lieu of the range address. You can
delete columns and insert columns and the named range moves with the data.
However, if you delete the actual column of data you will get an error.

Your formula would then look like this:-

=COUNTIF(Book1!Col_Y,Sheet2!B1)

Where Col_Y was the name given to the range.

Note that the workbook name preceeds the named range not the sheet number.

To name a range:

Select the range. (In your case column Y)
In xl2007 version: Formulas ribbon and Define Name.

Pre xl2007 version: Menu item Insert-Name-Define.

Regards,

OssieMac





"Ladymuck" wrote:

I have a standard COUNTIF query in a spreadsheet that I'm adding a macro to
in order to automate its generation.

At present the formula is =COUNTIF('Original Data'!Y:Y,B3) and it returns a
value of 595, which is correct.

I have some code in my macro which returns the column number (25) according
to set criteria and inserts this into the above formula. I have to determine
the column number each time as some enlightened people keep messing with the
source data format and columns move about in their exports (don't ask!).

So, the revised formula looks like this: =COUNTIF('Original Data'!25:25, B3)
and returns a value of 8. A very incorrect number!

Why does this happen and what's the best way to ensure that I get the
correct result returned? I have about 600 of these formulae to set up for
various parts of the spreadsheet. If I knew that the source data layout was
going to be static, I wouldn't be bothering with a macro at all and would
just leave all the formulae in situ.

Many thanks

Overwriting the data in the named range does not affect the named range. The
name applies to the range of cells not the data in them. Therefore if you
reference the named range then it will return whatever happens to be in them.

Regards,

OssieMac


"Ladymuck" wrote:

Not sure how this will work. The data in 'Original Data' will be overwritten
with the latest export so the named ranges will need to be redefined each
time. I'll have a thinkg and give it a try.

Thanks for the suggestion

"OssieMac" wrote:

Hi,

One way of retaining reference to a particular range is to name the range on
the worksheet and use the named range in lieu of the range address. You can
delete columns and insert columns and the named range moves with the data.
However, if you delete the actual column of data you will get an error.

Your formula would then look like this:-

=COUNTIF(Book1!Col_Y,Sheet2!B1)

Where Col_Y was the name given to the range.

Note that the workbook name preceeds the named range not the sheet number.

To name a range:

Select the range. (In your case column Y)
In xl2007 version: Formulas ribbon and Define Name.

Pre xl2007 version: Menu item Insert-Name-Define.

Regards,

OssieMac





"Ladymuck" wrote:

I have a standard COUNTIF query in a spreadsheet that I'm adding a macro to
in order to automate its generation.

At present the formula is =COUNTIF('Original Data'!Y:Y,B3) and it returns a
value of 595, which is correct.

I have some code in my macro which returns the column number (25) according
to set criteria and inserts this into the above formula. I have to determine
the column number each time as some enlightened people keep messing with the
source data format and columns move about in their exports (don't ask!).

So, the revised formula looks like this: =COUNTIF('Original Data'!25:25, B3)
and returns a value of 8. A very incorrect number!

Why does this happen and what's the best way to ensure that I get the
correct result returned? I have about 600 of these formulae to set up for
various parts of the spreadsheet. If I knew that the source data layout was
going to be static, I wouldn't be bothering with a macro at all and would
just leave all the formulae in situ.

Many thanks

Not quite what I meant. I know the ranges will not move but, if the data for
week one was in column Y but, after the aforementioned tinkering that
happens, it is in column AA the next week, I will have to define the named
ranges. I can do that, it's not a problem, I just need to think about the
extra coding required to redefine the named ranges everytime the file is
opened and the macro is rerun against new data.

"OssieMac" wrote:

Overwriting the data in the named range does not affect the named range. The
name applies to the range of cells not the data in them. Therefore if you
reference the named range then it will return whatever happens to be in them.

Regards,

OssieMac


"Ladymuck" wrote:

Not sure how this will work. The data in 'Original Data' will be overwritten
with the latest export so the named ranges will need to be redefined each
time. I'll have a thinkg and give it a try.

Thanks for the suggestion

"OssieMac" wrote:

Hi,

One way of retaining reference to a particular range is to name the range on
the worksheet and use the named range in lieu of the range address. You can
delete columns and insert columns and the named range moves with the data.
However, if you delete the actual column of data you will get an error.

Your formula would then look like this:-

=COUNTIF(Book1!Col_Y,Sheet2!B1)

Where Col_Y was the name given to the range.

Note that the workbook name preceeds the named range not the sheet number.

To name a range:

Select the range. (In your case column Y)
In xl2007 version: Formulas ribbon and Define Name.

Pre xl2007 version: Menu item Insert-Name-Define.

Regards,

OssieMac





"Ladymuck" wrote:

I have a standard COUNTIF query in a spreadsheet that I'm adding a macro to
in order to automate its generation.

At present the formula is =COUNTIF('Original Data'!Y:Y,B3) and it returns a
value of 595, which is correct.

I have some code in my macro which returns the column number (25) according
to set criteria and inserts this into the above formula. I have to determine
the column number each time as some enlightened people keep messing with the
source data format and columns move about in their exports (don't ask!).

So, the revised formula looks like this: =COUNTIF('Original Data'!25:25, B3)
and returns a value of 8. A very incorrect number!

Why does this happen and what's the best way to ensure that I get the
correct result returned? I have about 600 of these formulae to set up for
various parts of the spreadsheet. If I knew that the source data layout was
going to be static, I wouldn't be bothering with a macro at all and would
just leave all the formulae in situ.

Many thanks