M
Mark
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(B2=3,VLOOKUP(A2,Wednesday!$B$3:$AV$63,2,FALSE),IF(B2=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,VLOOKUP(A2,Sunday!$B$3:$AV$63,2,FALSE))))))))
Appreciate your thoughts.
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(B2=3,VLOOKUP(A2,Wednesday!$B$3:$AV$63,2,FALSE),IF(B2=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,VLOOKUP(A2,Sunday!$B$3:$AV$63,2,FALSE))))))))
Appreciate your thoughts.