Can i have tabs in my excel sheet ?

[arunabha]

Dear Sir,

Can I have an excel sheet with multiple tabs ? to make my point clear would like site the example of this forum website. on very first page we tabs like "Forum","What's new?","Members List", etc. etc...Like wise can I have a excel sheet in which a hyperlink doesn't take you to a different sheet, rather you see the content on the very first page only. I found this excel somewhere in the internet and was very impressed to see this can also be done in excel. You can see tabs like "Setup", Personal Info","Attendance", etc. etc., also there are tabs for different months as well. Amazing !!

Can you please guide me on this.

[Bernie Deitrick]

That is a single workbook with 28 worksheets, all protected, with hyperlinks between the worksheets on every worksheet in the same position. So you are actualy going to different sheets, but the hyperlinks are on every sheet. Note how the columns change width and how many cells are merged - whoever developed it either did it manually on one sheet and copied it to all others, or developed a macro that does it for them. Neither is particularly hard. Excel does not offer an option to move the sheet tabs to the top, so if you want that look, you need to do it yourself.

[arunabha]

Dear Sir,

Thank you for your guidance.
Also, would like to ask you, in the said sheet how they have made the tab names invisible ?
I am not too sure if you have already answered this question as I am very new to excel.

[Bernie Deitrick]

With your workbook open, choose File / Options... Advanced and under "Display Options for this workbook:" un-check "Show sheet tabs"

[arunabha]

Great Sir,
Thank you very much for sharing this information.

Aside>>Though my questions have been answered, still will keep this discussion on as I am working on implementing the hyperlink in my own excel sheet. Obviously, will need your assistance. Hence. . . . . .

[Bernie Deitrick]

Create your workbook with all the sheets you want, named the way you want them and format them the way you want. Then format row 3 of the first sheet the way you want, with colors and line and merged cells etc. Then in the first cell (or merged cell), use Insert / Hyperlink. Choose "Place in this document" and change the text to display to the name of the first sheet, and A4 as the cell reference, and the first sheet as the place in the document for the link. Do that for all the sheets, (in the cells across row 3) then copy row 3, select sheets 2 through the last sheet (using click / shift-click), and paste onto Row 3 of the grouped sheets. And the hyperlinks will be on all the sheets
.

[arunabha]

Dear Sir,

Please find the attached workbook created with your kind assistance.
Now, the requirement is that which employee is with the organisation for less than one year should get 17 paid leaves and 42 leaves for them who spent more than a year by now. For your ref. I have marked two cells on January sheet. Can we do something using conditional formatting or may be someway (preferably not using a macro) to put an alert system. So, whenever I am opening a sheet it will show which people has his one year completion on which date. Hope I am able to transfer my thoughts to you.

Kindly help if possible.

Thank you.

[arunabha]

Any suggestion Sir !!??

[ufopilot]

test_MonthlyAttendance.xlsx

Hi Arunabha,
I have browsed through the sheet you attached, and have conditional formatted for the <365> days. I also placed a formula (JAN Sheet) that would get you the time of service in Years, Months and Days - you can delete this formula and use what you had before (DOJ+364).
Hope this helps

[arunabha]

Dear Sir,
Thank you very much for spending your valuable time for me. Really appreciate your efforts.

What exactly I needed is that on a month the specific date should get highlighted, as I have done in the excel, on which the employee is completing his first year.
Why I need is because our ourganisation has a policy to provide 17 paid leaves for who are yet to complete his first (1) year and the day he completes his first year, from the very next day the employee is eligible for 42 leaves. That's I need to know on exactly which date one completes his first year.

Hope I am able to express my thoughts properly.

[ufopilot]

test.MonthlyAttendance.xlsx

Sorry, I guess I did not understand you the first time...
Please see if this is what you meant

[ufopilot]

Hi again,
This version has the formula embedded into the conditional formatting, instead in the cells. I'm not sure if you knew how to do this, so I did it for you in this version

[arunabha]

Dear Sir,

You are very close. But the formulas in all the cell will prevent me from marking the day if the employee is present or absent.
Can the same formula be placed in the conditional formatting so that I can record whether the employee is absent or present and also gives me an indication of his year completion. Please see, Sir.

Thank you

[ufopilot]

I have lready posted the one with the formula in the formatting...

[arunabha]

Oh !! Lovely !!!

You made my day. . . .Thank you very much Sir.
I salute your command over the subject and I heartily respect the same.

Thank you Sir. Falling short of my words to express my feelings. Thank you again.

[ufopilot]

You are welcome

[arunabha]

Dear Sir,

Can you place a formula in the conditional formatting which will check the date rather than day. The present syntax is figuring out the day of the completion date. But the requirement is if the dates do match, then only the cells should go orange !! ??

[ufopilot]

Yes, I changed the formula that just checkd the day of the month to the entire date by comparing the whole date

[arunabha]

Dear Sir,

Last request on this thread. Can you make an arrangement, so that I do not require column 'E' to calculate the date of year completion.
Rather, the conditional formatting itself calculates the date.

[ufopilot]

I have placed the formula in the conditional formatting so as to eliminate column E.
However, personally I would prefer to just hide the column, so that if there is a question on what date the formula is applied, it is easier to look and compare the two dates (as you have it now) then to do the calculations first...

[arunabha]

Thank you for all your support.