N
nanook
Hi,
I'm really hoping this is something that can be done, any help appreciated!
I am trying to track what pieces of kit are available on particular days. I
have a main sheet (Sheet 1) which displays dates in Row 1 and a list of kit
in Column A. All other sheets (of which there may be many ~30, but this is
changeable) contain the same list of kit in Column A with quantities in
Column B and the first two rows contain details of the job (number, dates,
etc). This will always be arranged in the same way for each subsequent sheet
(so although I'm using vlookup, there might be something simpler...?)
What I need to do is check the date ranges on each subsequent sheet, and if
the date in row 1 of sheet 1 falls within that range to then extract
information from the list of kit on that sheet. I think I have this working
ok for the case of it just looking at sheet 2; the formula I have is:
=IF(B$1<=Sheet2!$H$2,IF(B$1>=Sheet2!$G$2,IF(VLOOKUP($A2,Sheet2!$A$4:$B$23,2,FALSE)=0,"",CONCATENATE(VLOOKUP($A2,Sheet2!$A$4:$B$23,2,FALSE),"
from J#",Sheet2!$A$2)),""),"")
However, I then want it to search through each subsequent sheet for any more
dates ranges that the date falls in, and display this information in the cell
as well. So for instance it might read: 23 from J#1234 12 from J#5678. (There
may be a better way to display this, but I'm struggling to get my head around
it at this stage - I just need to get it looking at multiple sheets first!)
I hope this makes sense, I can clarify more if needed!
Thanks
I'm really hoping this is something that can be done, any help appreciated!
I am trying to track what pieces of kit are available on particular days. I
have a main sheet (Sheet 1) which displays dates in Row 1 and a list of kit
in Column A. All other sheets (of which there may be many ~30, but this is
changeable) contain the same list of kit in Column A with quantities in
Column B and the first two rows contain details of the job (number, dates,
etc). This will always be arranged in the same way for each subsequent sheet
(so although I'm using vlookup, there might be something simpler...?)
What I need to do is check the date ranges on each subsequent sheet, and if
the date in row 1 of sheet 1 falls within that range to then extract
information from the list of kit on that sheet. I think I have this working
ok for the case of it just looking at sheet 2; the formula I have is:
=IF(B$1<=Sheet2!$H$2,IF(B$1>=Sheet2!$G$2,IF(VLOOKUP($A2,Sheet2!$A$4:$B$23,2,FALSE)=0,"",CONCATENATE(VLOOKUP($A2,Sheet2!$A$4:$B$23,2,FALSE),"
from J#",Sheet2!$A$2)),""),"")
However, I then want it to search through each subsequent sheet for any more
dates ranges that the date falls in, and display this information in the cell
as well. So for instance it might read: 23 from J#1234 12 from J#5678. (There
may be a better way to display this, but I'm struggling to get my head around
it at this stage - I just need to get it looking at multiple sheets first!)
I hope this makes sense, I can clarify more if needed!
Thanks