Two way sorting

[JShilpa]

Hi! This is Shilpa.

I have in column H2 onwards the Month-Year format
(i.e different month and year in each cell).

I wanted a code where in cell X1 when I give any Month-Year
format then it calculates and gives me how many months it has been
since the details in H2 .( i.e 10 or 20 or 33 months etc..) in the
respective cell.

And in cell Y1 if I give any range say 10-33 or single value may be 20 then from the record
available from X2 onwards it checks and makes the respective
cell red in column Y2 onwards. In case if in cell Y1 I do not need any
range I will keep it blank.

I am not sure a code or formula is suitable . But if possible will like to try both the options.

Thanks

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[JShilpa]

Thanks a lot mehmetcik for the reply.
Surely will keep this in mind.

Attaching a file which might make the picture more clear.
However if it is not clear then let me know.

Thanks again
have a nice day
Shilpa

[mehmetcik]

1. Paste this formula in X2 and fill down

Please Login or Register  to view this content.

2. Highlight column Y, Select Conditional format, Select New Rule, Use Formula and enter this formula.

Please Login or Register  to view this content.

Select the format tab and select the fill colour red.

3. Highlight column Y, Select Conditional format, Select New Rule, Use Formula and enter this formula.

Please Login or Register  to view this content.

Select the format tab and select the fill colour red.

[JShilpa]

Hello mehmetcik
Thanks for helping.
The X column is working fine.
The Y column I tried 3rd option where the colour gets red.
But is not displaying the colour in the correct cells.
When I gave 1-4 it selected the first four cells Y1:Y4 and made
red. Then if I give 1-5 where the value 5 is not in the X column even then
it selected cells Y1:Y5.
Or may be I am doing any mistake just let me know.

Thanks
Shilpa

[mehmetcik]

Conditional format1

Please Login or Register  to view this content.

Conditional Format2

Please Login or Register  to view this content.

Formula for X2:

Please Login or Register  to view this content.

[JShilpa]

Hello mehmetcik
Thanks a lot for solving my problem. The last three options are working fine.
saved my half an hour everyday.

Thanks again
see you next time.