Copy Down Formula Equivalent By Using Conditional Formatting Instead

censo

Board Regular
Joined
Dec 25, 2015
Messages
110
Greetings all,

I'm looking for assistance in auto-populating a function in column B using Conditional Formatting (CF) as new rows of data are entered into Column A.

The current solution in place is: when data is entered into column A, I have to pull/drag/copy down the function from the last cell of functions above in column B down to the last row of data entered from column A (usually accomplished by double-clicking the little box in the bottom right hand corner of the cell outline).

A solution I thought of using is an IF statement for column B in which the cells would appear blank until data is entered into column A. This works like a charm but I was hoping there was a way I could include the function into CF and then when data is entered into rows of column A, the formula in rows of column B would automagically populate.

The problem I'm running into is relative cell reference. I don't know how to reference individual current cells in the function for only when the adjacent cell receives data (in column A).

In the diagram below, the hyperlink which appears in cell B1 is the result of a direct function in that cell. The goal is for that function to fire off when data appears in column A but only for the rows that actually contain data.

Is this possible?

Thanks!

-C


<b>Excel 2013 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-right: 1px solid black;;">6121652637168</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">6121652-637168</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">6141653579692</td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=HYPERLINK(<font color="#0000FF">"https://www.abc.com/account/order/"&A1,TEXT(<font color="#FF0000">A1,"0000000-000000"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,165
Office Version
  1. 2010
As the name says, CF only changes formatting. You cannot add words or formula with it.

I suppose in B1 you are already using=IF(A1="","",HYPERLINK(....))

You can always enter the value in A1 and this formula in B1 then select these cells - open Insert ribbon then select Table.
Now, each time you add a value in col A, col B will extend the formula without intervention
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
110
"You can always enter the value in A1 and this formula in B1 then select these cells - open Insert ribbon then select Table."

Ohh, I like where you're going with this. I wonder if a dynamic named range would be more fluid/scalable as the data grows. What do you think?
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,165
Office Version
  1. 2010
Nope, it's not necessary. Table will expand automatically as long as you don't leave any blank rows and formulas will follow
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,738
Members
413,929
Latest member
Hypatia

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
Top