Change formulas in a column based on a selection.

E

Ephraim

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
 
L

Luke M

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.
 
G

Gord Dibben

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
 
E

Ephraim

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top