Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
I am attempting to create a summary of a weeks actions on worksheet one. The
summary is of work that is listed on seven other worksheets (Monday to Sunday) There are fifty lines per worksheet. If a line is popluated I need some of the cells on that line to appear on the summary (lookup tables) but if the line is blank I need to then search the next worksheet for the next populated line and so on. What I have also done (in an effort to use the lookup formula below) is to automatically number a line as it is populated. This numbering system works throught the seven days concurrently and only places the next number in a populated line leaving a blank line with no number. Formula works for the first worksheet that is populated but then returns a "False" statement there after. Am I on the right rtack or should I be doing something else. So the summary sheet is number 1 to 400 and each populated line generates the next number in sequence hence my thoughts of a lookup tabel to follow through the worksheets. The formula I am attempting to use is as follows =IF(B2=1,VLOOKUP(A2,Monday!$B$3:$AV$63,2,FALSE),IF (B2=2,VLOOKUP(A2,Tuesday!$B$3:$AV$63,2,FALSE),IF(B 2=3,VLOOKUP(A2,Wednesday!$B$3:$AV$63,2,FALSE),IF(B 2=4,VLOOKUP(A2,Thursday!$B$3:$AV$63,2,FALSE),IF(B2 =5,VLOOKUP(A2,Friday!$B$3:$AV$63,2,FALSE),IF(B2=6, VLOOKUP(A2,Saturday!$B$3:$AV$63,2,FALSE),IF(B2=7,V LOOKUP(A2,Sunday!$B$3:$AV$63,2,FALSE)))))))) Appreciate your thoughts. -- Cheers...Mark |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
Perhaps you can compose a less complicated formula using
=CHOOSE(B1,VLOOKUP(A2,Monday!$B$3:$AV$63,2,FALSE), ... etc.) Regards, Stefi €˛Mark€¯ ezt Ć*rta: I am attempting to create a summary of a weeks actions on worksheet one. The summary is of work that is listed on seven other worksheets (Monday to Sunday) There are fifty lines per worksheet. If a line is popluated I need some of the cells on that line to appear on the summary (lookup tables) but if the line is blank I need to then search the next worksheet for the next populated line and so on. What I have also done (in an effort to use the lookup formula below) is to automatically number a line as it is populated. This numbering system works throught the seven days concurrently and only places the next number in a populated line leaving a blank line with no number. Formula works for the first worksheet that is populated but then returns a "False" statement there after. Am I on the right rtack or should I be doing something else. So the summary sheet is number 1 to 400 and each populated line generates the next number in sequence hence my thoughts of a lookup tabel to follow through the worksheets. The formula I am attempting to use is as follows =IF(B2=1,VLOOKUP(A2,Monday!$B$3:$AV$63,2,FALSE),IF (B2=2,VLOOKUP(A2,Tuesday!$B$3:$AV$63,2,FALSE),IF(B 2=3,VLOOKUP(A2,Wednesday!$B$3:$AV$63,2,FALSE),IF(B 2=4,VLOOKUP(A2,Thursday!$B$3:$AV$63,2,FALSE),IF(B2 =5,VLOOKUP(A2,Friday!$B$3:$AV$63,2,FALSE),IF(B2=6, VLOOKUP(A2,Saturday!$B$3:$AV$63,2,FALSE),IF(B2=7,V LOOKUP(A2,Sunday!$B$3:$AV$63,2,FALSE)))))))) Appreciate your thoughts. -- Cheers...Mark |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
Stefi,
Sorry can't follow. Tried replacing the "if" with "Choose" and amending "B2=1" to "B2" but now just get #VALUE! -- Cheers...Mark "Stefi" wrote: Perhaps you can compose a less complicated formula using =CHOOSE(B1,VLOOKUP(A2,Monday!$B$3:$AV$63,2,FALSE), ... etc.) Regards, Stefi €˛Mark€¯ ezt Ć*rta: I am attempting to create a summary of a weeks actions on worksheet one. The summary is of work that is listed on seven other worksheets (Monday to Sunday) There are fifty lines per worksheet. If a line is popluated I need some of the cells on that line to appear on the summary (lookup tables) but if the line is blank I need to then search the next worksheet for the next populated line and so on. What I have also done (in an effort to use the lookup formula below) is to automatically number a line as it is populated. This numbering system works throught the seven days concurrently and only places the next number in a populated line leaving a blank line with no number. Formula works for the first worksheet that is populated but then returns a "False" statement there after. Am I on the right rtack or should I be doing something else. So the summary sheet is number 1 to 400 and each populated line generates the next number in sequence hence my thoughts of a lookup tabel to follow through the worksheets. The formula I am attempting to use is as follows =IF(B2=1,VLOOKUP(A2,Monday!$B$3:$AV$63,2,FALSE),IF (B2=2,VLOOKUP(A2,Tuesday!$B$3:$AV$63,2,FALSE),IF(B 2=3,VLOOKUP(A2,Wednesday!$B$3:$AV$63,2,FALSE),IF(B 2=4,VLOOKUP(A2,Thursday!$B$3:$AV$63,2,FALSE),IF(B2 =5,VLOOKUP(A2,Friday!$B$3:$AV$63,2,FALSE),IF(B2=6, VLOOKUP(A2,Saturday!$B$3:$AV$63,2,FALSE),IF(B2=7,V LOOKUP(A2,Sunday!$B$3:$AV$63,2,FALSE)))))))) Appreciate your thoughts. -- Cheers...Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel Formula Doesn't Execute (Shows formula-not the calcuation) | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |