Copy Down Formula Equivalent By Using Conditional Formatting Instead

censo

Board Regular
Joined
Dec 25, 2015
Messages
155
Office Version
  1. 2013
Platform
  1. Windows
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



Excel 2013 32 bit
AB
161216526371686121652-637168
26141653579692
Sheet1
Cell Formulas
RangeFormula
B1=HYPERLINK("https://www.abc.com/account/order/"&A1,TEXT(A1,"0000000-000000"))
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,394
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
155
Office Version
  1. 2013
Platform
  1. Windows
"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,394
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
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
155
Office Version
  1. 2013
Platform
  1. Windows
Nope, it's not necessary. Table will expand automatically as long as you don't leave any blank rows and formulas will follow

Ok - thanks for the prompt reply and your contribution :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,526
Messages
5,832,268
Members
430,124
Latest member
Potatokat

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