I'm creating a list of delivery dates based on the date product was ordered
and the number of units that were ordered. I'd like the list to only show
info for the number of units ordered, i.e. if 10 units are ordered I want to
see info for all 10, if there were only 3 in the order I'd like the list to
shrink down to 3 rows so that I don't have a clutter of unnecessary info.

I've been trying variations of the following formula to get the list of # of
units to expand or shrink based on the order size:

=IF(AND(S5+1<=$Q$6,S5<""),S5+1,"")

This formula appears in cell S6 and:
S5 = cell for unit 1 and contains "1"
Q6 = # of units ordered

The idea is if the order size is at least 2 (as shown in Q6) then cell S6
would display 2, otherwise it would just stay blank.

What's happening is that this works until the first cell beyond the order
size (i.e. cell #6 for a 5 unit order shows blank) but in the subsequent
cells I get a "#VALUE" error rather than having them show blank.

It's a cosmetic thing but any help would be appreciated.

We can use the SUM function to get rid of the error caused by trying to add 1
to a text string (as SUM ignores text by default)

=IF(AND(SUM(S5,1)<=$Q$6,S5<""),S5+1,"")

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"southbaysufer" wrote:

I'm creating a list of delivery dates based on the date product was ordered
and the number of units that were ordered. I'd like the list to only show
info for the number of units ordered, i.e. if 10 units are ordered I want to
see info for all 10, if there were only 3 in the order I'd like the list to
shrink down to 3 rows so that I don't have a clutter of unnecessary info.

I've been trying variations of the following formula to get the list of # of
units to expand or shrink based on the order size:

=IF(AND(S5+1<=$Q$6,S5<""),S5+1,"")

This formula appears in cell S6 and:
S5 = cell for unit 1 and contains "1"
Q6 = # of units ordered

The idea is if the order size is at least 2 (as shown in Q6) then cell S6
would display 2, otherwise it would just stay blank.

What's happening is that this works until the first cell beyond the order
size (i.e. cell #6 for a 5 unit order shows blank) but in the subsequent
cells I get a "#VALUE" error rather than having them show blank.

It's a cosmetic thing but any help would be appreciated.

Of course as soon as I posted I found the solution. The formula was getting
choked up on what to do when it encountered " "+1<+Q6 " in the first
argument. I switched the arguments around so that it first checked to see if
the previous cell was blank and then if not told it to move on and check if
"S5+1=Q6". Works like a champ now.

Here's the new IF statement:

=IF(S5="","",IF(S5+1<=$Q$6,S5+1,""))


"southbaysufer" wrote:

I'm creating a list of delivery dates based on the date product was ordered
and the number of units that were ordered. I'd like the list to only show
info for the number of units ordered, i.e. if 10 units are ordered I want to
see info for all 10, if there were only 3 in the order I'd like the list to
shrink down to 3 rows so that I don't have a clutter of unnecessary info.

I've been trying variations of the following formula to get the list of # of
units to expand or shrink based on the order size:

=IF(AND(S5+1<=$Q$6,S5<""),S5+1,"")

This formula appears in cell S6 and:
S5 = cell for unit 1 and contains "1"
Q6 = # of units ordered

The idea is if the order size is at least 2 (as shown in Q6) then cell S6
would display 2, otherwise it would just stay blank.

What's happening is that this works until the first cell beyond the order
size (i.e. cell #6 for a 5 unit order shows blank) but in the subsequent
cells I get a "#VALUE" error rather than having them show blank.

It's a cosmetic thing but any help would be appreciated.