MS Excel issues :/ Closed

Question

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 

Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Answer

Macro is one option or may be use of INDIRECT and ADDRESS combination might do the trick.

Utetopia · Answer

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

rizvisa1 · Answer

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

MS Excel issues :/

3 responses

Similar discussions

Subscribe To Our Newsletter!