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