In Excel need to copy a range of cells based on single criteria

I am trying to copy/insert a range of cells based on a single criteria. I have tried using the IF function as follows

=IF(B6="RHH1",Sheet2!A4:J15,Sheet2!A18)

The FALSE statement works and the single cell is copied, but the TRUE statement fails with a #Value error.

I am guessing a macro would work, but not sure on even where to start there.

3 Answers

If you are interested to achieve this without using Macro, then you need to enter your range value as an Array formula. In order to enter as an array formula,

1. Select the destination range as the same size as the source range
2. Enter the formula
3. Press Control + Shift + Enter

Refer the Microsoft Documentation Introducing array formulas in Excel

The problem here is that the function (and any function) is just trying to set that single cell (which is why it starts with =) to a value. It doesn't have control of any of the cells around it.

A macro would work, Here is a good starting point to learning about macros.

Solution in two steps:

1. Enter the following in top-left cell of desired output range:

   =IF($B$6="RHH1",Sheet2!A4,"")

   Fill this down 11 more rows and then over 9 more columns.

2. Go back to the top-left cell (the first one you entered the formula into) and change its formula to the following:

   =IF($B$6="RHH1",Sheet2!A4,Sheet2!A18)

This should do the trick.