MS Excel issues :/

Closed
Report
Posts
3
Registration date
Saturday April 3, 2010
Status
Member
Last seen
April 6, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I need to replace a range of 7 cells with a choice of another several ranges, based on the valueof (yet) another cell. For example:

Range a1:a7 values need to be replaced with either a10:a17 or b10:b17 or c10:c17 etc...

the determining reason for the range replacement selection is in b2.

I fear that the replacement ranges would be more than 64, so nesting several =IF functions together is not a viable option.

atm, the formula is something like this:

=if($b$2=1,a10,if($b$2=2,b10,if($b$2=3,c10,0)))

i then use the fill down command, keeping the "reason cell" absolute, and having a2 aligned with a11, b11, c11; having c3 aligned with a12,b12,c12 etc.

Using this method, i will run out of nesting functions..

Any ideas?

Thanks

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Macro is one option or may be use of INDIRECT and ADDRESS combination might do the trick.
Posts
3
Registration date
Saturday April 3, 2010
Status
Member
Last seen
April 6, 2010

Hey.. thanks..
I'm not sure if the INDIRECT or ADDRESS functions would work. I'm pretty sure a macro would work, but i'm not really sure where or how to start writing one.. Guess i'll have to do a crash course in VB.. :/

I'm also looking at startin a dedicated summary of all the values i need on a seperate sheet!, and using a VLOOKUP referring to the seperate sheet! on the original.. Time consuming, i know, but probably qwikker than learning VB
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Actually VB is not that hard to start. MSO has a great feature of recording macro. So you basically do it manually and it records your action. This gives a template and you can make that template more generic to fit.

Sub t()

Dim action As String

    ' get instructions
    action = Range("B2")

    ' forking based on action selected
    Select Case action
    
        ' if action is 1, then copy range A10:A17
        Case Is = 1
            Range("A10:A17").Copy
            
        ' if action is 2, then copy range B10:B17
        Case Is = 2
            Range("b10:b17").Copy
            
        ' if action is 3, then copy range C10:C17
        Case Is = 3
            Range("c10:c17").Copy
            
        ' this was just to show you that if 10-17 is always there and
        'only thing that change is column, you can tell it what column
        'to look for
         Case Is = "A"
            Range(Cells(10, "A"), Cells(17, "A")).Copy
            
        'this was just to show you that you dont have to hard code column
        'letter but pass it as parameter
        Case Else
            Range(Cells(10, action), Cells(17, action)).Copy
    End Select

    Range("A1:A7").PasteSpecial xlPasteValues
    

End Sub