cell checking
 

I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input

Lee,

In cell B77, use the array formula:

=INDEX(B57:B76,MAX((B57:B760)*ROW(B57:B76))-ROW($B$57)+1)

Entered with Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input

what do you mean by "Entered with Ctrl-Shift-Enter."


"Bernie Deitrick" wrote:

Lee,

In cell B77, use the array formula:

=INDEX(B57:B76,MAX((B57:B760)*ROW(B57:B76))-ROW($B$57)+1)

Entered with Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Lee wrote:

what do you mean by "Entered with Ctrl-Shift-Enter."

"Bernie Deitrick" wrote:

Lee,

In cell B77, use the array formula:

=INDEX(B57:B76,MAX((B57:B760)*ROW(B57:B76))-ROW($B$57)+1)

Entered with Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input






--

Dave Peterson

Lee,

Instead of typing in the formula and pressing enter, type in the formula, hold down both the ctrl
and shift buttons, and while they are help down, press enter. Just like Ctrl-Alt-Delete.... That
tells Exel to process the formula differently.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
what do you mean by "Entered with Ctrl-Shift-Enter."


"Bernie Deitrick" wrote:

Lee,

In cell B77, use the array formula:

=INDEX(B57:B76,MAX((B57:B760)*ROW(B57:B76))-ROW($B$57)+1)

Entered with Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input

Thanks alot you guys,

Learn something new every day

"Bernie Deitrick" wrote:

Lee,

Instead of typing in the formula and pressing enter, type in the formula, hold down both the ctrl
and shift buttons, and while they are help down, press enter. Just like Ctrl-Alt-Delete.... That
tells Exel to process the formula differently.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
what do you mean by "Entered with Ctrl-Shift-Enter."


"Bernie Deitrick" wrote:

Lee,

In cell B77, use the array formula:

=INDEX(B57:B76,MAX((B57:B760)*ROW(B57:B76))-ROW($B$57)+1)

Entered with Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


"Lee" wrote in message
...
I have two Sheets (sheet1,sheet2), Sheet 1 has corridinate data imported from
Autocad. On Sheet 2, cells B57 through B76 have IF statements in them for
calculating data from Sheet 1.

What I want to do is check if cell B76 equals 0. If the cell equals 0 then
go to cell b75 and check if it equals 0 and so on. When it gets to the first
cell that is greater than 0 do a calculation that would return a value to
cell B77, and stop checking.

Is this possible without using VBA programming ?

Thanks for your input