I want to created a named range, which reorders a few columns and adds a new column with a static value in each row. Below is the formula I am trying to use for the new named range:
='Monthly Summary'!$S$7:$S$9,{"Income";"Income";"Income"},'Monthly Summary'!$R$7:$R$9,'Monthly Summary'!$V$7:$V$9
I am collating 3 rows of data from 3 columns but adding a new column with the value "Income" for each row. The formula will not save, what am I doing wrong?
The output from the named range should look like this:
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| S7 Value | Income | R7 Value | V7 Value |
| S8 Value | Income | R8 Value | V8 Value |
| S9 Value | Income | R9 Value | V9 Value |
Of course, HSTACK works as described in DS_London's comment. But (sometimes) there is the awkwardness of creating a full array for that inserted static column.
CHOOSE can handle that with the use of a single instance of the desired static element inside the array constant. So like this:
=CHOOSE( {2,4,1,3}, 'Monthly Summary'!$R$7:$R$9,'Monthly Summary'!$S$7:$S$9,'Monthly Summary'!$V$7:$V$9,{"Income"} )
which would take the columns as shown and give them back as column S-Income-column R-column V but only ever have to use that single element array constant ({"Income"}) rather than create one that has enough (and only enough) elements to fill the required rows.
Additionally, by arranging the columns using its first parameter, one might find listing columns left-to-right as found in the spreadsheet (R-S-V) easier than taking care to list them in the desired order, which admittedly might work most nicely when selecting them via mouse and not be so much of a concern when typing them instead.