Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cell names | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |