0

I have a worksheet where data is updated from an external source. The page contains data from today going out 20 days. I have a named range for each column i.e. Today ($D$4:$D$50), Tomorrow ($E$4:$E$50), etc, etc. My issue is that sometime the data from the external source does not contain data so nothing is populated in the columns. However, when there is data being returned again the name ranges are automatically changing i.e , Today changes to ($F$4:$F$50). No new columns are being inserted or deleted.

How can I specify / force the name ranges always to stay the same i.e. Today is always column E, I thought that was the point of the $.

Thanks.

user3890637
  • 17
  • 4
  • 2
    How is the data being pulled in, by a macro? Maybe the range is defined in the macro and that's why it's being set to something other than what you'd expect. – sous2817 Aug 12 '14 at 12:22
  • The point of named ranges is that you don't have to care about whether it's column E or F. If you do, you are doing something wrong. – GSerg Aug 12 '14 at 12:49

1 Answers1

0

you could try this formulae for your named range formula.

In Name Manager, edit your named range and pop a modified version of the formulea below into the refers to: field.

The modifications will be to the numbers within the address() function. The first value is the row number, the latter is the column number, so Column A = 1, Column B = 2 and so forth.

=INDIRECT(ADDRESS(1,13)&":"&ADDRESS(50,13))

It then uses the indirect function plus a concatentation of the 2 address functions to change formulae to a cell reference that the named range can use.

So the example above, means my named range will go from M1:M50.

KevHun
  • 21
  • 3
  • Thanks, if I need to specify a sheet where would I enter that in the formula i.e. sheet3? – user3890637 Aug 12 '14 at 15:58
  • Just do this: =INDIRECT("Sheet1!"&ADDRESS(1,1)&":"&ADDRESS(50,1)). I've just appended "Sheet1!" infront of the address, just for the first address. This then adds the sheet address – KevHun Aug 13 '14 at 12:12