Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
The row coding works wonderfully. I've used it for several years now.
No matter what rows are deleted in a worksheet, the rows numbered with this coding adapt and you don't have any numbering get out of order. But I ran into a problem with one spreadsheet coded with this that I had to create yesterday; the numbering jumps over the hidden rows that aren't shown when the spreadsheet was filtered.. What I mean is that if I choose to filter the worksheet by a certain criteria, the rows that don't follow this criteria are hidden, but the rows that show up don't adapt to show the numbering true to the number of rows now appearing. This is usu. a good thing, but not in this particular speadsheet where the row numbering was to reflect how many records fell under the criteria - either not filtered or filtered by various criteria. So what does this mean? Let's say rows 1, 3, 7, and 10 show up in a filter out of rows 1, 2, 3, 4 ... 10. The rows that appear then are numbered 1, 3, 7 and 10 and not 1, 2, 3 and 4, which would be what is needed here. So row 3 would then become 2, 7 would become 3, and 10 would become 4. Then when unfiltered, they'd go back to the numbering they fall under. Again, most of the time this is good, but not in this one sheet. Is there coding for row numbering that takes into account filtering and adapts accordingly? It's a long shot, I know, but it would be good. I don't know what I'll do if there isn't something like this. p.s., I'm a real VB newbie, just to let everyone know ahead of time. <g Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
StargateFan,
Let's say that you have labels in row 1, and want your row numbering in column A. In Cell A2, use the formula =SUBTOTAL(2,$B$2:B2) and copy down to match your data. Then, when you filter, the row numbering will update as you desire. HTH, Bernie MS Excel MVP "StargateFan" wrote in message ... The row coding works wonderfully. I've used it for several years now. No matter what rows are deleted in a worksheet, the rows numbered with this coding adapt and you don't have any numbering get out of order. But I ran into a problem with one spreadsheet coded with this that I had to create yesterday; the numbering jumps over the hidden rows that aren't shown when the spreadsheet was filtered.. What I mean is that if I choose to filter the worksheet by a certain criteria, the rows that don't follow this criteria are hidden, but the rows that show up don't adapt to show the numbering true to the number of rows now appearing. This is usu. a good thing, but not in this particular speadsheet where the row numbering was to reflect how many records fell under the criteria - either not filtered or filtered by various criteria. So what does this mean? Let's say rows 1, 3, 7, and 10 show up in a filter out of rows 1, 2, 3, 4 ... 10. The rows that appear then are numbered 1, 3, 7 and 10 and not 1, 2, 3 and 4, which would be what is needed here. So row 3 would then become 2, 7 would become 3, and 10 would become 4. Then when unfiltered, they'd go back to the numbering they fall under. Again, most of the time this is good, but not in this one sheet. Is there coding for row numbering that takes into account filtering and adapts accordingly? It's a long shot, I know, but it would be good. I don't know what I'll do if there isn't something like this. p.s., I'm a real VB newbie, just to let everyone know ahead of time. <g Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
On Sat, 5 Jun 2004 21:14:55 -0400, "Bernie Deitrick" <deitbe @
consumer dot org wrote: StargateFan, Let's say that you have labels in row 1, and want your row numbering in column A. In Cell A2, use the formula =SUBTOTAL(2,$B$2:B2) and copy down to match your data. Then, when you filter, the row numbering will update as you desire. Kewl, will give this a try right now! Thanks! HTH, Bernie MS Excel MVP "StargateFan" wrote in message .. . The row coding works wonderfully. I've used it for several years now. No matter what rows are deleted in a worksheet, the rows numbered with this coding adapt and you don't have any numbering get out of order. But I ran into a problem with one spreadsheet coded with this that I had to create yesterday; the numbering jumps over the hidden rows that aren't shown when the spreadsheet was filtered.. What I mean is that if I choose to filter the worksheet by a certain criteria, the rows that don't follow this criteria are hidden, but the rows that show up don't adapt to show the numbering true to the number of rows now appearing. This is usu. a good thing, but not in this particular speadsheet where the row numbering was to reflect how many records fell under the criteria - either not filtered or filtered by various criteria. So what does this mean? Let's say rows 1, 3, 7, and 10 show up in a filter out of rows 1, 2, 3, 4 ... 10. The rows that appear then are numbered 1, 3, 7 and 10 and not 1, 2, 3 and 4, which would be what is needed here. So row 3 would then become 2, 7 would become 3, and 10 would become 4. Then when unfiltered, they'd go back to the numbering they fall under. Again, most of the time this is good, but not in this one sheet. Is there coding for row numbering that takes into account filtering and adapts accordingly? It's a long shot, I know, but it would be good. I don't know what I'll do if there isn't something like this. p.s., I'm a real VB newbie, just to let everyone know ahead of time. <g Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
On Sun, 06 Jun 2004 12:04:29 -0400, StargateFan
wrote: On Sat, 5 Jun 2004 21:14:55 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: StargateFan, Let's say that you have labels in row 1, and want your row numbering in column A. In Cell A2, use the formula =SUBTOTAL(2,$B$2:B2) and copy down to match your data. Then, when you filter, the row numbering will update as you desire. Kewl, will give this a try right now! Well, I took the coding to the office. It seemed to work just great. I tested it out and whenever I did a filter, the rows that showed up numbered true; as well as when all filters were off. That seemed great. But then I kept adding rows at the bottom after the few blank ones that were there so I could see how it acted. Past row #60, something very weird took place. All rows after that were labelled 61! Though the coding that worked before didn't change, whether the rest of the row had data or not other than the row label, and even though Excel modified each row coding correctly to reflect new cell address, just as it did in the numbers early, I got all #61s!! So I came home to try it out on a brand new sheet and set up a sheet here quickly; yet something ever weirder happened here!! ALL rows here at home got named "0"! Is that weird, or what? Anyone know what in heaven's name is going on? <g (Before I think I have to be taken away to a funny farm <lol!) Thanks! Thanks! HTH, Bernie MS Excel MVP "StargateFan" wrote in message . .. The row coding works wonderfully. I've used it for several years now. No matter what rows are deleted in a worksheet, the rows numbered with this coding adapt and you don't have any numbering get out of order. But I ran into a problem with one spreadsheet coded with this that I had to create yesterday; the numbering jumps over the hidden rows that aren't shown when the spreadsheet was filtered.. What I mean is that if I choose to filter the worksheet by a certain criteria, the rows that don't follow this criteria are hidden, but the rows that show up don't adapt to show the numbering true to the number of rows now appearing. This is usu. a good thing, but not in this particular speadsheet where the row numbering was to reflect how many records fell under the criteria - either not filtered or filtered by various criteria. So what does this mean? Let's say rows 1, 3, 7, and 10 show up in a filter out of rows 1, 2, 3, 4 ... 10. The rows that appear then are numbered 1, 3, 7 and 10 and not 1, 2, 3 and 4, which would be what is needed here. So row 3 would then become 2, 7 would become 3, and 10 would become 4. Then when unfiltered, they'd go back to the numbering they fall under. Again, most of the time this is good, but not in this one sheet. Is there coding for row numbering that takes into account filtering and adapts accordingly? It's a long shot, I know, but it would be good. I don't know what I'll do if there isn't something like this. p.s., I'm a real VB newbie, just to let everyone know ahead of time. <g Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
Stargate,
The technique worked for me for thousands of rows. Try forcing a full recalc (Ctrl-Alt-F9) - beyond that: perhaps your computer was unstable, or the Excel workbook was corrupt, or you accidentally pasted values rather than formulas. HTH, Bernie MS Excel MVP "StargateFan" wrote in message ... Well, I took the coding to the office. It seemed to work just great. I tested it out and whenever I did a filter, the rows that showed up numbered true; as well as when all filters were off. That seemed great. But then I kept adding rows at the bottom after the few blank ones that were there so I could see how it acted. Past row #60, something very weird took place. All rows after that were labelled 61! Though the coding that worked before didn't change, whether the rest of the row had data or not other than the row label, and even though Excel modified each row coding correctly to reflect new cell address, just as it did in the numbers early, I got all #61s!! So I came home to try it out on a brand new sheet and set up a sheet here quickly; yet something ever weirder happened here!! ALL rows here at home got named "0"! Is that weird, or what? Anyone know what in heaven's name is going on? <g (Before I think I have to be taken away to a funny farm <lol!) Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ...
Stargate, The technique worked for me for thousands of rows. Try forcing a full recalc (Ctrl-Alt-F9) - beyond that: perhaps your computer was unstable, or the Excel workbook was corrupt, or you accidentally pasted values rather than formulas. HTH, Bernie MS Excel MVP <ROFL Oh, this is tooooo funny!! This time it was #69 that kept repeating. But since the error result changed, I could easily figure out what was wrong! <g Guess where the entries stopped???? Yes! Row 69 is the last row to have data in the cells other than in the number column! Phew! I was wondering what was happening. Naturally, the force-recalc didn't work here as it's not really a glitch. This formula, then, doesn't work properly until data has been entered. The instant I put something in the second column in the next row, the preceding number column changes to the right number in sequence. I tested this for the few remaining rows on that page and the formula adjusted properly each time. So ... Here's what needs fixing - how to keep the cells in the number column, with this special formula in it, blank until such a time as one enters info in the rest of the row anywhere? Then I can protect the sheet and lock the row number column. Anyway, I use a basic formula when I need to make cells "invisible" until there is input. I use this as a basic starting point: =IF(C7<"",E7/7,"") Naturally, I change cell references and formula. I tried to use this type of thing in this situation but must admit that I don't have a clue on integrating the formula =SUBTOTAL(2,$B$2:B2) with an if statement of this kind. If you know how, can you pls advise? With that done, then this will be a breeze and should work perfectly. <fingers crossed Thanks so much! "StargateFan" wrote in message ... Well, I took the coding to the office. It seemed to work just great. I tested it out and whenever I did a filter, the rows that showed up numbered true; as well as when all filters were off. That seemed great. But then I kept adding rows at the bottom after the few blank ones that were there so I could see how it acted. Past row #60, something very weird took place. All rows after that were labelled 61! Though the coding that worked before didn't change, whether the rest of the row had data or not other than the row label, and even though Excel modified each row coding correctly to reflect new cell address, just as it did in the numbers early, I got all #61s!! So I came home to try it out on a brand new sheet and set up a sheet here quickly; yet something ever weirder happened here!! ALL rows here at home got named "0"! Is that weird, or what? Anyone know what in heaven's name is going on? <g (Before I think I have to be taken away to a funny farm <lol!) Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
Stargate,
=IF(B2<"",SUBTOTAL(2,$B$2:B2),"") HTH, Bernie MS Excel MVP "Jones" wrote in message om... "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Stargate, The technique worked for me for thousands of rows. Try forcing a full recalc (Ctrl-Alt-F9) - beyond that: perhaps your computer was unstable, or the Excel workbook was corrupt, or you accidentally pasted values rather than formulas. HTH, Bernie MS Excel MVP <ROFL Oh, this is tooooo funny!! This time it was #69 that kept repeating. But since the error result changed, I could easily figure out what was wrong! <g Guess where the entries stopped???? Yes! Row 69 is the last row to have data in the cells other than in the number column! Phew! I was wondering what was happening. Naturally, the force-recalc didn't work here as it's not really a glitch. This formula, then, doesn't work properly until data has been entered. The instant I put something in the second column in the next row, the preceding number column changes to the right number in sequence. I tested this for the few remaining rows on that page and the formula adjusted properly each time. So ... Here's what needs fixing - how to keep the cells in the number column, with this special formula in it, blank until such a time as one enters info in the rest of the row anywhere? Then I can protect the sheet and lock the row number column. Anyway, I use a basic formula when I need to make cells "invisible" until there is input. I use this as a basic starting point: =IF(C7<"",E7/7,"") Naturally, I change cell references and formula. I tried to use this type of thing in this situation but must admit that I don't have a clue on integrating the formula =SUBTOTAL(2,$B$2:B2) with an if statement of this kind. If you know how, can you pls advise? With that done, then this will be a breeze and should work perfectly. <fingers crossed Thanks so much! "StargateFan" wrote in message ... Well, I took the coding to the office. It seemed to work just great. I tested it out and whenever I did a filter, the rows that showed up numbered true; as well as when all filters were off. That seemed great. But then I kept adding rows at the bottom after the few blank ones that were there so I could see how it acted. Past row #60, something very weird took place. All rows after that were labelled 61! Though the coding that worked before didn't change, whether the rest of the row had data or not other than the row label, and even though Excel modified each row coding correctly to reflect new cell address, just as it did in the numbers early, I got all #61s!! So I came home to try it out on a brand new sheet and set up a sheet here quickly; yet something ever weirder happened here!! ALL rows here at home got named "0"! Is that weird, or what? Anyone know what in heaven's name is going on? <g (Before I think I have to be taken away to a funny farm <lol!) Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
On Mon, 14 Jun 2004 16:19:53 -0400, "Bernie Deitrick" <deitbe @
consumer dot org wrote: Stargate, =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") I was stumped on the equal sign. I had a hunch it might be as easy as taking that out but wasn't sure. I'll give this a try. Thanks! (p.s., posting at home again! Hope it's not too confusing. I posted earlier at work through Google.) HTH, Bernie MS Excel MVP "Jones" wrote in message . com... "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Stargate, The technique worked for me for thousands of rows. Try forcing a full recalc (Ctrl-Alt-F9) - beyond that: perhaps your computer was unstable, or the Excel workbook was corrupt, or you accidentally pasted values rather than formulas. HTH, Bernie MS Excel MVP <ROFL Oh, this is tooooo funny!! This time it was #69 that kept repeating. But since the error result changed, I could easily figure out what was wrong! <g Guess where the entries stopped???? Yes! Row 69 is the last row to have data in the cells other than in the number column! Phew! I was wondering what was happening. Naturally, the force-recalc didn't work here as it's not really a glitch. This formula, then, doesn't work properly until data has been entered. The instant I put something in the second column in the next row, the preceding number column changes to the right number in sequence. I tested this for the few remaining rows on that page and the formula adjusted properly each time. So ... Here's what needs fixing - how to keep the cells in the number column, with this special formula in it, blank until such a time as one enters info in the rest of the row anywhere? Then I can protect the sheet and lock the row number column. Anyway, I use a basic formula when I need to make cells "invisible" until there is input. I use this as a basic starting point: =IF(C7<"",E7/7,"") Naturally, I change cell references and formula. I tried to use this type of thing in this situation but must admit that I don't have a clue on integrating the formula =SUBTOTAL(2,$B$2:B2) with an if statement of this kind. If you know how, can you pls advise? With that done, then this will be a breeze and should work perfectly. <fingers crossed Thanks so much! [snip] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does?
On Mon, 14 Jun 2004 19:46:56 -0400, StargateFan
wrote: On Mon, 14 Jun 2004 16:19:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Stargate, =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") [snip] I tried to use this type of thing in this situation but must admit that I don't have a clue on integrating the formula =SUBTOTAL(2,$B$2:B2) with an if statement of this kind. If you know how, can you pls advise? [snip] This formula has always worked befo =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") But lately since I've been using it, I get all "0" in the column and no numbers. The new spreadsheets where I've been trying to incorporate this are no different from the original spreadsheets where I used this formula. What the above does, btw, is number the rows 1, 2, 3, etc. But the trick is that even when filtered, the numbering is reflected correctly because of this formula. Column A holds the above formula and the IF statement means that the cell in column A is blank until a value is put in the cell to the right of it. Hopefully someone knows why this is no longer working. Thanks! :oD |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" with IF shows only "0" values. What's wrong pls?
On Thu, 06 Oct 2005 00:24:32 -0400, StargateFan
wrote: On Mon, 14 Jun 2004 19:46:56 -0400, StargateFan wrote: On Mon, 14 Jun 2004 16:19:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Stargate, =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") [snip] I tried to use this type of thing in this situation but must admit that I don't have a clue on integrating the formula =SUBTOTAL(2,$B$2:B2) with an if statement of this kind. If you know how, can you pls advise? [snip] This formula has always worked befo =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") But lately since I've been using it, I get all "0" in the column and no numbers. The new spreadsheets where I've been trying to incorporate this are no different from the original spreadsheets where I used this formula. What the above does, btw, is number the rows 1, 2, 3, etc. But the trick is that even when filtered, the numbering is reflected correctly because of this formula. Column A holds the above formula and the IF statement means that the cell in column A is blank until a value is put in the cell to the right of it. Hopefully someone knows why this is no longer working. Thanks! :oD |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"=ROW()-1" with IF shows only "0" values. What's wrong pls?
It works for me if I enter numbers in Column B. However, if I enter letters
or letters or numbers, then I need to use a 3 as the first argument to Subtotal. IF(B2<"",SUBTOTAL(3,$B$2:B2),"") -- Regards, Tom Ogilvy "StargateFan" wrote in message ... On Thu, 06 Oct 2005 00:24:32 -0400, StargateFan wrote: On Mon, 14 Jun 2004 19:46:56 -0400, StargateFan wrote: On Mon, 14 Jun 2004 16:19:53 -0400, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Stargate, =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") [snip] I tried to use this type of thing in this situation but must admit that I don't have a clue on integrating the formula =SUBTOTAL(2,$B$2:B2) with an if statement of this kind. If you know how, can you pls advise? [snip] This formula has always worked befo =IF(B2<"",SUBTOTAL(2,$B$2:B2),"") But lately since I've been using it, I get all "0" in the column and no numbers. The new spreadsheets where I've been trying to incorporate this are no different from the original spreadsheets where I used this formula. What the above does, btw, is number the rows 1, 2, 3, etc. But the trick is that even when filtered, the numbering is reflected correctly because of this formula. Column A holds the above formula and the IF statement means that the cell in column A is blank until a value is put in the cell to the right of it. Hopefully someone knows why this is no longer working. Thanks! :oD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coding "Category (X) Axis"... | Charts and Charting in Excel | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
how do I type "itis" without Excel putting a space "it is"? | Excel Worksheet Functions | |||
Where is the toolbar with the "bold type", "font type", options | New Users to Excel |