Can nobody help me?

[John]

This might be hard to explain, but here goes nothing.
I am creating a quote template which will consist of many
worksheets, which once completed only one sheet is visible
to the customer. There are 3 levels of service the rep can
select, from the "Options" Sheet. Each Level can contain
additional options. For Example: If Level one is selected,
then:

A1 B1
1 Option 1 (Y/N)
2 Option 2 (Y/N)
3 Option 3 (Y/N)
4 Option 4 (Y/N)
5 Option 5 (Y/N)

If Level 2 is selected, then it might look like:

A1 B1
1 Option 2 (Y/N)
2 Option 4 (Y/N)
3 Option 6 (Y/N)
4 Option 7 (Y/N)
5 Option 8 (Y/N)

The problem that I run into is if the user selects an
option, it will then appear on the "Quote" Sheet. But it
will not show up line after line. For Example if Options
2, 6, 8 are selected (from level 2) on the "Options"
sheet, the "Quote" Sheet will display it as:

F1
1 Option 2
2
3 Option 6
4
5 Option 8

Since the quote is for the customer, I need it to look
like:

F1
1 Option 2
2 Option 6
3 Option 8
4
5

I have tried using IF Statements to try to get this to
work, but it's not. Please help!! Thanks.


..

 

[RP]

Are you using strictly Excel functions or are you using VBA in the
background?

 

[john]

Strictly Excel. I am not familiar with using VBA.

 

[JE McGimpsey]

one way:

on the quote sheet, F1, array-enter:

=IF(SMALL(IF(Options!$B$1:$B$5="Y", ROW(Options!$B$1:$B$5), 65537)
ROW())>65536, "", INDEX($A:$A, SMALL(IF(Options!$B$1:$B$5="Y",
ROW(Options!$B$1:$B$5), 65537), ROW())))

If you start on a different row, you'll need to adjust the last term.

 

[RP]

Using just Excel, it is going to be difficult. Without seeing what and how
you are doing it going to make it hard to help.
Anyway you can provide more detail?

 

[Tom]

-----Original Message-----


This might be hard to explain, but here goes nothing.
I am creating a quote template which will consist of many
worksheets, which once completed only one sheet is visible
to the customer. There are 3 levels of service the rep can
select, from the "Options" Sheet. Each Level can contain
additional options. For Example: If Level one is selected,
then:

A1 B1
1 Option 1 (Y/N)
2 Option 2 (Y/N)
3 Option 3 (Y/N)
4 Option 4 (Y/N)
5 Option 5 (Y/N)

If Level 2 is selected, then it might look like:

A1 B1
1 Option 2 (Y/N)
2 Option 4 (Y/N)
3 Option 6 (Y/N)
4 Option 7 (Y/N)
5 Option 8 (Y/N)

The problem that I run into is if the user selects an
option, it will then appear on the "Quote" Sheet. But it
will not show up line after line. For Example if Options
2, 6, 8 are selected (from level 2) on the "Options"
sheet, the "Quote" Sheet will display it as:

F1
1 Option 2
2
3 Option 6
4
5 Option 8

Since the quote is for the customer, I need it to look
like:

F1
1 Option 2
2 Option 6
3 Option 8
4
5

I have tried using IF Statements to try to get this to
work, but it's not. Please help!! Thanks.


..


.
You can use a little VBA to solve this.

Step through the range of options in a loop to find "Yes"
values. When you get a "Yes" value, append a variable,
say "i". Fill in an array ("arrayYes") with each
resulting i and then write that back to the sheet area.
It should look something like below, although I am not
testing it and doing it "off the cuff".
For each cell in [Options]
If cell="Yes" then
i=i+1
arrayYes(i,1)=cell
end if
Next cell
range("a1").resize(i,1)=arrayYes
I always assign every range to an array first and then
proceed. Therefore, I do not know if this is the correct
form of code for "pure ranges" but it's worth looking
into.

 

[Guest]

-----Original Message-----

-----Original Message-----


This might be hard to explain, but here goes nothing.
I am creating a quote template which will consist of many
worksheets, which once completed only one sheet is visible
to the customer. There are 3 levels of service the rep can
select, from the "Options" Sheet. Each Level can contain
additional options. For Example: If Level one is selected,
then:

A1 B1
1 Option 1 (Y/N)
2 Option 2 (Y/N)
3 Option 3 (Y/N)
4 Option 4 (Y/N)
5 Option 5 (Y/N)

If Level 2 is selected, then it might look like:

A1 B1
1 Option 2 (Y/N)
2 Option 4 (Y/N)
3 Option 6 (Y/N)
4 Option 7 (Y/N)
5 Option 8 (Y/N)

The problem that I run into is if the user selects an
option, it will then appear on the "Quote" Sheet. But it
will not show up line after line. For Example if Options
2, 6, 8 are selected (from level 2) on the "Options"
sheet, the "Quote" Sheet will display it as:

F1
1 Option 2
2
3 Option 6
4
5 Option 8

Since the quote is for the customer, I need it to look
like:

F1
1 Option 2
2 Option 6
3 Option 8
4
5

I have tried using IF Statements to try to get this to
work, but it's not. Please help!! Thanks.


..


.
You can use a little VBA to solve this.

Step through the range of options in a loop to find "Yes"
values. When you get a "Yes" value, append a variable,
say "i". Fill in an array ("arrayYes") with each
resulting i and then write that back to the sheet area.
It should look something like below, although I am not
testing it and doing it "off the cuff".
For each cell in [Options]
If cell="Yes" then
i=i+1
arrayYes(i,1)=cell
end if
Next cell
range("a1").resize(i,1)=arrayYes
I always assign every range to an array first and then
proceed. Therefore, I do not know if this is the correct
form of code for "pure ranges" but it's worth looking
into.
.
There is another way to do this when I think about it

further. If you put in IF statement in cells d1 to d5
that are: d1=if(b1=Yes", 1,""),d2=if(b2="Yes",2,"")
d3=if(b3="Yes",3,"") etc.
Now, in cells e1 to e5 put in the following formulae:
e1=if(iserror(small(a$1:a$5,1)),"",small(a$1:a$5,1))
e2=if(iserror(small(a$1:a$5,2)),"",small(a$1:a$5,2))
e3=if(iserror(small(a$1:a$5,3)),"",small(a$1:a$5,3))
etc. This will find the instances of where the "yes"
occurs and put them in sequence regardless of where they
occur. I tried it and it works.