Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Defined range problem
The following formula is used as a defined range:
=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) What I have difficulty with is that the formula does not ignore cells that contain a formula which do not contain any data. Data ends in C807 but the formula continues on to C1000. What change can be made to the defined range so that it ignores a formula and just recognise a value? Hope someone will be able to point me the right direction. Many thanks. Pat |
#2
|
|||
|
|||
How about:
=OFFSET(Centre!$C$77,0,0, MAX((Centre!$C$77:$C$1000<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) If you know that your data won't exceed a certain number of rows, it's less taxing on excel when you limit the range. (I changed 65536 to 1000 in my suggestion.) == By the way, =counta() counts formulas, too, no matter what they evaluate to. Pat wrote: The following formula is used as a defined range: =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) What I have difficulty with is that the formula does not ignore cells that contain a formula which do not contain any data. Data ends in C807 but the formula continues on to C1000. What change can be made to the defined range so that it ignores a formula and just recognise a value? Hope someone will be able to point me the right direction. Many thanks. Pat -- Dave Peterson |
#3
|
|||
|
|||
Thank you for your help but I am afraid your formula still takes in
C808:C1000 which contain a formula but no data. If I extend the range of your formula to say C1100 your formula will show the defined range to C1000. So clearly the formula in C77:C1000 causes a problem in creating a defined range. Pat "Dave Peterson" wrote in message ... How about: =OFFSET(Centre!$C$77,0,0, MAX((Centre!$C$77:$C$1000<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) If you know that your data won't exceed a certain number of rows, it's less taxing on excel when you limit the range. (I changed 65536 to 1000 in my suggestion.) == By the way, =counta() counts formulas, too, no matter what they evaluate to. Pat wrote: The following formula is used as a defined range: =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) What I have difficulty with is that the formula does not ignore cells that contain a formula which do not contain any data. Data ends in C807 but the formula continues on to C1000. What change can be made to the defined range so that it ignores a formula and just recognise a value? Hope someone will be able to point me the right direction. Many thanks. Pat -- Dave Peterson |
#4
|
|||
|
|||
That formula worked ok for me.
What does your formula look like? Does it really return "" or does it return " " (a space character, maybe??) If it does return a space character, I'd change that formula to return "". But you could also trim the name formula: =OFFSET(Centre!$C$77,0,0,MAX((trim(Centre!$C$77:$C $1000)<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) Pat wrote: Thank you for your help but I am afraid your formula still takes in C808:C1000 which contain a formula but no data. If I extend the range of your formula to say C1100 your formula will show the defined range to C1000. So clearly the formula in C77:C1000 causes a problem in creating a defined range. Pat "Dave Peterson" wrote in message ... How about: =OFFSET(Centre!$C$77,0,0, MAX((Centre!$C$77:$C$1000<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) If you know that your data won't exceed a certain number of rows, it's less taxing on excel when you limit the range. (I changed 65536 to 1000 in my suggestion.) == By the way, =counta() counts formulas, too, no matter what they evaluate to. Pat wrote: The following formula is used as a defined range: =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) What I have difficulty with is that the formula does not ignore cells that contain a formula which do not contain any data. Data ends in C807 but the formula continues on to C1000. What change can be made to the defined range so that it ignores a formula and just recognise a value? Hope someone will be able to point me the right direction. Many thanks. Pat -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Perhaps what you're after is a data validation droplist which will
skip any blanks: "" within the source range ? If so, try this play .. Assuming the source range is: $C$77:$C$2000 Use 2 helper columns, say, cols D and E? Put in D77: =IF(C77="","",ROW()) Put in E77: =INDEX($C$77:$C$2000,MATCH(SMALL($C$77:$C$2000,ROW S($A$1:A1)),$C$77:$C$2000, 0)) Select D77:E77, fill down to E2000 Use the formula below as a defined range (say: MyList) for the data validation (DV): [via Insert Name Define] Names in workbook: MyList Refers to: =OFFSET(Centre!$E$77,0,0,SUMPRODUCT(--NOT(ISERROR(Centre!$E$77:$E$2000))),) What you'll get in the DV with: Allow: List Source: =MyList is a droplist which will skip any blanks: "" within the range C$77:$C$2000 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... Thank you for your help but I am afraid your formula still takes in C808:C1000 which contain a formula but no data. If I extend the range of your formula to say C1100 your formula will show the defined range to C1000. So clearly the formula in C77:C1000 causes a problem in creating a defined range. Pat "Dave Peterson" wrote in message ... How about: =OFFSET(Centre!$C$77,0,0, MAX((Centre!$C$77:$C$1000<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) If you know that your data won't exceed a certain number of rows, it's less taxing on excel when you limit the range. (I changed 65536 to 1000 in my suggestion.) == By the way, =counta() counts formulas, too, no matter what they evaluate to. Pat wrote: The following formula is used as a defined range: =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) What I have difficulty with is that the formula does not ignore cells that contain a formula which do not contain any data. Data ends in C807 but the formula continues on to C1000. What change can be made to the defined range so that it ignores a formula and just recognise a value? Hope someone will be able to point me the right direction. Many thanks. Pat -- Dave Peterson |
#6
|
|||
|
|||
Put in E77:
=INDEX($C$77:$C$2000,MATCH(SMALL($C$77:$C$2000,ROW S($A$1:A1)),$C$77:$C$2000, 0)) Sorry, a correction to the formula above: Put in E77: =INDEX($C$77:$C$2000,MATCH(SMALL($D$77:$D$2000,ROW S($A$1:A1)),$D$77:$D$2000, 0)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Dave,
Well done, by trimming the formula you have sorted that one out perfectly. Many thanks Cheers Pat "Dave Peterson" wrote in message ... That formula worked ok for me. What does your formula look like? Does it really return "" or does it return " " (a space character, maybe??) If it does return a space character, I'd change that formula to return "". But you could also trim the name formula: =OFFSET(Centre!$C$77,0,0,MAX((trim(Centre!$C$77:$C $1000)<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) Pat wrote: Thank you for your help but I am afraid your formula still takes in C808:C1000 which contain a formula but no data. If I extend the range of your formula to say C1100 your formula will show the defined range to C1000. So clearly the formula in C77:C1000 causes a problem in creating a defined range. Pat "Dave Peterson" wrote in message ... How about: =OFFSET(Centre!$C$77,0,0, MAX((Centre!$C$77:$C$1000<"") *ROW(Centre!$C$77:$C$1000))-ROW(Centre!$C$77)+1,1) If you know that your data won't exceed a certain number of rows, it's less taxing on excel when you limit the range. (I changed 65536 to 1000 in my suggestion.) == By the way, =counta() counts formulas, too, no matter what they evaluate to. Pat wrote: The following formula is used as a defined range: =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) What I have difficulty with is that the formula does not ignore cells that contain a formula which do not contain any data. Data ends in C807 but the formula continues on to C1000. What change can be made to the defined range so that it ignores a formula and just recognise a value? Hope someone will be able to point me the right direction. Many thanks. Pat -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
Max,
I am quite interested in your suggestion for using in another part of the workbook. I have tried it out and it almost worked for me. I say almost because when I deleted a cell in colC the result of the formula in colE shifted up one cell instead of staying where it should be. What this has meant is the values of colC and colE are nolonger on the same row. Maybe this is meant to work this way because the dropdown list is working as you intended it to do. One other thing I noticed is the error #NUM! on in some cells at the bottom of colE where there is nothing to calculate in some cells in colC. Pat "Max" wrote in message ... Put in E77: =INDEX($C$77:$C$2000,MATCH(SMALL($C$77:$C$2000,ROW S($A$1:A1)),$C$77:$C$2000, 0)) Sorry, a correction to the formula above: Put in E77: =INDEX($C$77:$C$2000,MATCH(SMALL($D$77:$D$2000,ROW S($A$1:A1)),$D$77:$D$2000, 0)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#9
|
|||
|
|||
"Pat" wrote:
Max, I am quite interested in your suggestion for using in another part of the workbook. I have tried it out and it almost worked for me. I say almost because when I deleted a cell in colC the result of the formula in colE shifted up one cell instead of staying where it should be. What this has meant is the values of colC and colE are nolonger on the same row. Maybe this is meant to work this way because the dropdown list is working as you intended it to do. It'll all stay in sync if you delete the entire row instead of shifting up one cell in col C <g. One alternative to always point to the same range of cells in col C (C77:C2000) irrespective might be to use INDIRECT, for example we could try instead: In D77: =IF(INDIRECT("C"&ROW(A77))="","",ROW()) In E77: =INDEX(INDIRECT("$C$77:$C$2000"),MATCH(SMALL($D$77 :$D$2000,ROWS($A$1:A1)),$D $77:$D$2000,0)) with D77:E77 copied down to E2000 as before The above means that we always want to point only to those cells in the range: C77:C2000 as the target source, so whatever gets shifted up before C77 (e.g.: to C76, C75, etc from where they were formerly within C77:C2000) will be "lost" from the DV. One other thing I noticed is the error #NUM! on in some cells at the bottom of colE where there is nothing to calculate in some cells in colC. The error cells are used / counted by the SUMPRODUCT in the defined range formula to compute the number of items to show in the droplist (in a converse manner, so as to speak) Should the appearance of the error cells bother you, you could conditionally format the range E77:E2000 with the formula: =ISERROR(E77), and choose a font color to blend-in with the fill color (white?) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Defined range difficulty | Excel Discussion (Misc queries) | |||
Dynamic Range Problem | Excel Discussion (Misc queries) | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
HTML_Control Range name | Excel Discussion (Misc queries) |