Re: dynamic offsets

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

thanks , but how does that solve my problem ?? can you please explain

"Domenic" <domenic22@xxxxxxxxxxxx> wrote in message
news:domenic22-EE7BCE.09101507012006@xxxxxxxxxxxxxxxxxxxxxxx
> Try...
>
> =INDEX(A1:E1,MATCH(MIN(A3:E3),A3:E3,0))
>
> and
>
> =INDEX(A2:E2,MATCH(MIN(A3:E3),A3:E3,0))
>
> Adjust the range accordingly.
>
> Hope this helps!
>
> In article <8o2dnbTZxrabWSLeRVny1g@xxxxxxxxx>,
> "lost" <lost@xxxxxxxxxxxxxx> wrote:
>
>> The data below is used in the descrption of the problem
>>
>>
>>
>> A B C D E
>>
>> 1 34 7 95 0 0
>>
>> 2 5 56 7 94 0
>>
>> 3 342 66 67 219 0
>>
>> 4 1 2 3 4 0
>>
>>
>>
>>
>>
>> A little explanation first
>>
>> I am trying to create a totally dynamic table
>>
>> Row 4 is automatically incremented by one each time the sum of the column
>> is
>> above 0. This is the reason E4 is 0. I am using this to detect if data in
>> entered into the table. I.e the real data in this example is rows 1-3
>>
>>
>>
>> I am trying to detect the lowest number in completed row 3 and then
>> display
>> other cells in the row
>>
>> i.e in the example the lowest number in row 3 is 66, so I want to be able
>> to
>> show cells B2 & B1
>>
>>
>>
>> I so far have the following
>>
>>
>>
>> =(MIN(A3:(OFFSET($A3,0,MAX(A4:IV4)))))
>>
>>
>>
>> which appears to be automatically converted to
>>
>>
>>
>>
>>
>> =(MIN(A3:(OFFSET($A3,0,MAX(4:4)))))
>>
>>
>>
>> This part work fine and shows the contents of B3 (in this example)
>>
>>
>>
>> I tried the following which failed
>>
>>
>>
>> =OFFSET((MIN(A3:(OFFSET($A3,0,MAX(4:4))))),-1,0)
>>
>> Which I was hoping would show the contents of B2
>>
>>
>>
>> I have also tried the MATCH function but again this only gives me an
>> offset.
>>
>> Is it possible to return a cell ID some how, or another way to get this
>> to
>> work
>>
>>
>>
>> Thanks in advance


.

Relevant Pages

  • Re: dynamic offsets
    ... Adjust the range accordingly. ... > The data below is used in the descrption of the problem ... > I am trying to detect the lowest number in completed row 3 and then display ... > other cells in the row ...
    (microsoft.public.excel)
  • Re: darker highlighting
    ... Depending on the monitor the highlight of the selected cells in Excell 2007 ... Try adjusting the contrast of your monitor - either up or down. ... dingy - especially if you then adjust the contrast down. ...
    (microsoft.public.excel.misc)
  • Re: Macros for adjusting graphs every day
    ... by dragging cells around until you have the arrangement you need. ... Jon Peltier, Microsoft Excel MVP ... I am using a copy/paste from MS project to produce 3 graph lines from the ... which adjust themselves according to todays date? ...
    (microsoft.public.excel.misc)
  • Re: How do I adjust Excel cell margins in Office 2007?
    ... I know your message is old, but did you ever find out how to adjust the top ... Jon Peltier, Microsoft Excel MVP ... The reason I wanted to adjust the margins inside the cells is ...
    (microsoft.public.excel.misc)
  • Re: User input box?
    ... That's why I've never changed settings. ... >> added data validation fields so that all the major information cells ... >>>Sub Test() ... >>>(Don't forget to adjust the prompt, ...
    (microsoft.public.excel)