Pulling multiple rows from seperate tab

[caroline_cb]

Hi,

I am looking to create a planner for my team to use with regards to holiday and bank holidays, basically a calender in excel with the rota thrown in too. My actual spreadsheet has five tabs which the user will use buttons to navigate from one sheet to another.

I have put together another spreadsheet (attached) and provided only the tab that I am having an issue with. Basically on the 'Home' tab it shows todays date, I want a formula that searches the 'Planner' tab for that date and returns the four rows below to the 'Home' page so that at a glance the person using the planner can see what is happening on that day. I have played around with it, but I'm a bit rusty on excel so can't find anything on google that will help.

Any advice would be appreciated. :)

[ARGK]

Hi Caroline
You can use this formula to return the four line values one under the other:

=HLOOKUP(B3,Planner!5:14,2,FALSE)& CHAR(10) &HLOOKUP(B3,Planner!5:14,3,FALSE)& CHAR(10) &HLOOKUP(B3,Planner!5:14,4,FALSE)& CHAR(10) &HLOOKUP(B3,Planner!5:14,5,FALSE)

Good luck.
Tony

[caroline_cb]

Tony,

I have just tried this and it does work, but only if the date you are searching for appears on the top row of the range within the HLOOKUP. I hope this makes sense, so if you are searching for a date in December it doesn't work if November is still present on the sheet.

[ARGK]

Hi Caroline
That will teach me not to try and provide solutions without testing them properly .
I have done it with VBA code sitting behind the worksheet. To view the code right click on the sheet tab name and select View Code.
Hope this works ok for you.
Good luck.
Tony

[caroline_cb]

Tony,

Thanks for this, it works fine

Slight problem though, I'm not sure what the numbers in the reference mean so that I can copy it over to my spreadsheet. Any chance you can let me know......I promise to leave you along then

[ARGK]

Hi Caroline
In cell A3 of the Home sheet this formula simply picks out the month and year of the value in B3. It is used by the code to determine which month you are searching for. Is this what you are refe3rring to or is it the code you are looking at?
Tony

[caroline_cb]

Tony,

Thanks for coming back to me

Its the actual code, I have worked on spreadsheets before, but its been that long that I can't remember it really. The spreadsheet I sent you isn't the one I am actually using so I have to copy your code from there to my sheet.

It is helpful and I really appreciate your help, but I'm not sure what the numbers in the code are so not sure if I will need to change them when copying the code over. Whenever I have worked on spreadsheet its not been using R and C only letters and numbers.

[ARGK]

Hi Caroline
The R and C numbers refer to the Row and Column numbers on the Planner sheet for each month you are searching for. Are you able to post an actual copy of your master workbook (hide the names) and I will change the code for you.
Tony

[caroline_cb]

Tony,

Sorry it's been ages, I have been really busy at work and have not had chance to check for a reply. Please see attached the actual file that i will be using

Thanks,

Caroline