Help with displaying the contents of the last populate cell.

I have numerous sheets within a book where all cells in column C in all 
sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you 
reference both columns P and R hold a monetary value and are formatted as 
Currency.

Is there a way that cell D1 can automatically be populated with the contents 
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

0
Pank (119)
4/3/2007 3:28:04 PM
excel.newusers 15348 articles. 2 followers. Follow

9 Replies
525 Views

Similar Articles

[PageSpeed] 3

If the cells are always filled up to the last cell meaning if the first 
filled cell is in C2 and the last cell in C30 all the cells in-between are 
filled you can simply use

=INDEX(C2:C10000,COUNT(C2:C10000))

change the ranges accordingly if you think you will fill more than 10000 
cells

post back if it's not that way

Regards,

Peo Sjoblom

"Pank" <Pank@discussions.microsoft.com> wrote in message 
news:1A6870CF-33AA-4FD8-8D1C-12E40A806372@microsoft.com...
>I have numerous sheets within a book where all cells in column C in all
> sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For you
> reference both columns P and R hold a monetary value and are formatted as
> Currency.
>
> Is there a way that cell D1 can automatically be populated with the 
> contents
> of the last cell in column C that has a value in it.
>
> E.G.
>
> Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
> Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
> Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.
>
> Any help offered would be appreciated.
> 


0
terre081 (3244)
4/3/2007 3:39:10 PM
If you want the value of the last numeric value in the column range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to  suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

> I have numerous sheets within a book where all cells in column C in all 
> sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you 
> reference both columns P and R hold a monetary value and are formatted as 
> Currency.
> 
> Is there a way that cell D1 can automatically be populated with the contents 
> of the last cell in column C that has a value in it.
> 
> E.G.
> 
> Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
> Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
> Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.
> 
> Any help offered would be appreciated.
> 
0
RonCoderre (135)
4/3/2007 3:50:01 PM
Peo,

Thanks just what I wanted.

Thank U.

"Peo Sjoblom" wrote:

> If the cells are always filled up to the last cell meaning if the first 
> filled cell is in C2 and the last cell in C30 all the cells in-between are 
> filled you can simply use
> 
> =INDEX(C2:C10000,COUNT(C2:C10000))
> 
> change the ranges accordingly if you think you will fill more than 10000 
> cells
> 
> post back if it's not that way
> 
> Regards,
> 
> Peo Sjoblom
> 
> "Pank" <Pank@discussions.microsoft.com> wrote in message 
> news:1A6870CF-33AA-4FD8-8D1C-12E40A806372@microsoft.com...
> >I have numerous sheets within a book where all cells in column C in all
> > sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For you
> > reference both columns P and R hold a monetary value and are formatted as
> > Currency.
> >
> > Is there a way that cell D1 can automatically be populated with the 
> > contents
> > of the last cell in column C that has a value in it.
> >
> > E.G.
> >
> > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
> > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
> > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.
> >
> > Any help offered would be appreciated.
> > 
> 
> 
> 
0
Pank (119)
4/3/2007 3:50:03 PM
Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you please 
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

> If you want the value of the last numeric value in the column range....
> 
> Try something like this:
> D1: =LOOKUP(10^99,C1:C20)
> 
> Adjust the range reference to  suit your situation.
> 
> 
> Does that help?
> ***********
> Regards,
> Ron
> 
> XL2002, WinXP
> 
> 
> "Pank" wrote:
> 
> > I have numerous sheets within a book where all cells in column C in all 
> > sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you 
> > reference both columns P and R hold a monetary value and are formatted as 
> > Currency.
> > 
> > Is there a way that cell D1 can automatically be populated with the contents 
> > of the last cell in column C that has a value in it.
> > 
> > E.G.
> > 
> > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
> > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
> > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.
> > 
> > Any help offered would be appreciated.
> > 
0
Pank (119)
4/3/2007 4:42:04 PM
Through a fortunate quirk in the LOOKUP function, if you use  it to find a 
value that is greater than any other value in the list...it returns the last 
item of that type (text or numeric) in the list.  Excel's maximum possible 
number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.

If you were looking for the last text value in a column range, this would 
work:
=LOOKUP(REPT("z",255),C2:C20)
Note: REPT("z",255) returns a string of 255 z's

Regarding Peo's solution, the COUNT function returns the  count of numeric 
cells.  As long as the series of numeric values is contiguous (no blanks or 
text within the list), it returns the position of the last numeric cell 
within the list.

In Peo's application, the INDEX function returns the n-th item in C2:C10000, 
where "n" is the count of numeric cells.  
 
I hope that helps.
(Post back if you have more  questions)
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

> Ron,
> 
> Thar worked a treat as well.
> 
> I understand the lookup, however, I do not understand the Can you please 
> explain what the 10^99, can you please explain.
> 
> If possible, can you also explain Peo solution.
> 
> Thank you for your time.
> 
> "Ron Coderre" wrote:
> 
> > If you want the value of the last numeric value in the column range....
> > 
> > Try something like this:
> > D1: =LOOKUP(10^99,C1:C20)
> > 
> > Adjust the range reference to  suit your situation.
> > 
> > 
> > Does that help?
> > ***********
> > Regards,
> > Ron
> > 
> > XL2002, WinXP
> > 
> > 
> > "Pank" wrote:
> > 
> > > I have numerous sheets within a book where all cells in column C in all 
> > > sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you 
> > > reference both columns P and R hold a monetary value and are formatted as 
> > > Currency.
> > > 
> > > Is there a way that cell D1 can automatically be populated with the contents 
> > > of the last cell in column C that has a value in it.
> > > 
> > > E.G.
> > > 
> > > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
> > > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
> > > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.
> > > 
> > > Any help offered would be appreciated.
> > > 
0
RonCoderre (135)
4/3/2007 5:02:05 PM
Ron,

Thank U for explaining. So much to learn and put into pratice.

Regards

"Ron Coderre" wrote:

> Through a fortunate quirk in the LOOKUP function, if you use  it to find a 
> value that is greater than any other value in the list...it returns the last 
> item of that type (text or numeric) in the list.  Excel's maximum possible 
> number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.
> 
> If you were looking for the last text value in a column range, this would 
> work:
> =LOOKUP(REPT("z",255),C2:C20)
> Note: REPT("z",255) returns a string of 255 z's
> 
> Regarding Peo's solution, the COUNT function returns the  count of numeric 
> cells.  As long as the series of numeric values is contiguous (no blanks or 
> text within the list), it returns the position of the last numeric cell 
> within the list.
> 
> In Peo's application, the INDEX function returns the n-th item in C2:C10000, 
> where "n" is the count of numeric cells.  
>  
> I hope that helps.
> (Post back if you have more  questions)
> ***********
> Regards,
> Ron
> 
> XL2002, WinXP
> 
> 
> "Pank" wrote:
> 
> > Ron,
> > 
> > Thar worked a treat as well.
> > 
> > I understand the lookup, however, I do not understand the Can you please 
> > explain what the 10^99, can you please explain.
> > 
> > If possible, can you also explain Peo solution.
> > 
> > Thank you for your time.
> > 
> > "Ron Coderre" wrote:
> > 
> > > If you want the value of the last numeric value in the column range....
> > > 
> > > Try something like this:
> > > D1: =LOOKUP(10^99,C1:C20)
> > > 
> > > Adjust the range reference to  suit your situation.
> > > 
> > > 
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > > 
> > > XL2002, WinXP
> > > 
> > > 
> > > "Pank" wrote:
> > > 
> > > > I have numerous sheets within a book where all cells in column C in all 
> > > > sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you 
> > > > reference both columns P and R hold a monetary value and are formatted as 
> > > > Currency.
> > > > 
> > > > Is there a way that cell D1 can automatically be populated with the contents 
> > > > of the last cell in column C that has a value in it.
> > > > 
> > > > E.G.
> > > > 
> > > > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
> > > > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
> > > > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.
> > > > 
> > > > Any help offered would be appreciated.
> > > > 
0
Pank (119)
4/3/2007 5:08:06 PM
"<<<Through a fortunate quirk in the LOOKUP function>>>"

I wouldn't exactly describe it as a "quirk".<g>

I believe it's more like a computer program "blindly" following the code of 
the author.

Since Lookup() is *supposed* to be properly used on *only* an ascending 
ordered list,
*AND*
If Lookup() can't find the lookup value, it uses the largest value in the 
array (list) that is less than or equal to lookup value,
Lookup() assumes the *largest* value is the *LAST* value, since it is 
programmed to believe the list is sorted, ascending.
And we make the lookup value larger then any number that would *normally* 
exist in the list:
10^99 - 99^99

Or we make it as large as any number which *can* exist in the list:
9.99999999999999E307

-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" <RonCoderre@discussions.microsoft.com> wrote in message 
news:E6CF55B2-BDA6-487C-ADED-C75EE6D8B938@microsoft.com...
> Through a fortunate quirk in the LOOKUP function, if you use  it to find a
> value that is greater than any other value in the list...it returns the 
> last
> item of that type (text or numeric) in the list.  Excel's maximum possible
> number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.
>
> If you were looking for the last text value in a column range, this would
> work:
> =LOOKUP(REPT("z",255),C2:C20)
> Note: REPT("z",255) returns a string of 255 z's
>
> Regarding Peo's solution, the COUNT function returns the  count of numeric
> cells.  As long as the series of numeric values is contiguous (no blanks 
> or
> text within the list), it returns the position of the last numeric cell
> within the list.
>
> In Peo's application, the INDEX function returns the n-th item in 
> C2:C10000,
> where "n" is the count of numeric cells.
>
> I hope that helps.
> (Post back if you have more  questions)
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Pank" wrote:
>
>> Ron,
>>
>> Thar worked a treat as well.
>>
>> I understand the lookup, however, I do not understand the Can you please
>> explain what the 10^99, can you please explain.
>>
>> If possible, can you also explain Peo solution.
>>
>> Thank you for your time.
>>
>> "Ron Coderre" wrote:
>>
>> > If you want the value of the last numeric value in the column range....
>> >
>> > Try something like this:
>> > D1: =LOOKUP(10^99,C1:C20)
>> >
>> > Adjust the range reference to  suit your situation.
>> >
>> >
>> > Does that help?
>> > ***********
>> > Regards,
>> > Ron
>> >
>> > XL2002, WinXP
>> >
>> >
>> > "Pank" wrote:
>> >
>> > > I have numerous sheets within a book where all cells in column C in 
>> > > all
>> > > sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For 
>> > > you
>> > > reference both columns P and R hold a monetary value and are 
>> > > formatted as
>> > > Currency.
>> > >
>> > > Is there a way that cell D1 can automatically be populated with the 
>> > > contents
>> > > of the last cell in column C that has a value in it.
>> > >
>> > > E.G.
>> > >
>> > > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 
>> > > 200.
>> > > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 
>> > > 250.
>> > > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 
>> > > 900.
>> > >
>> > > Any help offered would be appreciated.
>> > > 


0
ragdyer1 (4060)
4/4/2007 12:18:39 AM
You didn't like "quirk", RD?

It seems like we both described the same function behavior.
However, I apologize to LOOKUP() if I disparaged it in any way. <vbg>

Best Regards,

Ron


"Ragdyer" <ragdyer@cutoutmsn.com> wrote in message 
news:eBfIm6kdHHA.2268@TK2MSFTNGP02.phx.gbl...
> "<<<Through a fortunate quirk in the LOOKUP function>>>"
>
> I wouldn't exactly describe it as a "quirk".<g>
>
> I believe it's more like a computer program "blindly" following the code 
> of the author.
>
> Since Lookup() is *supposed* to be properly used on *only* an ascending 
> ordered list,
> *AND*
> If Lookup() can't find the lookup value, it uses the largest value in the 
> array (list) that is less than or equal to lookup value,
> Lookup() assumes the *largest* value is the *LAST* value, since it is 
> programmed to believe the list is sorted, ascending.
> And we make the lookup value larger then any number that would *normally* 
> exist in the list:
> 10^99 - 99^99
>
> Or we make it as large as any number which *can* exist in the list:
> 9.99999999999999E307
>
> -- 
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Ron Coderre" <RonCoderre@discussions.microsoft.com> wrote in message 
> news:E6CF55B2-BDA6-487C-ADED-C75EE6D8B938@microsoft.com...
>> Through a fortunate quirk in the LOOKUP function, if you use  it to find 
>> a
>> value that is greater than any other value in the list...it returns the 
>> last
>> item of that type (text or numeric) in the list.  Excel's maximum 
>> possible
>> number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.
>>
>> If you were looking for the last text value in a column range, this would
>> work:
>> =LOOKUP(REPT("z",255),C2:C20)
>> Note: REPT("z",255) returns a string of 255 z's
>>
>> Regarding Peo's solution, the COUNT function returns the  count of 
>> numeric
>> cells.  As long as the series of numeric values is contiguous (no blanks 
>> or
>> text within the list), it returns the position of the last numeric cell
>> within the list.
>>
>> In Peo's application, the INDEX function returns the n-th item in 
>> C2:C10000,
>> where "n" is the count of numeric cells.
>>
>> I hope that helps.
>> (Post back if you have more  questions)
>> ***********
>> Regards,
>> Ron
>>
>> XL2002, WinXP
>>
>>
>> "Pank" wrote:
>>
>>> Ron,
>>>
>>> Thar worked a treat as well.
>>>
>>> I understand the lookup, however, I do not understand the Can you please
>>> explain what the 10^99, can you please explain.
>>>
>>> If possible, can you also explain Peo solution.
>>>
>>> Thank you for your time.
>>>
>>> "Ron Coderre" wrote:
>>>
>>> > If you want the value of the last numeric value in the column 
>>> > range....
>>> >
>>> > Try something like this:
>>> > D1: =LOOKUP(10^99,C1:C20)
>>> >
>>> > Adjust the range reference to  suit your situation.
>>> >
>>> >
>>> > Does that help?
>>> > ***********
>>> > Regards,
>>> > Ron
>>> >
>>> > XL2002, WinXP
>>> >
>>> >
>>> > "Pank" wrote:
>>> >
>>> > > I have numerous sheets within a book where all cells in column C in 
>>> > > all
>>> > > sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For 
>>> > > you
>>> > > reference both columns P and R hold a monetary value and are 
>>> > > formatted as
>>> > > Currency.
>>> > >
>>> > > Is there a way that cell D1 can automatically be populated with the 
>>> > > contents
>>> > > of the last cell in column C that has a value in it.
>>> > >
>>> > > E.G.
>>> > >
>>> > > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 
>>> > > 200.
>>> > > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 
>>> > > 250.
>>> > > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 
>>> > > 900.
>>> > >
>>> > > Any help offered would be appreciated.
>>> > >
>
> 


0
4/4/2007 1:19:11 AM
Come to think of it, you're label is probably appropo.<bg>
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message 
news:eiS7KdldHHA.4616@TK2MSFTNGP03.phx.gbl...
> You didn't like "quirk", RD?
>
> It seems like we both described the same function behavior.
> However, I apologize to LOOKUP() if I disparaged it in any way. <vbg>
>
> Best Regards,
>
> Ron
>
>
> "Ragdyer" <ragdyer@cutoutmsn.com> wrote in message 
> news:eBfIm6kdHHA.2268@TK2MSFTNGP02.phx.gbl...
>> "<<<Through a fortunate quirk in the LOOKUP function>>>"
>>
>> I wouldn't exactly describe it as a "quirk".<g>
>>
>> I believe it's more like a computer program "blindly" following the code 
>> of the author.
>>
>> Since Lookup() is *supposed* to be properly used on *only* an ascending 
>> ordered list,
>> *AND*
>> If Lookup() can't find the lookup value, it uses the largest value in the 
>> array (list) that is less than or equal to lookup value,
>> Lookup() assumes the *largest* value is the *LAST* value, since it is 
>> programmed to believe the list is sorted, ascending.
>> And we make the lookup value larger then any number that would *normally* 
>> exist in the list:
>> 10^99 - 99^99
>>
>> Or we make it as large as any number which *can* exist in the list:
>> 9.99999999999999E307
>>
>> -- 
>> Regards,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>> "Ron Coderre" <RonCoderre@discussions.microsoft.com> wrote in message 
>> news:E6CF55B2-BDA6-487C-ADED-C75EE6D8B938@microsoft.com...
>>> Through a fortunate quirk in the LOOKUP function, if you use  it to find 
>>> a
>>> value that is greater than any other value in the list...it returns the 
>>> last
>>> item of that type (text or numeric) in the list.  Excel's maximum 
>>> possible
>>> number is 9.99999999999999E307, but I use 10^99 to avoid all that 
>>> typing.
>>>
>>> If you were looking for the last text value in a column range, this 
>>> would
>>> work:
>>> =LOOKUP(REPT("z",255),C2:C20)
>>> Note: REPT("z",255) returns a string of 255 z's
>>>
>>> Regarding Peo's solution, the COUNT function returns the  count of 
>>> numeric
>>> cells.  As long as the series of numeric values is contiguous (no blanks 
>>> or
>>> text within the list), it returns the position of the last numeric cell
>>> within the list.
>>>
>>> In Peo's application, the INDEX function returns the n-th item in 
>>> C2:C10000,
>>> where "n" is the count of numeric cells.
>>>
>>> I hope that helps.
>>> (Post back if you have more  questions)
>>> ***********
>>> Regards,
>>> Ron
>>>
>>> XL2002, WinXP
>>>
>>>
>>> "Pank" wrote:
>>>
>>>> Ron,
>>>>
>>>> Thar worked a treat as well.
>>>>
>>>> I understand the lookup, however, I do not understand the Can you 
>>>> please
>>>> explain what the 10^99, can you please explain.
>>>>
>>>> If possible, can you also explain Peo solution.
>>>>
>>>> Thank you for your time.
>>>>
>>>> "Ron Coderre" wrote:
>>>>
>>>> > If you want the value of the last numeric value in the column 
>>>> > range....
>>>> >
>>>> > Try something like this:
>>>> > D1: =LOOKUP(10^99,C1:C20)
>>>> >
>>>> > Adjust the range reference to  suit your situation.
>>>> >
>>>> >
>>>> > Does that help?
>>>> > ***********
>>>> > Regards,
>>>> > Ron
>>>> >
>>>> > XL2002, WinXP
>>>> >
>>>> >
>>>> > "Pank" wrote:
>>>> >
>>>> > > I have numerous sheets within a book where all cells in column C in 
>>>> > > all
>>>> > > sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". 
>>>> > > For you
>>>> > > reference both columns P and R hold a monetary value and are 
>>>> > > formatted as
>>>> > > Currency.
>>>> > >
>>>> > > Is there a way that cell D1 can automatically be populated with the 
>>>> > > contents
>>>> > > of the last cell in column C that has a value in it.
>>>> > >
>>>> > > E.G.
>>>> > >
>>>> > > Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 
>>>> > > 200.
>>>> > > Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 
>>>> > > 250.
>>>> > > Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 
>>>> > > 900.
>>>> > >
>>>> > > Any help offered would be appreciated.
>>>> > >
>>
>>
>
> 


0
ragdyer1 (4060)
4/4/2007 5:25:01 PM
Reply:

Similar Artilces:

How to make a cell equal another cells colour?
I have conditional formatting on a set of values to highlight high/low values using a 2-colour scale. Is there a way I can get another set of cells to mirror the colour alone without changing their values? Many thanks in advance Sam.D Have them use the same conditional format rules, simply references the cells they are "mirroring". -- Best Regards, Luke M "Sam.D" <SamD@discussions.microsoft.com> wrote in message news:B6BAC7A4-17F8-4A53-BBF3-9BCED618642F@microsoft.com... >I have conditional formatting on a set of values to highlight high...

Recover a Prod content database to Dev farm
Hello, I was wondering is it possible to recover (copy) a content database from a seperate farm to a Dev farm? I came across a few hints about using the "Copy the database files to a network folder" option, but nothing concrete. I'd appreciate if someone could clarify this for me? regards, Edgar You can recovery a database to a network location. We have the following options: http://technet.microsoft.com/en-us/library/bb808956.aspx You will see the details in the "Tasks" section -- Santhosh Sivarajan | MCTS, MCSE (W2K3/W2K/NT4), MCSA (W2K3/W2K...

Excel does not return to previous cell
Hi all, I am running into an problem with my users. We have a Excel 2000 spreadsheet. I know that typically, the sheet/cell that is selected when a user closes the spreadsheet will be the same when a user opens the spreadsheet the next time. However, the problem I am seeing is that the proves true with some users, but not with all users. Some users open the spreadsheet and instead of going back to, say cell A972, it opens to, say cell B4. I would welcome any input on this issue. Thanks, Jeff Averhoff <<<"I know that typically, the sheet/cell that is selected when a user...

Entourage not Downloading Whole messages/displaying HTML
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Email Client: pop Sorry for what is really a simple question. <br> last month had to &quot;rebuild&quot; database. <br> Since then, emails do not download in their entirety nor display HTML. <br> I have changed the preference radio button to Display HTML repeatedly. <br> I have un-checked the partial download messages radio buttons on the account setting repeatedly. <br><br>Please help...DRIVING me NUTS to have to click download whole message on virtually EVERY message. <br><br&g...

Listbox contents do not show..
Hi (Access 2003..), I have an unbound listbox I use to get some info from a user (by merely selecting a row). The listbox, and its contents show properly. There's an "after update" event for this box where I gather and analysze some info, such as the row number and the content of various columns. So far so good. I've recently added some code that sometimes issues a warning message (based on circumstances). This is done as a msgbox with some verbage and which simply contains an OK button. The problem is that when the msgbox appears on the screen, the entire content...

Cell Shading Color
I am running Excel 2003 in Win XP Pro SP2 I would like to set up cells so the fill color: = pale blue when the entered value is => 110 = no fill if =< 109 Is this possible? if so, how do I do it? TIA Bill Conditional formatting. Format>Conditional Formatting and set the tests accordingly. -- HTH RP (remove nothere from the email address if mailing direct) "Bill" <smile@here.net> wrote in message news:uxSFnEmHFHA.156@TK2MSFTNGP10.phx.gbl... > I am running Excel 2003 in Win XP Pro SP2 > > I would like to set up cells so the fill color: > >...

19 Digits in a Cell
It has been necessary to put 19 digits in a cell but the last four revert to Zeros (0000) I have attempted various formats of the cell but of no availe, Why? and Help please. :confused: Bri -- bri ------------------------------------------------------------------------ bri's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27704 View this thread: http://www.excelforum.com/showthread.php?threadid=489539 Preformat the cell as text (format|cells|number tab|Text) Or start your entry with an apostrophe: '1234123412341234123 bri wrote: > > It has been...

click on cell takes you to specific sheet
Sheet 1 is a summary. Each value comes from an underlying sheet. Is there a way of clicking on the cell and it will jump me to the specific sheet. With 40 or so sheets it is hard to remember which sheet generates the value on sheet 1. Thank you You can make a hyperlink which lets you jump to a specified cell in a specified worksheet with Insert / Hyperlink.. /Place in This Document . GL, Henk "Ron Tarr" wrote: > Sheet 1 is a summary. Each value comes from an underlying sheet. > > Is there a way of clicking on the cell and it will jump me to the specific > sheet....

copy entire contents worksheet into new workbook, link but editabl
I need to copy the entire contents of a worksheet which is used by several collegues who need to see it looking the same always (headers in the same order,etc). In the new worksheet (workbook) I will rearrange the data for other purposes. However, I will need the data to be kept up-to-date as per the source worksheet. ...

Protect cells from moving
I have a protected worksheet with unprotected cells for users to input data. There are formulae which read the cells to build up a set of codes. Users have been moving thier input cells (Cut & Paste) which of course corrupts the formulae. Protection allows me to prevent inserting and deletion of rows but not cells. How can I prevent users from moving cells. Its fine if they want to copy. So Copy & Paste is fine but not Cut & Paste. Thanks Neil ...

Calling HTML Help from subform
I'm working in ACCESS 2003 on Windows XP. I converted our .hlp help file to ..chm and I am using the code from Microsoft's web page at http://support.microsoft.com/?kbid=275117 to call it. Our .chm help file opens fine for all forms and controls except for subforms which are contained within main forms. Can anyone tell me how to change the code so it (the help file) opens when the focus is on a control in a subform? When the focus is on a control on the main form the code works but not when the focus is on the subform. I have context IDs on all the form and subform controls. Th...

Pasting into visible cells only
I have two worksheets that are set up to custom view with a sort function; i.e. it hides lines. I know how to copy visible lines to a fully open worksheet. If the worksheet I want to copy to is also sorted to have the same number of visible lines is there a way to copy just into those visible lines? I get an error box saying " cannot paste in to multiple selections". Excel will only paste into a contiguous range, so you can't copy visible cells only, and paste into visible cells only. TV Man wrote: > I have two worksheets that are set up to custom view with > a so...

Creating column content based on another column's keyword(s)
All: I'm trying to create a -new- *Category* column based on keywords from pre-existing *Names* column. For example, if the *Names* column is full of US maps (e.g. Abielene TX; Akron, OH; etc.), the *Category* column's adjacent cell woul contain just the state: A2: Abielene, TX | B2: TX A3: Akron, OH | B3: OH etc. At its simplest, the *Category* column could just be the firs letter/number/charc of *Names* column. Basically, I'm looking for the proverbial quick n' dirty macro scrip to achieve these results. Thx! K -- KHashmi31 --------------------------------------...

Internet Explorer cannot display this feed
The feeds section works for about a day before reverting to 'Internet Explorer cannot display this feed' . After removing allthe feeds and starting over for the 7th or 8th time I guess it is time to give up. This is not the only problem in IE8. Stopped using IE 8 years ago but thought it was worth a try with a new computer and Windows 7. BIG BIG mistake. There is obviously no answer to the problem as nobody gets a response from MS. Typical. "craggers" <craggers@discussions.microsoft.com> wrote in message news:065995F7-7510-4341-B43E-39AC774FB8A0@microso...

Displaying a blank cell
I have this formula in my spreadsheet... =IF('Prospect Questions'!B61=""," ","") Obviously, if cell B61 on the Prospect Questions work sheet is blank, I want this new cell to appear with a blank. Otherwise I want the contents of what's in B61. When I enter this formula, it is displaying the formula. What am I doing wrong??? Is the formatting wrong??? Or just the formula?? HELP! Have you text format in the cell with the formula? Also, =IF('Prospect Questions'!B61="","",'Prospect Questions'!B61) I noticed th...

keyboard shortcut to return to previous cell after "find" or "got.
Is there any easy way to returnt to the previously selected cells? This is helpful especially after "goto" or "find". Hi, One way is to have 2 macros, both with their own shortcuts, one to store the current active cell address, and the other to use that stored address to reset the active cell. The macro would store the address somewhere on the sheet, or in a Public statement. Or a single macro could be used, which toggles between storing and restoring, depending on the state of the storage address. Would this be of help? Regards - Dave. "Nadavb" wrote: &g...

Display Report Options in Alphabetical Order/Rename Report Options
Display Report Options in Alphabetical Order in the Report Options Window For example, when the Trial Balance Report Options window is open, the drop down list of report options appear to be in the order in which the reports were created, making it difficult to navigate to find the desired report option, especially when many options have been created. Would also like the ability to rename a report option. -- Elisabeth ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I A...

preventing a cell from being referenced?
How do you do this? I don't think you can stop it. Eijah626 wrote: > > How do you do this? -- Dave Peterson ...

can I import excel cells into word as labels
I am using Office 2000, and I'm trying to import excel data into word as labels. I have one column of numbers(approx. 350 cells) I would like to import into Word as individual labels. can this be done? -- Trial and Error takes too long! Use Mail merge. :) http://www.officearticles.com/word/mail_merge_labels_in_microsoft_word.htm ************ Anne Troy www.OfficeArticles.com "wichita6" <wichita6@discussions.microsoft.com> wrote in message news:E79D3E90-8093-4B9F-B195-DF6C4C54ED0D@microsoft.com... >I am using Office 2000, and I'm trying to import excel data into ...

Formula Help 03-01-10
I need to find how many times Dan occurs in a certain month. Example Below. A B 1 1/2/10 Dan 2 1/5/10 Dan 3 1/10/10 Perry 4 2/3/10 Perry 5 2/7/10 Dan 6 2/12/10 Perry Answer = 2 for January & 1 for February -- Thanks! PIVOT table is the right tool for this. "mahlandj" wrote: > I need to find how many times Dan occurs in a certain month. Example Below. > > A ...

Help ! Import Items from Excel or Comma Delimited file
Current user of Quicksell 2000 planning on upgrading. I have never used Quicksell to the fullest extent possible but am planning on using RMS to the maximum extent possible. Local partner familiar to RMS but I from reading it seems you might have so more information to help me and him. When I spoke with the local partner he said it wasn't possible to import price updates yearly from suppliers. I have multiple suppliers for one product I will probably use the UPC for the item number. He said I could import once but would have to manually update the prices each year from the sup...

Blanking cells with formula errors
The cell with the below formula returns the #N/A sign. =VLOOKUP(A3,'!MW'!A:J,5,FALSE) The value I am looking up (A3 on this occasion) is sometimes #N/A (which I know) Is there an IF formula that I can incoroporate within the above formula to return "" if my lookup value (A3) equals #N/A. ???? I hope someone follows this! Thanks in advance! =IF(ISNUMBER(MATCH(A3,'!MW'!A:A,0)),VLOOKUP(A3,'!MW'!A:J,5,FALSE),"") -- Regards, Peo Sjoblom "Richard Layzell" <anonymous@discussions.microsoft.com> wrote in message news:099101c3a7...

Referencing an excel 'cell' value on the shapesheet screen
I have a value in an excel spreadsheet, Test.xls for example at cell 'A1', that I want to use in the PinX field of a shapesheet. How do I reference the excel sheet in the shapesheet window? I tried to use ='Path\[Test.xls]Sheet1'!A1 to do this but it didn't work. Thanks for all your help. Hi Fred, That's a cool idea and it makes perfect sense, but... I know the ShapeSheet looks like Excel, but unfortunately, you can't do this directly. You could write some automation code that might be able to do some sort of linking, but this capability isn't built ...

> 12 series displayed, or better way?
a user has 11 curves to dispay. in addition, there is a series of X,Y pairs that he would also like to display. when the (x,y) pairs are displayed (they are actually discrete points), the first one shows fine, but any point added after the first, does not visibly appear on the chart. I tried deleting one of the 11 curves... then a second (x,y) point would display. It appears that the chart was only able to handle 12 sets of data. The reason that I tried to do it with a new series for each (x,y) pair is that the info displayed when the cursor hovers over the data point is much more...

How do I get the date to display a Julian date? (yyddd)
I am trying to get the date to show up as a Julian date, yyddd. In my form and report I need to have both regular ddmmyyyy and yyddd fields. they are the same day so if there is an expression that I can use to convert the first date into the julian that would work as well. Thanks next time try a simple search for it on this site... this was given by Karl Dewey Format(Date(), "yy") & Format(DatePart("y", Date()), "000") "Jacobs33Ladder" wrote: > I am trying to get the date to show up as a Julian date, yyddd. In my form &...