dynamic named range

Hi,

I am having a few problems with dynamic named range in excel 2000.

When adding new data to the range, excel extends the range correctly, but only copies some of the formula correctly. It does not copy the formula that references a cell from another line.

I am trying to create a excel spreadsheet and have a formula =e10-e9, which does not copy down.

Any help provided would be gratefully received.


Thanks

Hi

By the sounds of it, you are not actually having any problems with your dynamically named range? I am afraid I am finding it difficult to understand exactly what problem you are experiencing. Perhaps you could show, by way of examples, what cells you are having trouble with (and what they contain) and what you think they should contain after you add new data to your row?

Originally Posted by RichardSchollar

Hi

By the sounds of it, you are not actually having any problems with your dynamically named range? I am afraid I am finding it difficult to understand exactly what problem you are experiencing. Perhaps you could show, by way of examples, what cells you are having trouble with (and what they contain) and what you think they should contain after you add new data to your row?

Hi, thanks for the response. I will try and provide more detail.

I have a formula which works out the trip distance by subtracting the latest odo reading from the last.

Column E has the odo readings listed. In cell G10 I have the fomula =E10-E9

The dynamic named range works for other formulas, but only from cells in that row, is this a limitation on dynamic ranges?

But where does the DNR come into play in this? Is it referencing column G?

Originally Posted by RichardSchollar

But where does the DNR come into play in this? Is it referencing column G?

Hello again,

I have added a sample spreadsheet.

If you add figures to the A12 to D12, DNR fills in H12 to J15 ok, but it does not fill in cells E12 to G12.

Thanks

Hi Compaq

Would you mind posting a message with what formulas you have in the cells (eg E3:J3) as when i open your file I get an error message saying some data has been lost (the formulas are replaced with #N/A, although the DNR exists and the data values in A:D are visible). Thanks!

Richard

Originally Posted by RichardSchollar

Hi Compaq

Would you mind posting a message with what formulas you have in the cells (eg E3:J3) as when i open your file I get an error message saying some data has been lost (the formulas are replaced with #N/A, although the DNR exists and the data values in A:D are visible). Thanks!

Richard

Hi,

E3=IF(ISNUMBER((D3)),D3-D2,"")
F3=IF(AND(ISNUMBER(B3),ISNUMBER(C3),ISNUMBER(D3)),(D3-D2)/((B3/C3)/4.54609188),"")
G3=IF(AND(ISNUMBER(D3),ISNUMBER(J3)),E3/(J3),"")
H3=IF(ISNUMBER(C3),C3*4.54609188,"")
I3=IF(AND(ISNUMBER(B3),ISNUMBER(C3)),(B3/C3)/4.54609188,"")
J3=IF(AND(ISNUMBER(B3),ISNUMBER(C3)),(B3/C3),"")

Thanks.

Thanks for the formulas.

Firstly, I'm not certain why this happened. Secondly, it doesn't have anything to do with your use of the Dynamically Named Range (it is actually a setting within Tools>Options>Edit tab and check "Extend data range formats and formulas").

To ensure the formulas correctly populated E:G cells when a new record was added, I converted the data range (ie A1:J11) into a List (via Data>List>Create List).

Now, if you select any cell containing data, you should see a blank row appear at the bottom (with the formatting) and a blue asterisk at the far left hand side. Just fill in this row with data A:D and the formulas should populate in E:J. You can then continue adding further records as required. I have attached your sample workbook.

Note that Lists were new to xl2003, so this won't work if you are using an earlier version.

Hope this helps!

Richard

Ah - just read you're using xl2000.

I shall investigate further...

OK, I think it was caused by the formulas in E & F as they referred to cells above the row in which they appeared (G referred to E which was why that one didn't populate). I have changed the formulas slightly so that the OFFSET command is used instead of referring to the row above (the result is the same). Please see attached.

Note: for some reason I am unable to attach the workbook. The formulas I have used in E3 and F3 copied down are:

E3 =IF(ISNUMBER((D3)),D3-OFFSET(D3,-1,0),"")
F3 =IF(AND(ISNUMBER(B3),ISNUMBER(C3),ISNUMBER(D3)), (D3-OFFSET(D3,-1,0))/((B3/C3)/4.54609188),"")

No change is required to G3.

Hope this helps!

Originally Posted by RichardSchollar

OK, I think it was caused by the formulas in E & F as they referred to cells above the row in which they appeared (G referred to E which was why that one didn't populate). I have changed the formulas slightly so that the OFFSET command is used instead of referring to the row above (the result is the same). Please see attached.

Note: for some reason I am unable to attach the workbook. The formulas I have used in E3 and F3 copied down are:

E3 =IF(ISNUMBER((D3)),D3-OFFSET(D3,-1,0),"")
F3 =IF(AND(ISNUMBER(B3),ISNUMBER(C3),ISNUMBER(D3)), (D3-OFFSET(D3,-1,0))/((B3/C3)/4.54609188),"")

No change is required to G3.

Hope this helps!

Hi Richard,

This has fixed the problem. Not sure why it doesnt copy my formula down, but your offset solution works.

Thanks for your help on this.