numbers within a range of numbers

[Cathy Landry]

Hello,

I need a formula or vba code to bring back the numbers between a range of
numbers. For example, in sheet1:

Col A Col B
05 10

In sheet2 it would list in Col A: 6,7,8,9

Thank you!
Cathy

 

[Ron Coderre]

Try something like this:

On Sheet1, cells A1:B2
Start End
5 10

On Sheet2
A1: =Sheet1!A2+1
A2: =IF(MAX($A$1:$A1)=Sheet1!$B$2-1,"",A1+1)
Copy that formula down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[T. Valko]

With:

Sheet1 A1 = 5
Sheet1 B1 = 10

Maybe something like this entered on Sheet2 cell A1:

=IF(COUNT(Sheet1!A$1:B$1)<2,"",IF(ROWS($1:1)<=Sheet1!B$1-Sheet1!A$1-1,Sheet1!A$1+ROWS($1:1),""))

Copy down until you get blanks

Biff

 

[Bernd]

Hello Cathy,

Select cells A1:A1000 in Sheet2 and array-enter (enter with CTRL +
SHIFT + ENTER, not only ENTER):
=ROW(INDIRECT(Sheet1!A1+1&":"&Sheet1!B1-1))

Regards,
Bernd

 

[yshridhar]

use
Randombetween() worksheet function

 

[Teethless mama]

In sheet 2
A1: =Sheet1!A1+1
A2: =IF(ISERR(A1+1),"",IF(A1+1<Sheet1!$B$1,A1+1,""))
Copy from A2 down until you see blank

 

[Cathy Landry]

Hi Ron,

Yes, that works! Thank you

Try something like this:

On Sheet1, cells A1:B2
Start End
5 10

On Sheet2
A1: =Sheet1!A2+1
A2: =IF(MAX($A$1:$A1)=Sheet1!$B$2-1,"",A1+1)
Copy that formula down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Cathy Landry]

Hello Yshridhar,

Could you expand on how to use this workseet function?

Thankyou
Cathy

 

[David Biddulph]

I don't think there is a randombetween() function. There is a randbetween()
and you'll find details in Excel help, but I don't think it does what you're
looking for.

 

[Cathy Landry]

Thank you everyone! I have several options to work with now

 

[Shoney]

Hello T.,

Is there a way to make this formula work with time?

For example, I'm looking to find all half-hour intervals between two
specified times. So, if I had a range of 12:00pm-2:00pm, I would want the
formula to show 12:00pm, then drag down (12:30pm, 1:00pm, 1:30pm, 2:00pm,
then blanks).

 

[T. Valko]

Try this:

A1 = start = 12:00 PM
B1 = end = 2:00 PM

A3: =Start

Enter this formula in A4 and copy down as needed:

=IF(A3="","",IF(A3+TIME(0,30,0)>end,"",A3+TIME(0,30,0)))

 

[Shoney]

Thanks! That's exactly what I was looking for.

Try this:

A1 = start = 12:00 PM
B1 = end = 2:00 PM

A3: =Start

Enter this formula in A4 and copy down as needed:

=IF(A3="","",IF(A3+TIME(0,30,0)>end,"",A3+TIME(0,30,0)))

 

[T. Valko]

You're welcome. Thanks for the feedback!