Excel Formula

[Guest]

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.

 

[Guest]

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:

 

[Guest]

Stefi,

Sorry can't follow. Tried replacing the "if" with "Choose" and amending
"B2=1" to "B2" but now just get #VALUE!