Equation Selector

G

Guest

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.
 
N

Niek Otten

In M1:

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

Copy down as far as needed
 
G

Guest

zephyr said:
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.
 
G

Guest

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
 
N

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
 
G

Guest

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
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top