Possible Conditional Formatting use, to pull text from one cell to another.

[CubeMonkey]

Good day my fellow nerds,

I need help so I came here to see what Master Nerd could be so kind to solve this issue I am having.


What I am trying to do:
Using Conditional Formatting, I would like the = function to pull text from one cell to another based on a Start Date.

Example:

1. Text in Cell B11 is "Feed them cheap banana's".
2. I would like the text in that cell to be displayed in Cell M11 [Attachment: CubeMonkey Co.PNG]
3. I'm assuming this can be done somehow using the Start Date (Cell C11)
4. I am looking to display the text over the color on this Gantt Chart Calendar.

Issue:

1. I have no idea how to do this.
2. Cell C4 changes the week view. If any other week is selected I would like the text to change also in correlation to the Start Date. [Attachment: CubeMonkey Co 2.PNG]
3. Please see the included spreadsheet.

Note:
I tried uploading a Mini-Sheet and it would not work. If you would like the actual file like me know and I can send it somehow.


Please help!

-CubeMonkey

 

[6StringJazzer]

Conditional formatting is unrelated to what you are trying to do.

You have a problem here. The problem is that to do this, you will need a formula in every cell starting in G9 and filled to every cell to the right and down. However, if you do that, the contents of any cell will not extend to the right into adjoining cells as you show in your example. It will be confined to one cell.

Just for the record this formula would do it, but it won't display how you want it. This assumes that row 5 contains actual date values custom formatted as "d". If that is not the case, you have to do that first to do any of this.

=IF(G$5=$C9,B9,"")

I think this is going to need to be a VBA solution that inserts the text anytime you have a change in column B or C.

 

[CubeMonkey]

Conditional formatting is unrelated to what you are trying to do.

You have a problem here. The problem is that to do this, you will need a formula in every cell starting in G9 and filled to every cell to the right and down. However, if you do that, the contents of any cell will not extend to the right into adjoining cells as you show in your example. It will be confined to one cell.

Just for the record this formula would do it, but it won't display how you want it. This assumes that row 5 contains actual date values custom formatted as "d". If that is not the case, you have to do that first to do any of this.

=IF(G$5=$C9,B9,"")

I think this is going to need to be a VBA solution that inserts the text anytime you have a change in column B or C.

6SJ,

What would you recommend would be the best way oh wise one?

If VBA, how?

I appreciate the help; I have passion for excel but still have a lot to learn.


-CubeMonkey

 

[CubeMonkey]

Conditional formatting is unrelated to what you are trying to do.

You have a problem here. The problem is that to do this, you will need a formula in every cell starting in G9 and filled to every cell to the right and down. However, if you do that, the contents of any cell will not extend to the right into adjoining cells as you show in your example. It will be confined to one cell.

Just for the record this formula would do it, but it won't display how you want it. This assumes that row 5 contains actual date values custom formatted as "d". If that is not the case, you have to do that first to do any of this.

=IF(G$5=$C9,B9,"")

I think this is going to need to be a VBA solution that inserts the text anytime you have a change in column B or C.

6SJ,

I tried the =IF(G$5=$C9,B9,"") Code, very cool trick indeed!

Only issue is when I use it the other cell will block out the Text. [Please see attached picture for reference.]

Is there a way around this to display all of the Text in B9? and if so, what would be the best way to input this throughout all the cells?



P.S. I'm so impressed with how smart everyone is with this stuff, very inspiring.

-CubeMonkey

 

[6StringJazzer]

That's what I meant when I said

the contents of any cell will not extend to the right into adjoining cells as you show in your example. It will be confined to one cell.

A cell with text that is wider than the cell will extend in the cells to the right, but only if the cells on the right are empty. If the cells to the right have formulas, even if the results of those formulas are blanks, they are not considered empty.

I think this requires VBA. I don't think it would take to me too long to do it but I'll have to find the time first. If you are not experienced with VBA then I will have to explain how to install it and how it works. All of this will take a little time. Meantime someone else may be able to do it faster.

 

[CubeMonkey]

That's what I meant when I said

A cell with text that is wider than the cell will extend in the cells to the right, but only if the cells on the right are empty. If the cells to the right have formulas, even if the results of those formulas are blanks, they are not considered empty.

I think this requires VBA. I don't think it would take to me too long to do it but I'll have to find the time first. If you are not experienced with VBA then I will have to explain how to install it and how it works. All of this will take a little time. Meantime someone else may be able to do it faster.

I understand now, I appreciate the clarification.

I will dive in to learn VBA as I'm a newbie. I wish Excel had a formula layering function so we could layer formula to override stuff like that. Noticed there are a few other people online asking about doing the same. I'll have to get cleaver and think of a way to accomplish this.

Is there a formula I can put somewhere for projecting text into a cell with no formula in that cell?
Example: =IF(TaskDate=CalendarDate, Then Project to Collom of Same Date and Same Row Task with TaskName,"")

Please be kind, I'm trying


-CubeMonkey

 

[6StringJazzer]

No, a formula cannot "project" a value into a different cell*. An Excel formula is an expression to calculate a value, and the resulting value belongs to the cell that has the formula.

______________________________
*Array formulas technically display results in cells adjacent to the one containing the formula, but that is totally different than what we are talking about here.

 

[CubeMonkey]

No, a formula cannot "project" a value into a different cell*. An Excel formula is an expression to calculate a value, and the resulting value belongs to the cell that has the formula.

______________________________
*Array formulas technically display results in cells adjacent to the one containing the formula, but that is totally different than what we are talking about here.

Thank you for your time. Much appreciated.

-CubeMonkey