![]() |
Change formulas in a column based on a selection.
I have checkboxes from the forms toolbar in Column A linked to
corresponding Cells in Column B. I have this formula in the corresponding cell in Column E =IF($B2,"Computer 1","") So when checkbox A2 is checked it displays "Computer 1" in Cell E2 I have this formula in the corresponding cell in Column F =IF($B2,I2,"") So when checkbox A2 is checked it displays the value in Cell I2. What I'd like to accomplish is to have a way to select the heading for Column I, J, K, L or M so that all the formulas in column F would change based on that selection. i.e. The formulas in Column F would change to =IF($B2,I2,"") 'If column I was selected =IF($B2,J2,"") 'If column J was selected =IF($B2,K2,"") 'If column K was selected =IF($B2,L2,"") 'If column L was selected =IF($B2,M2,"") 'If column M was selected What I am currently doing is changing the formula in Cell F and dragging it down to replace all the other formulas. A macro to change the formulas would be much easier. Thanks, Ephraim |
Change formulas in a column based on a selection.
By "selecting the column" I'll assume you use a dropdown, (which has a
integer output). If you link this to cell B3, your single formula is then: =IF($B2,OFFSET(I2,,B3-1),"") As the number in B3 changes, the cell that is returned by this formula will change. -- Best Regards, Luke M "Ephraim" wrote in message ... I have checkboxes from the forms toolbar in Column A linked to corresponding Cells in Column B. I have this formula in the corresponding cell in Column E =IF($B2,"Computer 1","") So when checkbox A2 is checked it displays "Computer 1" in Cell E2 I have this formula in the corresponding cell in Column F =IF($B2,I2,"") So when checkbox A2 is checked it displays the value in Cell I2. What I'd like to accomplish is to have a way to select the heading for Column I, J, K, L or M so that all the formulas in column F would change based on that selection. i.e. The formulas in Column F would change to =IF($B2,I2,"") 'If column I was selected =IF($B2,J2,"") 'If column J was selected =IF($B2,K2,"") 'If column K was selected =IF($B2,L2,"") 'If column L was selected =IF($B2,M2,"") 'If column M was selected What I am currently doing is changing the formula in Cell F and dragging it down to replace all the other formulas. A macro to change the formulas would be much easier. Thanks, Ephraim |
Change formulas in a column based on a selection.
To change formulas in F to activecell column reference.
Sub Auto_Fill() Dim lRow As Long Dim actcol As Long actcol = ActiveCell.Column With ActiveSheet .Range("F1").FormulaR1C1 = "=IF(R[1]C2,R[1]C[" & _ actcol - 6 & "],"""")" lRow = .Range("F" & Rows.Count).End(xlUp).Row .Range("F1:F" & lRow).FillDown End With End Sub Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 07:55:08 -0700 (PDT), Ephraim wrote: I have checkboxes from the forms toolbar in Column A linked to corresponding Cells in Column B. I have this formula in the corresponding cell in Column E =IF($B2,"Computer 1","") So when checkbox A2 is checked it displays "Computer 1" in Cell E2 I have this formula in the corresponding cell in Column F =IF($B2,I2,"") So when checkbox A2 is checked it displays the value in Cell I2. What I'd like to accomplish is to have a way to select the heading for Column I, J, K, L or M so that all the formulas in column F would change based on that selection. i.e. The formulas in Column F would change to =IF($B2,I2,"") 'If column I was selected =IF($B2,J2,"") 'If column J was selected =IF($B2,K2,"") 'If column K was selected =IF($B2,L2,"") 'If column L was selected =IF($B2,M2,"") 'If column M was selected What I am currently doing is changing the formula in Cell F and dragging it down to replace all the other formulas. A macro to change the formulas would be much easier. Thanks, Ephraim |
Change formulas in a column based on a selection.
On Apr 1, 12:01*pm, Gord Dibben <gorddibbATshawDOTca wrote:
To change formulas in F to activecell column reference. Sub Auto_Fill() Dim lRow As Long Dim actcol As Long actcol = ActiveCell.Column * * With ActiveSheet * * .Range("F1").FormulaR1C1 = "=IF(R[1]C2,R[1]C[" & _ * * * * actcol - 6 & "],"""")" * * * * lRow = .Range("F" & Rows.Count).End(xlUp).Row * * * * .Range("F1:F" & lRow).FillDown * * End With End Sub Gord Dibben *MS Excel MVP On Thu, 1 Apr 2010 07:55:08 -0700 wrote: I have checkboxes from the forms toolbar in Column A linked to corresponding Cells in Column B. I have this formula in the corresponding cell in Column E =IF($B2,"Computer 1","") So when checkbox A2 is checked it displays "Computer 1" in Cell E2 I have this formula in the corresponding cell in Column F =IF($B2,I2,"") So when checkbox A2 is checked it displays the value in Cell I2. What I'd like to accomplish is to have a way to select the heading for Column I, J, K, L or M so that all the formulas in column F would change based on that selection. i.e. The formulas in Column F would change to =IF($B2,I2,"") * *'If column I was selected =IF($B2,J2,"") * 'If column J was selected =IF($B2,K2,"") * 'If column K was selected =IF($B2,L2,"") * 'If column L was selected =IF($B2,M2,"") *'If column M was selected What I am currently doing is changing the formula in Cell F and dragging it down to replace all the other formulas. A macro to change the formulas would be much easier. Thanks, Ephraim How can I change this so that the formula starts at F2 instead of F1. As it currently is this macro replaces the label I have in F1. Otherwise this is exactly as I had wanted. Thank you. |
All times are GMT +1. The time now is 05:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com