See if cell value appears within a value range given by another cell.

In one column (b), have the figure of 470
In another column (a), a cell contains values containing a number, a
dash and another number: "425 - 490"
I need to check to determine if the number 470 is found within a range
defined by a cells contents.  In this case, yes, it's equal to or
between them.

(The dash may or may not have spaces around it.  ..could be 425-490,
or 425 - 490.)
The column (b) item can be the 425, or the 490 and any number in
between.

Need to do a vlookup to find if the 470 is present in any of the
assorted ranges contained in column (a).

Thanks for your interest.
Pierre

0
Pierre
4/1/2010 6:16:19 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

9 Replies
1275 Views

Similar Articles

[PageSpeed] 32

Hi

This formula will return Yes if the number in B1 is in the range given
in A1.

=3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
$1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))

The formula can be copied down as required.

Hopes this helps.
....
Per

On 1 Apr., 20:16, Pierre <cow...@aol.com> wrote:
> In one column (b), have the figure of 470
> In another column (a), a cell contains values containing a number, a
> dash and another number: "425 - 490"
> I need to check to determine if the number 470 is found within a range
> defined by a cells contents. =A0In this case, yes, it's equal to or
> between them.
>
> (The dash may or may not have spaces around it. =A0..could be 425-490,
> or 425 - 490.)
> The column (b) item can be the 425, or the 490 and any number in
> between.
>
> Need to do a vlookup to find if the 470 is present in any of the
> assorted ranges contained in column (a).
>
> Thanks for your interest.
> Pierre

0
Per
4/1/2010 6:56:56 PM
Per:  You're a genius!

I knight you "Sir Per".
Thank you.
Pierre

On Apr 1, 1:56=A0pm, Per Jessen <perjesse...@hotmail.com> wrote:
> Hi
>
> This formula will return Yes if the number in B1 is in the range given
> in A1.
>
> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> The formula can be copied down as required.
>
> Hopes this helps.
> ...
> Per
>
> On 1 Apr., 20:16, Pierre <cow...@aol.com> wrote:
>
>
>
> > In one column (b), have the figure of 470
> > In another column (a), a cell contains values containing a number, a
> > dash and another number: "425 - 490"
> > I need to check to determine if the number 470 is found within a range
> > defined by a cells contents. =A0In this case, yes, it's equal to or
> > between them.
>
> > (The dash may or may not have spaces around it. =A0..could be 425-490,
> > or 425 - 490.)
> > The column (b) item can be the 425, or the 490 and any number in
> > between.
>
> > Need to do a vlookup to find if the 470 is present in any of the
> > assorted ranges contained in column (a).
>
> > Thanks for your interest.
> > Pierre- Hide quoted text -
>
> - Show quoted text -

0
Pierre
4/1/2010 7:03:37 PM
Per, can it be adjusted to find a "Yes", or "No", if the value is
found (or not found) within all the cells in the entire column, not
just the range in A1?  Lets say A2:A10000?
I tried replacing it with that and it didn't behave.

Thanks for your thoughts on this.

Pierre
On Apr 1, 1:56=A0pm, Per Jessen <perjesse...@hotmail.com> wrote:
> Hi
>
> This formula will return Yes if the number in B1 is in the range given
> in A1.
>
> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> The formula can be copied down as required.
>
> Hopes this helps.
> ...
> Per
>
> On 1 Apr., 20:16, Pierre <cow...@aol.com> wrote:
>
>
>
> > In one column (b), have the figure of 470
> > In another column (a), a cell contains values containing a number, a
> > dash and another number: "425 - 490"
> > I need to check to determine if the number 470 is found within a range
> > defined by a cells contents. =A0In this case, yes, it's equal to or
> > between them.
>
> > (The dash may or may not have spaces around it. =A0..could be 425-490,
> > or 425 - 490.)
> > The column (b) item can be the 425, or the 490 and any number in
> > between.
>
> > Need to do a vlookup to find if the 470 is present in any of the
> > assorted ranges contained in column (a).
>
> > Thanks for your interest.
> > Pierre- Hide quoted text -
>
> - Show quoted text -

0
Pierre
4/1/2010 7:39:21 PM
Did you copy down to A10000 as Per suggested?


Gord Dibben  MS Excel MVP

On Thu, 1 Apr 2010 12:39:21 -0700 (PDT), Pierre <cowguy@aol.com> wrote:

>Per, can it be adjusted to find a "Yes", or "No", if the value is
>found (or not found) within all the cells in the entire column, not
>just the range in A1?  Lets say A2:A10000?
>I tried replacing it with that and it didn't behave.
>
>Thanks for your thoughts on this.
>
>Pierre
>On Apr 1, 1:56�pm, Per Jessen <perjesse...@hotmail.com> wrote:
>> Hi
>>
>> This formula will return Yes if the number in B1 is in the range given
>> in A1.
>>
>> =IF($B$1>=LEFT(A1,FIND("-",A1)-1)*1,IF($B
>> $1<=MID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>>
>> The formula can be copied down as required.
>>
>> Hopes this helps.
>> ...
>> Per
>>
>> On 1 Apr., 20:16, Pierre <cow...@aol.com> wrote:
>>
>>
>>
>> > In one column (b), have the figure of 470
>> > In another column (a), a cell contains values containing a number, a
>> > dash and another number: "425 - 490"
>> > I need to check to determine if the number 470 is found within a range
>> > defined by a cells contents. �In this case, yes, it's equal to or
>> > between them.
>>
>> > (The dash may or may not have spaces around it. �..could be 425-490,
>> > or 425 - 490.)
>> > The column (b) item can be the 425, or the 490 and any number in
>> > between.
>>
>> > Need to do a vlookup to find if the 470 is present in any of the
>> > assorted ranges contained in column (a).
>>
>> > Thanks for your interest.
>> > Pierre- Hide quoted text -
>>
>> - Show quoted text -

0
Gord
4/1/2010 8:01:58 PM
Gord,: I copied it down.  As Per said, it'll find a match if the
contents in B1 meet the criteria in A1.  That works great.
What I'm looking for is to see it it'll match B1 with any range in
A1:a10000.  I copied down the formulas in column C, got either
#VALUE!, or "No".
Thanks.

Pierre

..On Apr 1, 3:01=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Did you copy down to A10000 as Per suggested?
>
> Gord Dibben =A0MS Excel MVP
>
>
>
> On Thu, 1 Apr 2010 12:39:21 -0700 (PDT),Pierre<cow...@aol.com> wrote:
> >Per, can it be adjusted to find a "Yes", or "No", if the value is
> >found (or not found) within all the cells in the entire column, not
> >just the range in A1? =A0Lets say A2:A10000?
> >I tried replacing it with that and it didn't behave.
>
> >Thanks for your thoughts on this.
>
> >Pierre
> >On Apr 1, 1:56 pm, Per Jessen <perjesse...@hotmail.com> wrote:
> >> Hi
>
> >> This formula will return Yes if the number in B1 is in the range given
> >> in A1.
>
> >> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> >> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> >> The formula can be copied down as required.
>
> >> Hopes this helps.
> >> ...
> >> Per
>
> >> On 1 Apr., 20:16,Pierre<cow...@aol.com> wrote:
>
> >> > In one column (b), have the figure of 470
> >> > In another column (a), a cell contains values containing a number, a
> >> > dash and another number: "425 - 490"
> >> > I need to check to determine if the number 470 is found within a ran=
ge
> >> > defined by a cells contents. In this case, yes, it's equal to or
> >> > between them.
>
> >> > (The dash may or may not have spaces around it. ..could be 425-490,
> >> > or 425 - 490.)
> >> > The column (b) item can be the 425, or the 490 and any number in
> >> > between.
>
> >> > Need to do a vlookup to find if the 470 is present in any of the
> >> > assorted ranges contained in column (a).
>
> >> > Thanks for your interest.
> >> >Pierre- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
Pierre
4/1/2010 8:34:14 PM
Pierre,

My formula can *only* evaluate row by row. Use my original formula in
C1 and copy it down to C10000, now you have yes on now for each line.

Then insert this formula in D1 to calculate 'one' yes or no (column C
can be hidden):

=3DIF(COUNTIF(C1:C10000,"=3DYes")>=3D1,"Yes","No")

/Per

On 1 Apr., 22:34, Pierre <cow...@aol.com> wrote:
> Gord,: I copied it down. =A0As Per said, it'll find a match if the
> contents in B1 meet the criteria in A1. =A0That works great.
> What I'm looking for is to see it it'll match B1 with any range in
> A1:a10000. =A0I copied down the formulas in column C, got either
> #VALUE!, or "No".
> Thanks.
>
> Pierre
>
> .On Apr 1, 3:01=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>
>
>
> > Did you copy down to A10000 as Per suggested?
>
> > Gord Dibben =A0MS Excel MVP
>
> > On Thu, 1 Apr 2010 12:39:21 -0700 (PDT),Pierre<cow...@aol.com> wrote:
> > >Per, can it be adjusted to find a "Yes", or "No", if the value is
> > >found (or not found) within all the cells in the entire column, not
> > >just the range in A1? =A0Lets say A2:A10000?
> > >I tried replacing it with that and it didn't behave.
>
> > >Thanks for your thoughts on this.
>
> > >Pierre
> > >On Apr 1, 1:56 pm, Per Jessen <perjesse...@hotmail.com> wrote:
> > >> Hi
>
> > >> This formula will return Yes if the number in B1 is in the range giv=
en
> > >> in A1.
>
> > >> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> > >> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> > >> The formula can be copied down as required.
>
> > >> Hopes this helps.
> > >> ...
> > >> Per
>
> > >> On 1 Apr., 20:16,Pierre<cow...@aol.com> wrote:
>
> > >> > In one column (b), have the figure of 470
> > >> > In another column (a), a cell contains values containing a number,=
 a
> > >> > dash and another number: "425 - 490"
> > >> > I need to check to determine if the number 470 is found within a r=
ange
> > >> > defined by a cells contents. In this case, yes, it's equal to or
> > >> > between them.
>
> > >> > (The dash may or may not have spaces around it. ..could be 425-490=
,
> > >> > or 425 - 490.)
> > >> > The column (b) item can be the 425, or the 490 and any number in
> > >> > between.
>
> > >> > Need to do a vlookup to find if the 470 is present in any of the
> > >> > assorted ranges contained in column (a).
>
> > >> > Thanks for your interest.
> > >> >Pierre- Hide quoted text -
>
> > >> - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

0
Per
4/1/2010 11:18:16 PM
Per,
Thank you for the reply and adding the column formula.  Will try later
today and report back.  Thanks again for your interest.

Pierre

On Apr 1, 6:18=A0pm, Per Jessen <perjesse...@hotmail.com> wrote:
> Pierre,
>
> My formula can *only* evaluate row by row. Use my original formula in
> C1 and copy it down to C10000, now you have yes on now for each line.
>
> Then insert this formula in D1 to calculate 'one' yes or no (column C
> can be hidden):
>
> =3DIF(COUNTIF(C1:C10000,"=3DYes")>=3D1,"Yes","No")
>
> /Per
>
> On 1 Apr., 22:34, Pierre <cow...@aol.com> wrote:
>
>
>
> > Gord,: I copied it down. =A0As Per said, it'll find a match if the
> > contents in B1 meet the criteria in A1. =A0That works great.
> > What I'm looking for is to see it it'll match B1 with any range in
> > A1:a10000. =A0I copied down the formulas in column C, got either
> > #VALUE!, or "No".
> > Thanks.
>
> > Pierre
>
> > .On Apr 1, 3:01=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> > > Did you copy down to A10000 as Per suggested?
>
> > > Gord Dibben =A0MS Excel MVP
>
> > > On Thu, 1 Apr 2010 12:39:21 -0700 (PDT),Pierre<cow...@aol.com> wrote:
> > > >Per, can it be adjusted to find a "Yes", or "No", if the value is
> > > >found (or not found) within all the cells in the entire column, not
> > > >just the range in A1? =A0Lets say A2:A10000?
> > > >I tried replacing it with that and it didn't behave.
>
> > > >Thanks for your thoughts on this.
>
> > > >Pierre
> > > >On Apr 1, 1:56 pm, Per Jessen <perjesse...@hotmail.com> wrote:
> > > >> Hi
>
> > > >> This formula will return Yes if the number in B1 is in the range g=
iven
> > > >> in A1.
>
> > > >> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> > > >> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> > > >> The formula can be copied down as required.
>
> > > >> Hopes this helps.
> > > >> ...
> > > >> Per
>
> > > >> On 1 Apr., 20:16,Pierre<cow...@aol.com> wrote:
>
> > > >> > In one column (b), have the figure of 470
> > > >> > In another column (a), a cell contains values containing a numbe=
r, a
> > > >> > dash and another number: "425 - 490"
> > > >> > I need to check to determine if the number 470 is found within a=
 range
> > > >> > defined by a cells contents. In this case, yes, it's equal to or
> > > >> > between them.
>
> > > >> > (The dash may or may not have spaces around it. ..could be 425-4=
90,
> > > >> > or 425 - 490.)
> > > >> > The column (b) item can be the 425, or the 490 and any number in
> > > >> > between.
>
> > > >> > Need to do a vlookup to find if the 470 is present in any of the
> > > >> > assorted ranges contained in column (a).
>
> > > >> > Thanks for your interest.
> > > >> >Pierre- Hide quoted text -
>
> > > >> - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Skjul tekst i anf=F8rselstegn -
>
> > - Vis tekst i anf=F8rselstegn -- Hide quoted text -
>
> - Show quoted text -

0
Pierre
4/2/2010 2:07:05 PM
Still unable to get it to behave.  Where I'd expect a "No", a On Apr
2, 9:07=A0am, Pierre <cow...@aol.com> wrote:
> Per,
> Thank you for the reply and adding the column formula. =A0Will try later
> today and report back. =A0Thanks again for your interest.
>
> Pierre
>
> On Apr 1, 6:18=A0pm, Per Jessen <perjesse...@hotmail.com> wrote:
>
>
>
> > Pierre,
>
> > My formula can *only* evaluate row by row. Use my original formula in
> > C1 and copy it down to C10000, now you have yes on now for each line.
>
> > Then insert this formula in D1 to calculate 'one' yes or no (column C
> > can be hidden):
>
> > =3DIF(COUNTIF(C1:C10000,"=3DYes")>=3D1,"Yes","No")
>
> > /Per
>
> > On 1 Apr., 22:34, Pierre <cow...@aol.com> wrote:
>
> > > Gord,: I copied it down. =A0As Per said, it'll find a match if the
> > > contents in B1 meet the criteria in A1. =A0That works great.
> > > What I'm looking for is to see it it'll match B1 with any range in
> > > A1:a10000. =A0I copied down the formulas in column C, got either
> > > #VALUE!, or "No".
> > > Thanks.
>
> > > Pierre
>
> > > .On Apr 1, 3:01=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> > > > Did you copy down to A10000 as Per suggested?
>
> > > > Gord Dibben =A0MS Excel MVP
>
> > > > On Thu, 1 Apr 2010 12:39:21 -0700 (PDT),Pierre<cow...@aol.com> wrot=
e:
> > > > >Per, can it be adjusted to find a "Yes", or "No", if the value is
> > > > >found (or not found) within all the cells in the entire column, no=
t
> > > > >just the range in A1? =A0Lets say A2:A10000?
> > > > >I tried replacing it with that and it didn't behave.
>
> > > > >Thanks for your thoughts on this.
>
> > > > >Pierre
> > > > >On Apr 1, 1:56 pm, Per Jessen <perjesse...@hotmail.com> wrote:
> > > > >> Hi
>
> > > > >> This formula will return Yes if the number in B1 is in the range=
 given
> > > > >> in A1.
>
> > > > >> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> > > > >> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> > > > >> The formula can be copied down as required.
>
> > > > >> Hopes this helps.
> > > > >> ...
> > > > >> Per
>
> > > > >> On 1 Apr., 20:16,Pierre<cow...@aol.com> wrote:
>
> > > > >> > In one column (b), have the figure of 470
> > > > >> > In another column (a), a cell contains values containing a num=
ber, a
> > > > >> > dash and another number: "425 - 490"
> > > > >> > I need to check to determine if the number 470 is found within=
 a range
> > > > >> > defined by a cells contents. In this case, yes, it's equal to =
or
> > > > >> > between them.
>
> > > > >> > (The dash may or may not have spaces around it. ..could be 425=
-490,
> > > > >> > or 425 - 490.)
> > > > >> > The column (b) item can be the 425, or the 490 and any number =
in
> > > > >> > between.
>
> > > > >> > Need to do a vlookup to find if the 470 is present in any of t=
he
> > > > >> > assorted ranges contained in column (a).
>
> > > > >> > Thanks for your interest.
> > > > >> >Pierre- Hide quoted text -
>
> > > > >> - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -- Skjul tekst i anf=F8rselstegn -
>
> > > - Vis tekst i anf=F8rselstegn -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
Pierre
4/2/2010 10:36:59 PM
Hello All

Still can't get this to behave.  Added column D formulaWhere I'd
expect a "No" to appear in col C, FALSE is showing up.  It's still not
looking down column A for either the existing value or if the value
falls within any of the ranges presented somewhere column A.

Any help is appreciated.
Pierre

On Apr 1, 6:18=A0pm, Per Jessen <perjesse...@hotmail.com> wrote:
> Pierre,
>
> My formula can *only* evaluate row by row. Use my original formula in
> C1 and copy it down to C10000, now you have yes on now for each line.
>
> Then insert this formula in D1 to calculate 'one' yes or no (column C
> can be hidden):
>
> =3DIF(COUNTIF(C1:C10000,"=3DYes")>=3D1,"Yes","No")
>
> /Per
>
> On 1 Apr., 22:34, Pierre <cow...@aol.com> wrote:
>
>
>
> > Gord,: I copied it down. =A0As Per said, it'll find a match if the
> > contents in B1 meet the criteria in A1. =A0That works great.
> > What I'm looking for is to see it it'll match B1 with any range in
> > A1:a10000. =A0I copied down the formulas in column C, got either
> > #VALUE!, or "No".
> > Thanks.
>
> > Pierre
>
> > .On Apr 1, 3:01=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>
> > > Did you copy down to A10000 as Per suggested?
>
> > > Gord Dibben =A0MS Excel MVP
>
> > > On Thu, 1 Apr 2010 12:39:21 -0700 (PDT),Pierre<cow...@aol.com> wrote:
> > > >Per, can it be adjusted to find a "Yes", or "No", if the value is
> > > >found (or not found) within all the cells in the entire column, not
> > > >just the range in A1? =A0Lets say A2:A10000?
> > > >I tried replacing it with that and it didn't behave.
>
> > > >Thanks for your thoughts on this.
>
> > > >Pierre
> > > >On Apr 1, 1:56 pm, Per Jessen <perjesse...@hotmail.com> wrote:
> > > >> Hi
>
> > > >> This formula will return Yes if the number in B1 is in the range g=
iven
> > > >> in A1.
>
> > > >> =3DIF($B$1>=3DLEFT(A1,FIND("-",A1)-1)*1,IF($B
> > > >> $1<=3DMID(A1,FIND("-",A1)+1,999)*1,"Yes","No"))
>
> > > >> The formula can be copied down as required.
>
> > > >> Hopes this helps.
> > > >> ...
> > > >> Per
>
> > > >> On 1 Apr., 20:16,Pierre<cow...@aol.com> wrote:
>
> > > >> > In one column (b), have the figure of 470
> > > >> > In another column (a), a cell contains values containing a numbe=
r, a
> > > >> > dash and another number: "425 - 490"
> > > >> > I need to check to determine if the number 470 is found within a=
 range
> > > >> > defined by a cells contents. In this case, yes, it's equal to or
> > > >> > between them.
>
> > > >> > (The dash may or may not have spaces around it. ..could be 425-4=
90,
> > > >> > or 425 - 490.)
> > > >> > The column (b) item can be the 425, or the 490 and any number in
> > > >> > between.
>
> > > >> > Need to do a vlookup to find if the 470 is present in any of the
> > > >> > assorted ranges contained in column (a).
>
> > > >> > Thanks for your interest.
> > > >> >Pierre- Hide quoted text -
>
> > > >> - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Skjul tekst i anf=F8rselstegn -
>
> > - Vis tekst i anf=F8rselstegn -- Hide quoted text -
>
> - Show quoted text -

0
Pierre
4/2/2010 10:42:27 PM
Reply:

Similar Artilces:

I cant see emails coming in anymore.
I used to push send/receive and a box would come up telling me 'Receiving 8 of 40 emails' with a green bar going across the centre. Once the emails are received the box goes. If I push send/receive nothing happens. If I push it say, 10 times, again nothing happens. Once all the emails are thru the box that used to come up, than flashes up 10 times, but by than its a little late. Does anyone know how to get the box back? I would really appreciate it... KR Mick. I send this message using the forward procedure to somebody of the group My problem is likely the same as a lot ...

MS Office Publisher Help window appears after every print job
I've just started using Publisher 2003 and am doing fine with most of it. However, one thing is driving me crazy... every time I send a job to a printer, whether it's an actual printer like my HP Laserjet 4MV or a virtual printer like the Acrobat PDF 7, after the job spools out, the MS Office Pint Help window appears, on a page that says "Troubleshoot printing publications on a desktop printer." I didn't clikc on help. I don't need help, because the jobs print fine... why does this window keep appearing, and more importantly, how do I make it stop appearing sh...

Not able to see all data but can see on another computer
Hi, I am using SAP application and if i retrive the excel file from SAP i can see only 48 lines where as i should see 110 lines but if i opened that same excel file in another computer then i can see all lines (110 Lines) i don't know why its happening. Can anyone help on this? Thanks in advance. Regards, Harish ...

Getting data into Excel cells for OLE objects retuened from Access
Hello all, I have a MS Query in Excel filtering and retrieving relevant data from MS Access. When the data returns, all BUT the column which refers to a OLE object datatype in Access returns values. What should I set the format in Excel to see the OLE objects dynamically. Thanks AFAIK, that is not possible. Sorry. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <0dce01c3db7e$ba637bb0$a301280a@phx.gbl>, anonymous@discussions.microsoft.com says... > Hello all, &...

Query to Access : does'nt see tables
Office 2000 SP3 FR From Excel / MS Query, I want to read data in Access tables. I choose the mdb file and so MS Query displays this error message (translated) : "This data source does'nt have any visible table". Original in French : "Cette source de donn�es ne contient aucune table visible." I transfert my data to a new mdb file : it work's few times (!), then the error message appears again. How to resolve this problem ? Jacques. Gotcha ! MS Query does'nt accept a file name with 2 dot inside ! Example : MyFile.mdb --> Ok My.File.mdb --> "...

Select all colored cells
I am working on a code in visual basic for a macro in Excel. In the worksheet, I have an area that is green. What I want the program/macro to do is copy a formula in an existing cell, select all the colored cells (all cells are the same color) and paste the formula in the colored cells. Is this possible? If so, what does the code look like? Thank you! This might get you started: Option Explicit Sub testme03() Dim subRng As Range Dim cellWithFormula As Range Dim SuperRng As Range Dim myCell As Range With ActiveSheet Set SuperRng = Selection For E...

Create a user-defined cell to display shape's layer name
How can I reference a shape's own layer name to display as a field? Specifically, I want to create a text box with a custom formula or user-defined field that display's that shape's one and only layer name. I can display the layerMember index by using "=LayerMember", but can't figure out the name. Please no VB/VBA. Thanks. The 1st thing to do might be just making custom propery on the page. Then please open the shapesheet of the page, add rows to the Custom Preperty Section, (or Shape Data in Visio 2007). The number of rows are as same as the number of layers. An...

How to see my mail queue?
Hi there, I have a Exchange 2000 running on Windows 2000 server. Could you please tell me how to monitor my emails in my mail queue? I can't find a location on the Windows to see the mail queue status. Thanks in advance, Ross To view the mail queues follow these steps: 1. Open system manager, Navigate down to the following location Your organization --> Administrative groups --> Your Admin group --> Servers --> Your Server --> Protocols --> SMTP --> SMTP virtual server --> Queues You should then see all the smtp queues and monitor mail. thanks, J. ...

I see you
Well, not really, but I used Google Earth to zoom in on the Flatiron Building. You can get so close you can see the utilities on the roof, the hydrants on the street, the little parkish area across 5th Avenue, etc etc. Cool! Now tell me where your office is (like where is Peter's window, what floor and facing what street). Google Earth lets you zoom in all directions so I'll be able to turn the map to see that window. Silly I know but still a connection on some level. Pats looked good but not great last night. I didn't watch much more of the game. I'm not sold on ...

To See or Not To See
I have copied a few tables of text from a web page into Excel. Some of the information contain word that are hyperlinks or email addresses. Is there a way to change those columns of information to reveal the web address or email address? Thanks So is it just displaying plaintext currently? Have you seen this information from the Help Menu? When you make changes to the Hyperlink and Followed Hyperlink styles, your changes apply to all hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

cels within cells
hello, I have a problem concerning cells within cells. I want to strech a range of cells to 20 pixels except cel a3. a3 is than 10 pixels big, then i want to slide cel b3 with a3 making it together 20 pixels large thx ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Columnwidth is based on the column. You can't really change the width based on what row you're in. But you could use merged cells. Select your range (A3:B3) and do Format|Cells|alignment, che...

Count Cells with alphanumeric data
Hi, I'm just trying to count the cells in a range that have alphanumeric data. example: asd, we, rty, 22r, 45aa, 1s, wer, tyu. I need to count all cells in that range that have numbers. If all of the ones with number have a number as the 1st character, then a formula like this should work, just change the A1:A8 range to whatever range you need: =COUNTIF(A1:A8,"<A") "Aris" wrote: > Hi, > > I'm just trying to count the cells in a range that have alphanumeric data. > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu. ...

How to copy cells horizontally to cells out of sequence
I have data in cells that are on Sheet1. This data is in Cells B2, B3, B4, B5 etc (All Horizonally, ie staying in same row). I'm working on Sheet2 and would like to know how to take this data from Sheet1 and copy it to different cells on Sheet2, but have the data fall vertically. I can copy this once and repeat, but I am looking for a formula that will do this for me so I can copy it multiple times. Example: Sheet1, B2, will go to Sheet2, K4. Sheet1 C2, will go to Sheet2, K5 Sheet1 D2, will go to Sheet2, K6 Please help. One way: Enter this in K4 of Sheet2, and copy down as n...

Nice seeing all of you hopefully I see you in the other forum
really I just wanted to get the last post in prior to it closing today :) ...

Passing RetrieveGlobals9's Connection Object from IM to another dl
I am using RetrieveGlobals9 to get a connection and successfully connecting to the database from within script events in Integration Manager. However, when I tried to pass the open connection to another (VB.Net) application from within the script, I get an error, "Class does not support automation." Is this something that I can fix by rewriting the VB.Net application, or will that always happen when I try to call an external .dll from within Integration Manager? Thanks in advance. ...

E12 unable to see storage groups through EX MMC
We've been running E12 in a test environment for about 1 month and just today ran into a situation where the storage groups for a server are no longer visible from the console on that machine. If we view the server from a console on a different EX server we see all storage groups and stores. Any help is greatly appreciated Thanks can you list the storage groups and stores from the shell (EMS) on that server? did you try restarting EMC? -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- &quo...

increase cell character limit
Hi all, Simple question that is driving me crazy. I'm making a table containing text and numbers. One of the cells in this table has 1459 characters. For some reason, the last couple sentences won't fill the bottom of the cell; instead, they're cut off. I can only see them if I increase the column width, but I don't want to do that since it'll complicate other things (readability, etc). So, how do I get all the text/characters to display in this cell? Thanks -- Weissme ------------------------------------------------------------------------ Weissme's Profile:...

MS Excel VBA DAO SQL where clause cell
Hi I'm trying to get a table of data from MS Access to MS Excel via VBA DAO programming. In my worksheet call 'Data' in cell D14 there is my string value call "toys & Chairs" I can get the DATA into MS Excel without the Where clause in my SQL VBA script. The problem is when I use the WHERE clause which sources its value from cell D14... In my VBA code. Firstly, cell D14 is defined in VBA: Dim r1 As String r1 = Range("D14") Secondly, MY SQL is: SQL = "LVL_1, LVL_2, LVL_3" SQL = SQL & " FROM table_ABC" SQL = SQL &...

How do I get the total of a range of cells that are in another she
I have two sheets in the workbook and I need to get the total of a range in sheet1 into a table on sheet2. How do I go about this? something like =sum(sheet2!$a$1:$a$5) note the ! after the sheet name bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28504 View this thread: http://www.excelforum.com/showthread.php?threadid=487937 ...

Need to add cell comments in unlocked cell on protected worksheet
Using Excel 2002, I have a worksheet that I have "protected" so that only a certain block of cells can be selected/edited. Today, I have a new requirement to be able to add/edit/delete cell comments on those cells that remain unlocked. But the options to do so on the cell context menu disappear when the worksheet is protected, but reappear when the worksheet is unprotected. When you protect the worksheet, scroll down and check "edit objects". This will allow comments to be added/removed/changed--along with other objects (pictures/shapes/etc). dan400man wrote: >...

How to do evevet that triggers when a textbox value is changes
Hi I would like to know how to make event that trigger when a textbox (Unbound) changes. For example I have a textbox unbound with the value for example 999 and when the value have changed then event needs to be triggered. Thanks While the BeforeUpdate event doesn't work for unbound controls, the AfterUpdate one does. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Chipcom" <bz1977@gmail.com> wrote in message news:1193765222.443627.8450@19g2000hsx.googlegroups.com... > Hi > > I would like to know how to make event that t...

Can I see My Calendar as others see it
Can I view my calendar as if I am viewing it by someone with whom I share? no. Your permission levels are greater. Why do you want to be able to? -- Regards Judy Gleeson MVP Outlook in Canberra, Australia .. "laurelpowellcochrane" <laurelpowellcochrane@discussions.microsoft.com> wrote in message news:46AD6AD8-5B97-4D06-9F73-2D212B626E05@microsoft.com... > Can I view my calendar as if I am viewing it by someone with whom I share? ...

drop down values based on the another drop down
There are two drop down columns A & B, I want the drop down values of to be displayed based on the value selected for A -- Gupt ----------------------------------------------------------------------- Gupta's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2782 View this thread: http://www.excelforum.com/showthread.php?threadid=47330 Hi Gupta take a look at Debra Dalgleish's site for detailed examples of how to achieve this. http://www.contextures.com/xlDataVal01.html Regards Roger Govier Gupta wrote: >There are two drop down columns A & B...

Cannot see posts again
My posts from the Ng do not appear on the NG. I have to go to google groups to look them up. Cumbersome. How can I solve the problem? TIA This newsgroup ? Is WLM configured to use newsgroup community features(if so disable it) Are any of the missing messages present in the Storage folders/Recovered = items folders(and appearing as if sent to you instead of=20 the newsgroup)? -- ....winston ms-mvp mail "Ben Stevenson" wrote in message = news:u3f9tyRNLHA.4288@TK2MSFTNGP06.phx.gbl... My posts from the Ng do not appear on the NG. I have to go to google = grou...