Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
build up an array using formulas
I want to see in a variable range (A) if a certain value occurs.
The first and the last cell of range A are determined by other values. For example In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY) In col 2 I got values like "CC", "VV", ... or nothing In col 4 till 7 I got something like this from till 0 01 January 2005 08 January 2005 1 09 January 2005 05 February 2005 In col 7 I would like to find the cell in col 2 where the value = "CC" within the range defined by the from and till fields. The value "CC" may normally only occur once in this range. So I would need a function or something that translates the "from - till" fields in a range of col 2 and than look for the value "CC" in this range and return the date from col 1 on this row. Can someone help me out ? I already tried several combinations on match, index, lookup, ... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
build up an array using formulas
Hi Kezze,
do you need to find a formula-based solution for your task or would VBA also be possible? If you need a cell formula only, I could imagine about some database functions... but I'm not firm on that... With VBA, this problem should be very solvable... would that be okay too? Best Markus -----Original Message----- I want to see in a variable range (A) if a certain value occurs. The first and the last cell of range A are determined by other values. For example In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY) In col 2 I got values like "CC", "VV", ... or nothing In col 4 till 7 I got something like this from till 0 01 January 2005 08 January 2005 1 09 January 2005 05 February 2005 In col 7 I would like to find the cell in col 2 where the value = "CC" within the range defined by the from and till fields. The value "CC" may normally only occur once in this range. So I would need a function or something that translates the "from - till" fields in a range of col 2 and than look for the value "CC" in this range and return the date from col 1 on this row. Can someone help me out ? I already tried several combinations on match, index, lookup, ... . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
build up an array using formulas
Assme Start Date in E1 and End Date in F1 then:
=LARGE((($A$1:$A$100=E1)*($A$1:$A$100<=F1)*($B$1: $B$100="CC")*ROW($B$1:$B$1 00)),1) Entered with Ctrl+Shift+Enter rather than enter since this is an array formula. -- Regards, Tom Ogilvy "Kezze" wrote in message ... I want to see in a variable range (A) if a certain value occurs. The first and the last cell of range A are determined by other values. For example In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY) In col 2 I got values like "CC", "VV", ... or nothing In col 4 till 7 I got something like this from till 0 01 January 2005 08 January 2005 1 09 January 2005 05 February 2005 In col 7 I would like to find the cell in col 2 where the value = "CC" within the range defined by the from and till fields. The value "CC" may normally only occur once in this range. So I would need a function or something that translates the "from - till" fields in a range of col 2 and than look for the value "CC" in this range and return the date from col 1 on this row. Can someone help me out ? I already tried several combinations on match, index, lookup, ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
build up an array using formulas
Thanks Tom,
Exactly what I needed. I think I should investigate some more on the possibility's of array formulas "Tom Ogilvy" wrote: Assme Start Date in E1 and End Date in F1 then: =LARGE((($A$1:$A$100=E1)*($A$1:$A$100<=F1)*($B$1: $B$100="CC")*ROW($B$1:$B$1 00)),1) Entered with Ctrl+Shift+Enter rather than enter since this is an array formula. -- Regards, Tom Ogilvy "Kezze" wrote in message ... I want to see in a variable range (A) if a certain value occurs. The first and the last cell of range A are determined by other values. For example In col 1 I got dates from 01/01/2005 till 31/12/2005 (DD/MM/YYYY) In col 2 I got values like "CC", "VV", ... or nothing In col 4 till 7 I got something like this from till 0 01 January 2005 08 January 2005 1 09 January 2005 05 February 2005 In col 7 I would like to find the cell in col 2 where the value = "CC" within the range defined by the from and till fields. The value "CC" may normally only occur once in this range. So I would need a function or something that translates the "from - till" fields in a range of col 2 and than look for the value "CC" in this range and return the date from col 1 on this row. Can someone help me out ? I already tried several combinations on match, index, lookup, ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Build an array constant with DATE function | Excel Worksheet Functions | |||
build up an array using formulas | Excel Discussion (Misc queries) | |||
build up an array using formulas | Excel Discussion (Misc queries) | |||
build array of columns | Excel Programming |