Equation Selector

[zephyr]

My experience with Excel is very limited. I want to be able to use an Excel
spreadsheet to make various selections. For instance column B is used as the
selection cell, equations are in column C. If I put the selection number in
column B, I want the selected equation to be put in a different column.

For example

Cells A1 to A40 are numbered 1 to 40.

Cells C1 to C40 each contain an equation.

I want to use Cells B1 to B40 to type in the selection number.

For instance, I want to select the equations in C5, C11, C20, C33 and C38.

So for my selections I type number 1 in B5
number 2 in B11
number 3 in B20
number 4 in B33
number 5 in B38

The equations in C5, C11, C20, C33 and C38 are put in the selected order
into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.

For example:

A B C
1 1 A+B+C
2 2 2A+2B+2D
3 3 4y+4x+3w+4y+4x
4 4 4y+4x+3w+4y
5 5 1 y+4x+7w
6 6 4y+10x+3w
7 7 4y+3x+3w
8 8 11y+4x+3w
9 9 9y+4x+3w
10 10 4y+4x+14w
11 11 2 y+7x+3w


Any help gratefully accepted.

[Niek Otten]

In M1:

=IF(ISNA(VLOOKUP(ROW(),B1:C11,2,FALSE)),"",VLOOKUP(ROW(),B1:C11,2,FALSE))

Copy down as far as needed

--
Kind regards,

Niek Otten

"zephyr" <[email protected]> wrote in message
news:[email protected]...
> My experience with Excel is very limited. I want to be able to use an
> Excel
> spreadsheet to make various selections. For instance column B is used as
> the
> selection cell, equations are in column C. If I put the selection number
> in
> column B, I want the selected equation to be put in a different column.
>
> For example
>
> Cells A1 to A40 are numbered 1 to 40.
>
> Cells C1 to C40 each contain an equation.
>
> I want to use Cells B1 to B40 to type in the selection number.
>
> For instance, I want to select the equations in C5, C11, C20, C33 and C38.
>
> So for my selections I type number 1 in B5
> number 2 in B11
> number 3 in B20
> number 4 in B33
> number 5 in B38
>
> The equations in C5, C11, C20, C33 and C38 are put in the selected order
> into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.
>
> For example:
>
> A B C
> 1 1 A+B+C
> 2 2 2A+2B+2D
> 3 3 4y+4x+3w+4y+4x
> 4 4 4y+4x+3w+4y
> 5 5 1 y+4x+7w
> 6 6 4y+10x+3w
> 7 7 4y+3x+3w
> 8 8 11y+4x+3w
> 9 9 9y+4x+3w
> 10 10 4y+4x+14w
> 11 11 2 y+7x+3w
>
>
> Any help gratefully accepted.
>

[zephyr]

"zephyr" wrote:

> My experience with Excel is very limited. I want to be able to use an Excel
> spreadsheet to make various selections. For instance column B is used as the
> selection cell, equations are in column C. If I put the selection number in
> column B, I want the selected equation to be put in a different column.
>
> For example
>
> Cells A1 to A40 are numbered 1 to 40.
>
> Cells C1 to C40 each contain an equation.
>
> I want to use Cells B1 to B40 to type in the selection number.
>
> For instance, I want to select the equations in C5, C11, C20, C33 and C38.
>
> So for my selections I type number 1 in B5
> number 2 in B11
> number 3 in B20
> number 4 in B33
> number 5 in B38
>
> The equations in C5, C11, C20, C33 and C38 are put in the selected order
> into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.
>
> For example:
>
> A B C
> 1 1 A+B+C
> 2 2 2A+2B+2D
> 3 3 4y+4x+3w+4y+4x
> 4 4 4y+4x+3w+4y
> 5 5 1 y+4x+7w
> 6 6 4y+10x+3w
> 7 7 4y+3x+3w
> 8 8 11y+4x+3w
> 9 9 9y+4x+3w
> 10 10 4y+4x+14w
> 11 11 2 y+7x+3w
>
>
> Any help gratefully accepted.
>

[zephyr]

The formula when typed in M1 correctly selects C5 and puts the contents of
cell C5 into M1. In order for the second selection in C11 to go into M2 the
formula has to be put into M2.

Is there a way of putting the selections into column M without having to put
the formula into each cell in the column as there may be a lot more
selections than 1 to 5.

Many thanks


"zephyr" wrote:

> My experience with Excel is very limited. I want to be able to use an Excel
> spreadsheet to make various selections. For instance column B is used as the
> selection cell, equations are in column C. If I put the selection number in
> column B, I want the selected equation to be put in a different column.
>
> For example
>
> Cells A1 to A40 are numbered 1 to 40.
>
> Cells C1 to C40 each contain an equation.
>
> I want to use Cells B1 to B40 to type in the selection number.
>
> For instance, I want to select the equations in C5, C11, C20, C33 and C38.
>
> So for my selections I type number 1 in B5
> number 2 in B11
> number 3 in B20
> number 4 in B33
> number 5 in B38
>
> The equations in C5, C11, C20, C33 and C38 are put in the selected order
> into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.
>
> For example:
>
> A B C
> 1 1 A+B+C
> 2 2 2A+2B+2D
> 3 3 4y+4x+3w+4y+4x
> 4 4 4y+4x+3w+4y
> 5 5 1 y+4x+7w
> 6 6 4y+10x+3w
> 7 7 4y+3x+3w
> 8 8 11y+4x+3w
> 9 9 9y+4x+3w
> 10 10 4y+4x+14w
> 11 11 2 y+7x+3w
>
>
> Any help gratefully accepted.
>

[Niek Otten]

<Copy down as far as needed>

is really the simplest way to achieve that.
Select M1, hold down the SHIFT key while arrowing (or even paging) down,
release the SHIFT key, press CTRL+D

--
Kind regards,

Niek Otten

"zephyr" <[email protected]> wrote in message
news:[email protected]...
> The formula when typed in M1 correctly selects C5 and puts the contents of
> cell C5 into M1. In order for the second selection in C11 to go into M2
> the
> formula has to be put into M2.
>
> Is there a way of putting the selections into column M without having to
> put
> the formula into each cell in the column as there may be a lot more
> selections than 1 to 5.
>
> Many thanks
>
>
> "zephyr" wrote:
>
>> My experience with Excel is very limited. I want to be able to use an
>> Excel
>> spreadsheet to make various selections. For instance column B is used as
>> the
>> selection cell, equations are in column C. If I put the selection number
>> in
>> column B, I want the selected equation to be put in a different column.
>>
>> For example
>>
>> Cells A1 to A40 are numbered 1 to 40.
>>
>> Cells C1 to C40 each contain an equation.
>>
>> I want to use Cells B1 to B40 to type in the selection number.
>>
>> For instance, I want to select the equations in C5, C11, C20, C33 and
>> C38.
>>
>> So for my selections I type number 1 in B5
>> number 2 in B11
>> number 3 in B20
>> number 4 in B33
>> number 5 in B38
>>
>> The equations in C5, C11, C20, C33 and C38 are put in the selected order
>> into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.
>>
>> For example:
>>
>> A B C
>> 1 1 A+B+C
>> 2 2 2A+2B+2D
>> 3 3 4y+4x+3w+4y+4x
>> 4 4 4y+4x+3w+4y
>> 5 5 1 y+4x+7w
>> 6 6 4y+10x+3w
>> 7 7 4y+3x+3w
>> 8 8 11y+4x+3w
>> 9 9 9y+4x+3w
>> 10 10 4y+4x+14w
>> 11 11 2 y+7x+3w
>>
>>
>> Any help gratefully accepted.
>>

[zephyr]

Once again many thanks for your assistance youre solution works well.

"Niek Otten" wrote:

> <Copy down as far as needed>
>
> is really the simplest way to achieve that.
> Select M1, hold down the SHIFT key while arrowing (or even paging) down,
> release the SHIFT key, press CTRL+D
>
> --
> Kind regards,
>
> Niek Otten
>
> "zephyr" <[email protected]> wrote in message
> news:[email protected]...
> > The formula when typed in M1 correctly selects C5 and puts the contents of
> > cell C5 into M1. In order for the second selection in C11 to go into M2
> > the
> > formula has to be put into M2.
> >
> > Is there a way of putting the selections into column M without having to
> > put
> > the formula into each cell in the column as there may be a lot more
> > selections than 1 to 5.
> >
> > Many thanks
> >
> >
> > "zephyr" wrote:
> >
> >> My experience with Excel is very limited. I want to be able to use an
> >> Excel
> >> spreadsheet to make various selections. For instance column B is used as
> >> the
> >> selection cell, equations are in column C. If I put the selection number
> >> in
> >> column B, I want the selected equation to be put in a different column.
> >>
> >> For example
> >>
> >> Cells A1 to A40 are numbered 1 to 40.
> >>
> >> Cells C1 to C40 each contain an equation.
> >>
> >> I want to use Cells B1 to B40 to type in the selection number.
> >>
> >> For instance, I want to select the equations in C5, C11, C20, C33 and
> >> C38.
> >>
> >> So for my selections I type number 1 in B5
> >> number 2 in B11
> >> number 3 in B20
> >> number 4 in B33
> >> number 5 in B38
> >>
> >> The equations in C5, C11, C20, C33 and C38 are put in the selected order
> >> into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.
> >>
> >> For example:
> >>
> >> A B C
> >> 1 1 A+B+C
> >> 2 2 2A+2B+2D
> >> 3 3 4y+4x+3w+4y+4x
> >> 4 4 4y+4x+3w+4y
> >> 5 5 1 y+4x+7w
> >> 6 6 4y+10x+3w
> >> 7 7 4y+3x+3w
> >> 8 8 11y+4x+3w
> >> 9 9 9y+4x+3w
> >> 10 10 4y+4x+14w
> >> 11 11 2 y+7x+3w
> >>
> >>
> >> Any help gratefully accepted.
> >>
>
>
>

[zephyr]

The formula puts the selection on the same worksheet. Can you advise how I
can get the selection to go onto another worksheet or into a different
workbook.

When the formula goes into another worksheet or workbook is it possible to
remove the formula and leave the selections behind.

This is required so that I can email the contents of the new workbook.

Many thanks

"zephyr" wrote:

> My experience with Excel is very limited. I want to be able to use an Excel
> spreadsheet to make various selections. For instance column B is used as the
> selection cell, equations are in column C. If I put the selection number in
> column B, I want the selected equation to be put in a different column.
>
> For example
>
> Cells A1 to A40 are numbered 1 to 40.
>
> Cells C1 to C40 each contain an equation.
>
> I want to use Cells B1 to B40 to type in the selection number.
>
> For instance, I want to select the equations in C5, C11, C20, C33 and C38.
>
> So for my selections I type number 1 in B5
> number 2 in B11
> number 3 in B20
> number 4 in B33
> number 5 in B38
>
> The equations in C5, C11, C20, C33 and C38 are put in the selected order
> into a column M1, M2, M3, M4, M5 and M6 on the same or another worksheet.
>
> For example:
>
> A B C
> 1 1 A+B+C
> 2 2 2A+2B+2D
> 3 3 4y+4x+3w+4y+4x
> 4 4 4y+4x+3w+4y
> 5 5 1 y+4x+7w
> 6 6 4y+10x+3w
> 7 7 4y+3x+3w
> 8 8 11y+4x+3w
> 9 9 9y+4x+3w
> 10 10 4y+4x+14w
> 11 11 2 y+7x+3w
>
>
> Any help gratefully accepted.
>

[Niek Otten]

Instead of just B1:C11

[BookName]SheetName!$B$1:$C$11

Or, if there are spaces in the sheetname:

'[BookName]Sheet Name'!$B$1:$C$11

--
Kind regards,

Niek Otten

"zephyr" <[email protected]> wrote in message
news:[email protected]...
> Once again many thanks for your assistance youre solution works well.
>
> "Niek Otten" wrote:
>
>> <Copy down as far as needed>
>>
>> is really the simplest way to achieve that.
>> Select M1, hold down the SHIFT key while arrowing (or even paging) down,
>> release the SHIFT key, press CTRL+D
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "zephyr" <[email protected]> wrote in message
>> news:[email protected]...
>> > The formula when typed in M1 correctly selects C5 and puts the contents
>> > of
>> > cell C5 into M1. In order for the second selection in C11 to go into M2
>> > the
>> > formula has to be put into M2.
>> >
>> > Is there a way of putting the selections into column M without having
>> > to
>> > put
>> > the formula into each cell in the column as there may be a lot more
>> > selections than 1 to 5.
>> >
>> > Many thanks
>> >
>> >
>> > "zephyr" wrote:
>> >
>> >> My experience with Excel is very limited. I want to be able to use an
>> >> Excel
>> >> spreadsheet to make various selections. For instance column B is used
>> >> as
>> >> the
>> >> selection cell, equations are in column C. If I put the selection
>> >> number
>> >> in
>> >> column B, I want the selected equation to be put in a different
>> >> column.
>> >>
>> >> For example
>> >>
>> >> Cells A1 to A40 are numbered 1 to 40.
>> >>
>> >> Cells C1 to C40 each contain an equation.
>> >>
>> >> I want to use Cells B1 to B40 to type in the selection number.
>> >>
>> >> For instance, I want to select the equations in C5, C11, C20, C33 and
>> >> C38.
>> >>
>> >> So for my selections I type number 1 in B5
>> >> number 2 in B11
>> >> number 3 in B20
>> >> number 4 in B33
>> >> number 5 in B38
>> >>
>> >> The equations in C5, C11, C20, C33 and C38 are put in the selected
>> >> order
>> >> into a column M1, M2, M3, M4, M5 and M6 on the same or another
>> >> worksheet.
>> >>
>> >> For example:
>> >>
>> >> A B C
>> >> 1 1 A+B+C
>> >> 2 2 2A+2B+2D
>> >> 3 3 4y+4x+3w+4y+4x
>> >> 4 4 4y+4x+3w+4y
>> >> 5 5 1 y+4x+7w
>> >> 6 6 4y+10x+3w
>> >> 7 7 4y+3x+3w
>> >> 8 8 11y+4x+3w
>> >> 9 9 9y+4x+3w
>> >> 10 10 4y+4x+14w
>> >> 11 11 2 y+7x+3w
>> >>
>> >>
>> >> Any help gratefully accepted.
>> >>
>>
>>
>>