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

CubeMonkey

New Member
Joined
Nov 7, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
🤓 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🐒
 

Attachments

  • CubeMonkey Co.PNG
    CubeMonkey Co.PNG
    51 KB · Views: 18
  • CubeMonkey Co 2.PNG
    CubeMonkey Co 2.PNG
    49.6 KB · Views: 14

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
Excel Formula:
=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.
 
Upvote 0
Solution
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.
Excel Formula:
=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🐒
 
Upvote 0
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.
Excel Formula:
=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🐒
 

Attachments

  • WithoutCode.PNG
    WithoutCode.PNG
    831 bytes · Views: 4
  • WithCode.PNG
    WithCode.PNG
    539 bytes · Views: 5
Upvote 0
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.
 
Upvote 0
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🐒
 
Upvote 0
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.
 
Upvote 0
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🐒
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top