Programming question
in [Programming]
|
Prev: migrate xls app 2003 to 2007 -- get custom macro button out of des
Next: how do i bring a visual basic form to the spread sheet?
From: Howard Brazee on 23 Mar 2010 13:49 I have a VB enabled Excel document. One tab has a value in Column B. If that value is "A" or "B", I want to create a row somewhere that contains the lowest value of the corresponding columns C, D, E, F, G, H, I, J, K, M, N, O, P, Q, R, S, T, U This tab is growing. Could someone assist me in creating this row? -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison
From: Roger Govier on 23 Mar 2010 14:00 Hi Howard No need for a macro. In Z2 enter =IF(OR(B2="A",B2="B"),MIN(MIN(C2:K2),MIN(M2:U2)),"") Copy down the sheet, as far as you wish. As rows become populated, then the formula will produce the result. -- Regards Roger Govier Howard Brazee wrote: > I have a VB enabled Excel document. > > One tab has a value in Column B. If that value is "A" or "B", I want > to create a row somewhere that contains the lowest value of the > corresponding columns C, D, E, F, G, H, I, J, K, M, N, O, P, Q, R, > S, T, U > > This tab is growing. Could someone assist me in creating this row? >
From: Howard Brazee on 23 Mar 2010 14:21 On Tue, 23 Mar 2010 18:00:18 +0000, Roger Govier <roger(a)technology4nospamu.co.uk> wrote: >No need for a macro. >In Z2 enter >=IF(OR(B2="A",B2="B"),MIN(MIN(C2:K2),MIN(M2:U2)),"") > >Copy down the sheet, as far as you wish. >As rows become populated, then the formula will produce the result. I'm trying to produce a new row, not a new column. For instance: New Row name 12 12 13 5 8 ... A 12 55 33 18 9 ... B 33 12 88 5 22 ... C 3 8 12 55 0 ... B 33 88 13 5 8 ... .... -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison
From: Roger Govier on 24 Mar 2010 06:21 Hi Howard Then, place the "new row" as a fixed row ABOVE your existing Data, with a further new row inserted above that. So now your data would start in row 3 In C1 enter 3, in D1 enter 4, highlight both cells and drag across with the fill handle to step up the number. In c2 enter =INDEX($Z:$Z,C$1) and copy across as far as required. You will now have a row of Minimum values in row 2, with the corresponding row number from where this data was extracted in the cell above. -- Regards Roger Govier Howard Brazee wrote: > On Tue, 23 Mar 2010 18:00:18 +0000, Roger Govier > <roger(a)technology4nospamu.co.uk> wrote: > >> No need for a macro. >> In Z2 enter >> =IF(OR(B2="A",B2="B"),MIN(MIN(C2:K2),MIN(M2:U2)),"") >> >> Copy down the sheet, as far as you wish. >> As rows become populated, then the formula will produce the result. > > I'm trying to produce a new row, not a new column. > > For instance: > > New Row name 12 12 13 5 8 ... > A 12 55 33 18 9 ... > B 33 12 88 5 22 ... > C 3 8 12 55 0 ... > B 33 88 13 5 8 ... > ... > > > >
From: Howard Brazee on 24 Mar 2010 10:10
On Wed, 24 Mar 2010 10:21:03 +0000, Roger Govier <roger(a)technology4nospamu.co.uk> wrote: >Hi Howard > >Then, place the "new row" as a fixed row ABOVE your existing Data, with >a further new row inserted above that. >So now your data would start in row 3 My summary row is row 2, I get. Column A currently has a date, column B currently has a type (assume it has values "A", "B", "C", "D", or "E"). I am only interested in summarizing data for rows containing "A" or "B" in column B. >In C1 enter 3, in D1 enter 4, highlight both cells and drag across with >the fill handle to step up the number. I don't understand this. What are the numbers 3 & 4? What's a fill handle? Where do I drag these to? Why? Currently I already have data in C1 and D1 (headings) >In c2 enter >=INDEX($Z:$Z,C$1) >and copy across as far as required. What is "Z"? >You will now have a row of Minimum values in row 2, with the >corresponding row number from where this data was extracted in the cell >above. I'm showing my ignorance here. -- "In no part of the constitution is more wisdom to be found, than in the clause which confides the question of war or peace to the legislature, and not to the executive department." - James Madison |