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
|
Similar Threads
