Is it possible to extract only the first five or six  digits from a large number
? If a number that has been cubed to ninth power, eg: 732 = 6.03392 E+25  Is it
possible to have only the number 603392 to work with?

Leo

Thank you!!  I will definitely check it out.  Global financial domination
needs a helping hand!  I also run AnalyzerXL as an add-in in 2007.  Works
great.

----- Original Message -----
From: "Martin Topper" <mtopper@...>
To: <ms_excel@yahoogroups.com>
Sent: Tuesday, December 29, 2009 3:40 PM
Subject: Re: [ms_excel] Building a hyperlink


> if your aim is global financial domination, then you need to look at the
> smf add-in.  go to http://finance.groups.yahoo.com/group/smf_addin/  and
> download the addin (which by the way, does work in excel 2007)
>
> Marty
>
>
>
>
>
>
> ________________________________
> From: coldvernors <sjp@...>
> To: ms_excel@yahoogroups.com
> Sent: Tue, December 29, 2009 2:49:56 PM
> Subject: [ms_excel] Building a hyperlink
>
>
> I am using 2007 and am currently working on a spreadsheet of stocks that
> is basically a long list of ticker symbols.  In order to achieve global
> financial domination, I need to construct a hyperlink out of a constant
> URL root plus each ticker symbol.  Example:
>
> A2 = "http://www.google. com/finance? q="    B2 = MSFT
>
> If in C2 I CONCATENATE such that "=CONCATENATE( A2,B2)", I get the desired
> result of "http://www.google. com/finance? q=MSFT".  Unfortunately, it is
> not an active hyperlink, just the text.
>
> Since C2 is actually still a formula, I copied the value over to D2.
> However, it still did not become a hyperlink, just text.
>
> Interestingly enough, when I activated the cell in D2 by double clicking
> it and then I hit return, the hyperlink was created.  Don't understand
> this part at all.
>
> All of that double clicking is wearisome.  I was wondering if anyone in
> this august group can think of a good solution to this problem?
>
> Thanks in advance!
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>     =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>             and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo!
> Groups Links
>
>
>

Ted...thank you.  Better than what I was doing.


----- Original Message -----
From: "Spear, Ted (US - MGTTS)" <ted.spear@...>
To: <ms_excel@yahoogroups.com>
Sent: Tuesday, December 29, 2009 3:36 PM
Subject: RE: [ms_excel] Building a hyperlink


> Have you tried the Hyperlink to make sure it goes to the correct place
> before you do the whole sheet? Depending on how many formulas you have,
> EDIT should do the same as a double click. Set your cursor to move Down
> after you hit enter, highlight the area you need the hyperlinks, then
> just keep hitting F2 ENTER.
>
>
>
> Ted Spear
>
> ________________________________
>
> From: ms_excel@yahoogroups.com [mailto:ms_excel@yahoogroups.com] On
> Behalf Of coldvernors
> Sent: Tuesday, December 29, 2009 2:50 PM
> To: ms_excel@yahoogroups.com
> Subject: [ms_excel] Building a hyperlink
>
>
>
>
>
> I am using 2007 and am currently working on a spreadsheet of stocks that
> is basically a long list of ticker symbols. In order to achieve global
> financial domination, I need to construct a hyperlink out of a constant
> URL root plus each ticker symbol. Example:
>
> A2 = "http://www.google.com/finance?q=
> <http://www.google.com/finance?q=> " B2 = MSFT
>
> If in C2 I CONCATENATE such that "=CONCATENATE(A2,B2)", I get the
> desired result of "http://www.google.com/finance?q=MSFT
> <http://www.google.com/finance?q=MSFT> ". Unfortunately, it is not an
> active hyperlink, just the text.
>
> Since C2 is actually still a formula, I copied the value over to D2.
> However, it still did not become a hyperlink, just text.
>
> Interestingly enough, when I activated the cell in D2 by double clicking
> it and then I hit return, the hyperlink was created. Don't understand
> this part at all.
>
> All of that double clicking is wearisome. I was wondering if anyone in
> this august group can think of a good solution to this problem?
>
> Thanks in advance!
>
>
>
>
>
> This email may contain proprietary information and/or copyright
> material. This email is intended for the use of the addressee only.
> Any unauthorized use may be unlawful. If you receive this email by
> mistake, please advise the sender immediately by using the reply
> facility in your email software.
>
> Information contained in and/or attached to this document may be
> subject to export control regulations of the European Community, USA,
> or other countries. Each recipient of this document is responsible to
> ensure that usage and/or transfer of any information contained in
> this document complies with all relevant export control regulations.
> If you are in any doubt about the export control restrictions that
> apply to this information, please contact the sender immediately.
>
> Be aware that Meggitt may monitor incoming and outgoing emails to
> ensure compliance with the Meggitt IT User policy.
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>     =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>             and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo!
> Groups Links
>
>
>

If A2 contains:
http://www.google.com/finance?q=
B2 contains:
MSFT
Paste the following formula in cell C2:
=HYPERLINK(A2&B2,"Microsoft")
or
=HYPERLINK(A2&B2,B2)
Norman


________________________________
From: coldvernors <sjp@...>
To: ms_excel@yahoogroups.com
Sent: Tue, December 29, 2009 2:49:56 PM
Subject: [ms_excel] Building a hyperlink

 
I am using 2007 and am currently working on a spreadsheet of stocks that is
basically a long list of ticker symbols. In order to achieve global financial
domination, I need to construct a hyperlink out of a constant URL root plus each
ticker symbol. Example:

A2 = "http://www.google. com/finance? q=" B2 = MSFT

If in C2 I CONCATENATE such that "=CONCATENATE( A2,B2)", I get the desired
result of "http://www.google. com/finance? q=MSFT". Unfortunately, it is not an
active hyperlink, just the text.

Since C2 is actually still a formula, I copied the value over to D2. However, it
still did not become a hyperlink, just text.

Interestingly enough, when I activated the cell in D2 by double clicking it and
then I hit return, the hyperlink was created. Don't understand this part at all.

All of that double clicking is wearisome. I was wondering if anyone in this
august group can think of a good solution to this problem?

Thanks in advance!







[Non-text portions of this message have been removed]

if your aim is global financial domination, then you need to look at the smf
add-in.  go to http://finance.groups.yahoo.com/group/smf_addin/  and download
the addin (which by the way, does work in excel 2007)

Marty






________________________________
From: coldvernors <sjp@...>
To: ms_excel@yahoogroups.com
Sent: Tue, December 29, 2009 2:49:56 PM
Subject: [ms_excel] Building a hyperlink


I am using 2007 and am currently working on a spreadsheet of stocks that is
basically a long list of ticker symbols.  In order to achieve global financial
domination, I need to construct a hyperlink out of a constant URL root plus each
ticker symbol.  Example:

A2 = "http://www.google. com/finance? q="    B2 = MSFT

If in C2 I CONCATENATE such that "=CONCATENATE( A2,B2)", I get the desired
result of "http://www.google. com/finance? q=MSFT".  Unfortunately, it is not an
active hyperlink, just the text.

Since C2 is actually still a formula, I copied the value over to D2.  However,
it still did not become a hyperlink, just text.

Interestingly enough, when I activated the cell in D2 by double clicking it and
then I hit return, the hyperlink was created.  Don't understand this part at
all.

All of that double clicking is wearisome.  I was wondering if anyone in this
august group can think of a good solution to this problem?

Thanks in advance!







[Non-text portions of this message have been removed]

Yes!  Great!! Thanks.  -Robert


At 2009-12-29  02:30 PM, you wrote:
>
>
>Does this work for you?
>
>To count:
>=COUNTIF(J2:J36,"<10")
>
>To sum:
>=SUMIF(J2:J36,"<10")
>
>Norman
>
>________________________________
>From: Robert Carneal USA
><<mailto:carnealre%40adelphia.net>carnealre@...>
>To: <mailto:ms_excel%40yahoogroups.com>ms_excel@yahoogroups.com
>Sent: Tue, December 29, 2009 3:12:24 PM
>Subject: [ms_excel] Counting cells with single digits
>
>Â
>If I want to count all the cells in the range J2 though J36 for
>*single* digits, wouldn't this work?
>
>=SUM(IF(AND( J2:J36>=" 1",J2:J36< ="9"),1,0) )
>
>I.e., if cells:
>J3=1
>J5=3
>J8=2
>J12=9
>J15=2
>J17=33 << I do NOT want this one counted. Just the single digits.
>
>Then I wanted the formula to return 5 as a result. Instead, when I
>use that formula, I get a VALID error. The following formula works:
>
>=SUM(IF(J2:J36= "O",1,0)) +SUM(IF(J2: J36="1",1,
>0))+SUM(IF( J2:J36="2" ,1,0))+SUM( IF(J2:J36=
>"3",1,0)) +SUM(IF(J2: J36="4",1, 0))+SUM(IF(
>J2:J36="5" ,1,0))+SUM( IF(J2:J36= "6",1,0))
>+SUM(IF(J2: J36="7",1, 0))+SUM(IF( J2:J36="8" ,1,0))+SUM( IF(J2:J36= "9",1,0))
>
>Yes, I know they both require <Shft-Ctrl-Return> . I am just not
>seeing the problem with my first formula. Can anyone help please?
>
>Thank you.
>
>Robert

Have you tried the Hyperlink to make sure it goes to the correct place
before you do the whole sheet? Depending on how many formulas you have,
EDIT should do the same as a double click. Set your cursor to move Down
after you hit enter, highlight the area you need the hyperlinks, then
just keep hitting F2 ENTER.



Ted Spear

________________________________

From: ms_excel@yahoogroups.com [mailto:ms_excel@yahoogroups.com] On
Behalf Of coldvernors
Sent: Tuesday, December 29, 2009 2:50 PM
To: ms_excel@yahoogroups.com
Subject: [ms_excel] Building a hyperlink





I am using 2007 and am currently working on a spreadsheet of stocks that
is basically a long list of ticker symbols. In order to achieve global
financial domination, I need to construct a hyperlink out of a constant
URL root plus each ticker symbol. Example:

A2 = "http://www.google.com/finance?q=
<http://www.google.com/finance?q=> " B2 = MSFT

If in C2 I CONCATENATE such that "=CONCATENATE(A2,B2)", I get the
desired result of "http://www.google.com/finance?q=MSFT
<http://www.google.com/finance?q=MSFT> ". Unfortunately, it is not an
active hyperlink, just the text.

Since C2 is actually still a formula, I copied the value over to D2.
However, it still did not become a hyperlink, just text.

Interestingly enough, when I activated the cell in D2 by double clicking
it and then I hit return, the hyperlink was created. Don't understand
this part at all.

All of that double clicking is wearisome. I was wondering if anyone in
this august group can think of a good solution to this problem?

Thanks in advance!





This email may contain proprietary information and/or copyright
material. This email is intended for the use of the addressee only.
Any unauthorized use may be unlawful. If you receive this email by
mistake, please advise the sender immediately by using the reply
facility in your email software.

Information contained in and/or attached to this document may be
subject to export control regulations of the European Community, USA,
or other countries. Each recipient of this document is responsible to
ensure that usage and/or transfer of any information contained in
this document complies with all relevant export control regulations.
If you are in any doubt about the export control restrictions that
apply to this information, please contact the sender immediately.

Be aware that Meggitt may monitor incoming and outgoing emails to
ensure compliance with the Meggitt IT User policy.




[Non-text portions of this message have been removed]

Hi Robert,
I think you may require the COUNTIF function ie
=COUNTIF(J2:J36,"<=9")

Regards Graham Fellows




[Non-text portions of this message have been removed]

Does this work for you?

To count:
=COUNTIF(J2:J36,"<10")

To sum:
=SUMIF(J2:J36,"<10")

Norman



________________________________
From: Robert Carneal USA <carnealre@...>
To: ms_excel@yahoogroups.com
Sent: Tue, December 29, 2009 3:12:24 PM
Subject: [ms_excel] Counting cells with single digits

 
If I want to count all the cells in the range J2 though J36 for
*single* digits, wouldn't this work?

=SUM(IF(AND( J2:J36>=" 1",J2:J36< ="9"),1,0) )

I.e., if cells:
J3=1
J5=3
J8=2
J12=9
J15=2
J17=33 << I do NOT want this one counted. Just the single digits.

Then I wanted the formula to return 5 as a result. Instead, when I
use that formula, I get a VALID error. The following formula works:

=SUM(IF(J2:J36= "O",1,0)) +SUM(IF(J2: J36="1",1, 0))+SUM(IF( J2:J36="2"
,1,0))+SUM( IF(J2:J36= "3",1,0)) +SUM(IF(J2: J36="4",1, 0))+SUM(IF( J2:J36="5"
,1,0))+SUM( IF(J2:J36= "6",1,0)) +SUM(IF(J2: J36="7",1, 0))+SUM(IF( J2:J36="8"
,1,0))+SUM( IF(J2:J36= "9",1,0))

Yes, I know they both require <Shft-Ctrl-Return> . I am just not
seeing the problem with my first formula. Can anyone help please?

Thank you.

Robert






[Non-text portions of this message have been removed]

try this:

="http://www.google. com/finance? q="&b2




________________________________
From: coldvernors <sjp@...>
To: ms_excel@yahoogroups.com
Sent: Tue, December 29, 2009 2:49:56 PM
Subject: [ms_excel] Building a hyperlink


I am using 2007 and am currently working on a spreadsheet of stocks that is
basically a long list of ticker symbols.  In order to achieve global financial
domination, I need to construct a hyperlink out of a constant URL root plus each
ticker symbol.  Example:

A2 = "http://www.google. com/finance? q="    B2 = MSFT

If in C2 I CONCATENATE such that "=CONCATENATE( A2,B2)", I get the desired
result of "http://www.google. com/finance? q=MSFT".  Unfortunately, it is not an
active hyperlink, just the text.

Since C2 is actually still a formula, I copied the value over to D2.  However,
it still did not become a hyperlink, just text.

Interestingly enough, when I activated the cell in D2 by double clicking it and
then I hit return, the hyperlink was created.  Don't understand this part at
all.

All of that double clicking is wearisome.  I was wondering if anyone in this
august group can think of a good solution to this problem?

Thanks in advance!







[Non-text portions of this message have been removed]

If I want to count all the cells in the range J2 though J36 for
*single* digits, wouldn't this work?

=SUM(IF(AND(J2:J36>="1",J2:J36<="9"),1,0))


I.e., if cells:
J3=1
J5=3
J8=2
J12=9
J15=2
J17=33  << I do NOT want this one counted. Just the single digits.

Then I wanted the formula to return 5 as a result. Instead, when I
use that formula, I get a VALID error.  The following formula works:

=SUM(IF(J2:J36="O",1,0))+SUM(IF(J2:J36="1",1,0))+SUM(IF(J2:J36="2",1,0))+SUM(IF(\
J2:J36="3",1,0))+SUM(IF(J2:J36="4",1,0))+SUM(IF(J2:J36="5",1,0))+SUM(IF(J2:J36="\
6",1,0))+SUM(IF(J2:J36="7",1,0))+SUM(IF(J2:J36="8",1,0))+SUM(IF(J2:J36="9",1,0))

Yes, I know they both require <Shft-Ctrl-Return>. I am just not
seeing the problem with my first formula. Can anyone help please?

Thank you.

Robert

I am using 2007 and am currently working on a spreadsheet of stocks that is
basically a long list of ticker symbols.  In order to achieve global financial
domination, I need to construct a hyperlink out of a constant URL root plus each
ticker symbol.  Example:

A2 = "http://www.google.com/finance?q="    B2 = MSFT

If in C2 I CONCATENATE such that "=CONCATENATE(A2,B2)", I get the desired result
of "http://www.google.com/finance?q=MSFT".  Unfortunately, it is not an active
hyperlink, just the text.

Since C2 is actually still a formula, I copied the value over to D2.  However,
it still did not become a hyperlink, just text.

Interestingly enough, when I activated the cell in D2 by double clicking it and
then I hit return, the hyperlink was created.  Don't understand this part at
all.

All of that double clicking is wearisome.  I was wondering if anyone in this
august group can think of a good solution to this problem?

Thanks in advance!

Thanks and yes I have worked with this to get rid of the E.  But my problem is, 
as I change my input and the number gets bigger, the output gets larger and
larger.  Then when I want to cube or square that output, I run into challenges. 
I am trying to get these excel columns to make simple calculations depending on
the answer provided by operations to an input.  Could I get my formula to
recognize only the first five digits of an output so that I could work with
that?
Leo

--- In ms_excel@yahoogroups.com, Martin Topper <mtopper@...> wrote:
>
> try making the column wider and formatting the result as a number with commas
as separators and no decimal places.
>
> I tried 450,000,000 to the ninth power and got 7.5668E+104
>
> then I formatted the cell as above and widened the column
>
> the result is
>
>
>
>
756,680,642,578,125,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,\
000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
>
>
>
>
>
> hope this helps
>
> Marty
>
>
> ________________________________
> From: smokyviewmanca <smokyviewmanca@...>
> To: ms_excel@yahoogroups.com
> Sent: Tue, December 29, 2009 1:44:09 PM
> Subject: [ms_excel] IF Formula
>
>
> I am wondering if it is possible to reduce a number back to a tenth from a
Cubed number. The dilemna I face is if I calculate a number to it's ninth power
I will get a large number depending,of course,  on the input. Sometimes this
output number can vary greatly(ie:  1.2345667E15 or 1.23456E20). Is there any
way of identifying the result to automatically convert it back to a number base
of 10?
> Leo
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

If I remember correctly the result you get IS in base 10.  Just displayed in
scientific notation with the E....... representing how many additional
placeholder columns are neccesary to display the whole number.

Marty




________________________________
From: smokyviewmanca <smokyviewmanca@...>
To: ms_excel@yahoogroups.com
Sent: Tue, December 29, 2009 1:44:09 PM
Subject: [ms_excel] IF Formula


I am wondering if it is possible to reduce a number back to a tenth from a Cubed
number. The dilemna I face is if I calculate a number to it's ninth power I will
get a large number depending,of course,  on the input. Sometimes this output
number can vary greatly(ie:  1.2345667E15 or 1.23456E20). Is there any way of
identifying the result to automatically convert it back to a number base of 10?
Leo







[Non-text portions of this message have been removed]

try making the column wider and formatting the result as a number with commas as
separators and no decimal places.

I tried 450,000,000 to the ninth power and got 7.5668E+104

then I formatted the cell as above and widened the column

the result is



756,680,642,578,125,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,\
000,000,000,000,000,000,000,000,000,000,000,000,000,000,000





hope this helps

Marty


________________________________
From: smokyviewmanca <smokyviewmanca@...>
To: ms_excel@yahoogroups.com
Sent: Tue, December 29, 2009 1:44:09 PM
Subject: [ms_excel] IF Formula


I am wondering if it is possible to reduce a number back to a tenth from a Cubed
number. The dilemna I face is if I calculate a number to it's ninth power I will
get a large number depending,of course,  on the input. Sometimes this output
number can vary greatly(ie:  1.2345667E15 or 1.23456E20). Is there any way of
identifying the result to automatically convert it back to a number base of 10?
Leo







[Non-text portions of this message have been removed]

I am wondering if it is possible to reduce a number back to a tenth from a Cubed
number. The dilemna I face is if I calculate a number to it's ninth power I will
get a large number depending,of course,  on the input. Sometimes this output
number can vary greatly(ie:  1.2345667E15 or 1.23456E20). Is there any way of
identifying the result to automatically convert it back to a number base of 10?
Leo

The file you uploaded is an .ODS file, did you upload the right file?
Please put your files in the 2009 folder. You have allot of problems in
one email, we need to start breaking them down. I would suggest asking
for help on problem at a time. One option for changing things in
multiple sheets would be to do a Find and Replace on multiple sheets. If
you hold down the Ctrl key and click on a tab or Shift and click across
a section, you can make changes on many sheets at the same time.
HOWEVER, be very careful using this, as any single change on one sheet
while the others are highlighted will make that change on every
highlighted sheet. You can make a big mess in a big hurry.

Keep working and as you come across specific problems, post them to the
group.



Ted Spear

________________________________

From: ms_excel@yahoogroups.com [mailto:ms_excel@yahoogroups.com] On
Behalf Of rdin
Sent: Thursday, December 24, 2009 12:33 AM
To: ms_excel@yahoogroups.com
Subject: [ms_excel] Help needed on formulas in pedigree chart





Working on making pedigree sheet.

File uploaded as "pedigree on 12 23 2009 1b" in file section.

I have the first of QR Marc's pedigree almost done. The problem I am now

running into, each name at the last column of the pedigree (Column G)
will
need their own 7 generation chart. That's 64 more sheets, each with a
pedigree and info sheet. I put formulas in the "info" sheet so it will
show
the names of horse and parents when I enter them in the "pedigree"
sheet.
This is a lot of work. Typing the names into the pedigree isn't too bad.

But if I have to go into each info sheet to change the forumlas to
reflect
the new pedigree sheet, that is going to be heck.

What I want to do, is there some way the formulas can take on the
reference
of the new sheet when I copy and paste the entire pedigree and info
sheets.
Right now I am using this formula in the Info sheet

=$Pedigree.F53

This will take the name from cell F53 in the Pedigree sheet and enter
that
name in the Info sheet. If I start with the first horse in column G,
*Morafic and give him a 7 generation sheet, I want to paste the Pedigree

sheet into the Pedigree_Morafic sheet (then take the names out so I can
enter *Morafic's ancestors). Then I want to paste the original Info
sheet
as Info_Morafic (with all the formulas). But I want all the
=$Pedigree.f53
etc. formulas to change to =$Pedigree_Morafic.f53 and so on.

Please tell me this can be done. If it can't is there any way around all

this manually changing? I am using Open Office Calc for this, but it
should
act the same in Excel.
Rdin





This email may contain proprietary information and/or copyright
material. This email is intended for the use of the addressee only.
Any unauthorized use may be unlawful. If you receive this email by
mistake, please advise the sender immediately by using the reply
facility in your email software.

Information contained in and/or attached to this document may be
subject to export control regulations of the European Community, USA,
or other countries. Each recipient of this document is responsible to
ensure that usage and/or transfer of any information contained in
this document complies with all relevant export control regulations.
If you are in any doubt about the export control restrictions that
apply to this information, please contact the sender immediately.

Be aware that Meggitt may monitor incoming and outgoing emails to
ensure compliance with the Meggitt IT User policy.




[Non-text portions of this message have been removed]

Thanks Dr. Johan for your time. Instead of banging my head and coming out with
some complex and slow excel file, I have got the output from my accounting
software modified which gives readymede fields. Now the same working is possible
with simple Pivot Table.
 
Regards,
 
Rajesh

--- On Sat, 26/12/09, Dr John C Bullas <john.bullas@...> wrote:


From: Dr John C Bullas <john.bullas@...>
Subject: Re: [ms_excel] Department Wise Items Quantity Summary & Movement
To: ms_excel@yahoogroups.com
Date: Saturday, 26 December, 2009, 12:14 AM


No you won't they are stripped off post in the files area 2009

On 25/12/2009, RAJESH DESAI <rmdesai73@...> wrote:
> Please find attached the file in CSV format.
>
> --- On Fri, 25/12/09, Dr John C Bullas <john.bullas@...> wrote:
>
>
> From: Dr John C Bullas <john.bullas@...>
> Subject: Re: [ms_excel] Department Wise Items Quantity Summary & Movement
> To: ms_excel@yahoogroups.com
> Date: Friday, 25 December, 2009, 9:02 PM
>
>
> please post this data as a CSV file or a workbook in files_2009
>
> have you tried using pivot tables as you can select ranges and indeed
> us another worksheet to tag data with values in response to this
>
> if you have another worksheet with boundary values like
> 01/12/09 and 12/12/09
>
> you can have a cell formula in the main database that uses a vlookup
> to test whether (say) a date in one of these colums matches or is
> included or is after or before these dates to set a column to BEFORE
> AFTER or DURING that can be used to generate the pivot tables
>
> for example, i am interested in a pivot table that only shows data
> regarding road sections i want included (usually 20 odd from 500+)  I
> set up a sorted list of these sections in another worksheet RELEVANT
> SECTIONS and then generate a column called RELEVANT and the cells
> contain an IF() based on the results of the lookup of the section name
> for that row against the list in RELEVANT SECTION, if the vlookup
> using false return nothing found, that cell is set to "NO" if the
> vlookup finds the section name it returns "YES"
>
> my pivot only ever displays results where the rows have RELEVANT as equal to
> YES
>
> The pivot can dynamically recalculate if I change the vlookup list in
> RELEVANT SECTIONS
>
> Does this make sense?
>
> Dr B
> 2009/12/25 RAJESH DESAI <rmdesai73@...>:
>> Merry Christmas to All,
>>
>> I am pasting a sample data below.
>>
>>
>>
>>
>>
>>
>>
>>
>> Date
>> Department
>> ItemCode
>> Quantity
>>
>> 01-Dec-09
>> FV
>> Item1
>> 10
>>
>> 01-Dec-09
>> FV
>> Item2
>> 15
>>
>> 01-Dec-09
>> CP
>> Item3
>> 25
>>
>> 01-Dec-09
>> CP
>> Item4
>> 30
>>
>> 01-Dec-09
>> FV
>> Item5
>> 17
>>
>> 01-Dec-09
>> FV
>> Item6
>> 12
>>
>> 01-Dec-09
>> CP
>> Item7
>> 8
>>
>> 10-Dec-09
>> FV
>> Item8
>> 5
>>
>> 10-Dec-09
>> CP
>> Item9
>> 33
>>
>> 10-Dec-09
>> CP
>> Item2
>> 13
>>
>> 10-Dec-09
>> CP
>> Item3
>> 2
>>
>> 10-Dec-09
>> CP
>> Item10
>> 20
>>
>> 20-Dec-09
>> FV
>> Item7
>> 5
>>
>> 20-Dec-09
>> FV
>> Item11
>> 13
>>
>> 20-Dec-09
>> FV
>> Item12
>> 44
>>
>> 20-Dec-09
>> CP
>> Item13
>> 50
>>
>> 20-Dec-09
>> CP
>> Item10
>> 4
>>
>> 20-Dec-09
>> CP
>> Item14
>> 1
>>
>> I want to prepare a department wise report to show movement between two
>> dates ie 1st Dec and 10th dec in follwoing manner.
>>
>> Opening Balance = Department wise Count of all the items on 1st Dec.
>> Closing Balance = Department wise Count of all the items on 10th Dec.
>> Above two thing I have managed with array formula using combination of
>> functions sum and if.
>>
>> I am unable to manage following two calculations.
>> Added During The Period = Items which were not there on 1st Dec but are on
>> 10th Dec.
>> Consumed During The Peiod = Items which were there on 1st Dec but are
>> either fully consumed and not there or their balance has gone down on
>> 10the dec.
>>
>> If any one of the above is managed then the remaining one will be the
>> balancing figure. So we will have to calculate correctly either Added or
>> Consumed items.
>>
>> I want to desing the worksheet in such a manner that I will provide
>> Opening Date and Closing Date fields to the user who will put the desired
>> dates. I will put the data validation at closing date so that it is always
>> greater than opening data and is there in the database. Rest of the grid
>> will be calculated automatically based on the two dates entered by the
>> user.
>>
>> Departments will be my rows and Opening Balance, Added, Consumed and
>> Closing Balance will be my columns of the report.
>>
>> IMPORTANT : Please note that I don't want item code wise totals anywhere.
>> My concern is count of all the items for a particular department
>> irrespective of their unit of measurement.
>>
>> I will keep one data base sheet in which the user will paste the dump from
>> my accounting software for desired dates in chronological order. I will
>> use dynamic range so that each new dump is automatically added up in the
>> data range.
>>
>> Please advise me on this. I have really gone mad thing over it.
>>
>> Regards,
>>
>> Rajesh
>>
>>
>>      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
>> http://in.yahoo.com/
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>> ------------------------------------
>>
>> ---
>>
>>     =========== Footer Revised 28 April 2006==========
>> PLEASE CHECK EXCEL HELP before posting a message to the forum
>>             and ask SMART questions
>> http://www.catb.org/~esr/faqs/smart-questions.html
>>
>> for normal live posts email <ms_excel-normal@yahoogroups.com>
>> for digest email <ms_excel-digest@yahoogroups.com>
>> for no mail email <ms_excel-nomail@yahoogroups.com>
>> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
>> ==============================================================Yahoo!
>> Groups Links
>>
>>
>>
>>
>
>
>
> --
>
> ===========================
> http://www.justgiving.com/thelongwayup2009
> 1017.8 miles from Land’s End to John
> O’Groats in 10 days for the Lingen
> Davies Cancer Relief fund and
> Primrose Hospice in Bromsgrove.
> ===========================
>
>
> ------------------------------------
>
> ---
>
>      =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>              and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo! Groups
> Links
>
>
>
>
>
>
>       The INTERNET now has a personality. YOURS! See your Yahoo!
Homepage.
> http://in.yahoo.com/
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>      =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>              and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo! Groups
> Links
>
>
>
>

--
Sent from my mobile device


===========================
http://www.justgiving.com/thelongwayup2009
1017.8 miles from Land’s End to John
O’Groats in 10 days for the Lingen
Davies Cancer Relief fund and
Primrose Hospice in Bromsgrove.
===========================


------------------------------------

---

     =========== Footer Revised 28 April 2006==========
PLEASE CHECK EXCEL HELP before posting a message to the forum
             and ask SMART questions
http://www.catb.org/~esr/faqs/smart-questions.html

for normal live posts email <ms_excel-normal@yahoogroups.com>
for digest email <ms_excel-digest@yahoogroups.com>
for no mail email <ms_excel-nomail@yahoogroups.com>
To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
==============================================================Yahoo! Groups
Links






       The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/

[Non-text portions of this message have been removed]

Marvelous! Worked the first time. Just had to adjust the column name from A to
F.

Seasons best.
Richard

----------------------------------------

From: "Randy Harmelink" <rharmelink@...>
Sent: Saturday, December 26, 2009 3:52 AM
To: ms_excel@yahoogroups.com
Subject: Re: [ms_excel] Truncating info from a single column









How about something like:

=LOWER(LEFT(A1,4)&"_"&MID(A1,FIND(" ",A1)+1,4))

On Fri, Dec 25, 2009 at 3:59 PM, rtkachuck <candr1@...> wrote:

> I have a list of about 10,000 bird species scientific names. A small sample
> is listed below:
> Struthio camelus
> Rhea americana
> Rhea pennata
> Casuarius casuarius
> Casuarius bennetti
>
> All the above names are in the same column.
>
> Notice that the species name contains the genus name first (Struthio) and
> then the species name is separated by a space (camalus) (this happens to be
> the ostrich)
>
> I would like a protocol that would take the first four letters of the genus
> name and the first four letters of the species name and combine them in the
> form: stru_came. Ideally, the output would come out in the format:
> Column A              Column B
> Struthio camelus       stru_came
> Rhea americana         rhea_amer
> Rhea pennata           rhea_penn
> Casuarius casuarius    casu_casu
> Casuarius bennetti     casu_benn
> etc
>
> In the final rendering in column B, notice that all letters are lower case.
>
> Suggestions would be appreciated.
>
> Richard
>

[Non-text portions of this message have been removed]












[Non-text portions of this message have been removed]

make sure to remove the Gannet, I never liked them, they wet their nests ;)

:)

http://www.inprint.co.uk/thebookguide/bookshop-skit.htm

Merry Christmas to one and all from your ever dedicated list co-owner Dr B

Thanks for the solution Randy!

2009/12/26 Randy Harmelink <rharmelink@...>:
> How about something like:
>
> =LOWER(LEFT(A1,4)&"_"&MID(A1,FIND(" ",A1)+1,4))
>

How about something like:

=LOWER(LEFT(A1,4)&"_"&MID(A1,FIND(" ",A1)+1,4))

On Fri, Dec 25, 2009 at 3:59 PM, rtkachuck <candr1@...> wrote:

> I have a list of about 10,000 bird species scientific names. A small sample
> is listed below:
> Struthio camelus
> Rhea americana
> Rhea pennata
> Casuarius casuarius
> Casuarius bennetti
>
> All the above names are in the same column.
>
> Notice that the species name contains the genus name first (Struthio) and
> then the species name is separated by a space (camalus) (this happens to be
> the ostrich)
>
> I would like a protocol that would take the first four letters of the genus
> name and the first four letters of the species name and combine them in the
> form: stru_came. Ideally, the output would come out in the format:
> Column A              Column B
> Struthio camelus       stru_came
> Rhea americana         rhea_amer
> Rhea pennata           rhea_penn
> Casuarius casuarius    casu_casu
> Casuarius bennetti     casu_benn
> etc
>
> In the final rendering in column B, notice that all letters are lower case.
>
> Suggestions would be appreciated.
>
> Richard
>


[Non-text portions of this message have been removed]

I have a list of about 10,000 bird species scientific names. A small sample is
listed below:
Struthio camelus
Rhea americana
Rhea pennata
Casuarius casuarius
Casuarius bennetti

All the above names are in the same column.

Notice that the species name contains the genus name first (Struthio) and then
the species name is separated by a space (camalus) (this happens to be the
ostrich)

I would like a protocol that would take the first four letters of the genus name
and the first four letters of the species name and combine them in the form:
stru_came. Ideally, the output would come out in the format:
Column A              Column B
Struthio camelus       stru_came
Rhea americana         rhea_amer
Rhea pennata           rhea_penn
Casuarius casuarius    casu_casu
Casuarius bennetti     casu_benn
etc

In the final rendering in column B, notice that all letters are lower case.

Suggestions would be appreciated.

Richard

No you won't they are stripped off post in the files area 2009

On 25/12/2009, RAJESH DESAI <rmdesai73@...> wrote:
> Please find attached the file in CSV format.
>
> --- On Fri, 25/12/09, Dr John C Bullas <john.bullas@...> wrote:
>
>
> From: Dr John C Bullas <john.bullas@...>
> Subject: Re: [ms_excel] Department Wise Items Quantity Summary & Movement
> To: ms_excel@yahoogroups.com
> Date: Friday, 25 December, 2009, 9:02 PM
>
>
> please post this data as a CSV file or a workbook in files_2009
>
> have you tried using pivot tables as you can select ranges and indeed
> us another worksheet to tag data with values in response to this
>
> if you have another worksheet with boundary values like
> 01/12/09 and 12/12/09
>
> you can have a cell formula in the main database that uses a vlookup
> to test whether (say) a date in one of these colums matches or is
> included or is after or before these dates to set a column to BEFORE
> AFTER or DURING that can be used to generate the pivot tables
>
> for example, i am interested in a pivot table that only shows data
> regarding road sections i want included (usually 20 odd from 500+)  I
> set up a sorted list of these sections in another worksheet RELEVANT
> SECTIONS and then generate a column called RELEVANT and the cells
> contain an IF() based on the results of the lookup of the section name
> for that row against the list in RELEVANT SECTION, if the vlookup
> using false return nothing found, that cell is set to "NO" if the
> vlookup finds the section name it returns "YES"
>
> my pivot only ever displays results where the rows have RELEVANT as equal to
> YES
>
> The pivot can dynamically recalculate if I change the vlookup list in
> RELEVANT SECTIONS
>
> Does this make sense?
>
> Dr B
> 2009/12/25 RAJESH DESAI <rmdesai73@...>:
>> Merry Christmas to All,
>>
>> I am pasting a sample data below.
>>
>>
>>
>>
>>
>>
>>
>>
>> Date
>> Department
>> ItemCode
>> Quantity
>>
>> 01-Dec-09
>> FV
>> Item1
>> 10
>>
>> 01-Dec-09
>> FV
>> Item2
>> 15
>>
>> 01-Dec-09
>> CP
>> Item3
>> 25
>>
>> 01-Dec-09
>> CP
>> Item4
>> 30
>>
>> 01-Dec-09
>> FV
>> Item5
>> 17
>>
>> 01-Dec-09
>> FV
>> Item6
>> 12
>>
>> 01-Dec-09
>> CP
>> Item7
>> 8
>>
>> 10-Dec-09
>> FV
>> Item8
>> 5
>>
>> 10-Dec-09
>> CP
>> Item9
>> 33
>>
>> 10-Dec-09
>> CP
>> Item2
>> 13
>>
>> 10-Dec-09
>> CP
>> Item3
>> 2
>>
>> 10-Dec-09
>> CP
>> Item10
>> 20
>>
>> 20-Dec-09
>> FV
>> Item7
>> 5
>>
>> 20-Dec-09
>> FV
>> Item11
>> 13
>>
>> 20-Dec-09
>> FV
>> Item12
>> 44
>>
>> 20-Dec-09
>> CP
>> Item13
>> 50
>>
>> 20-Dec-09
>> CP
>> Item10
>> 4
>>
>> 20-Dec-09
>> CP
>> Item14
>> 1
>>
>> I want to prepare a department wise report to show movement between two
>> dates ie 1st Dec and 10th dec in follwoing manner.
>>
>> Opening Balance = Department wise Count of all the items on 1st Dec.
>> Closing Balance = Department wise Count of all the items on 10th Dec.
>> Above two thing I have managed with array formula using combination of
>> functions sum and if.
>>
>> I am unable to manage following two calculations.
>> Added During The Period = Items which were not there on 1st Dec but are on
>> 10th Dec.
>> Consumed During The Peiod = Items which were there on 1st Dec but are
>> either fully consumed and not there or their balance has gone down on
>> 10the dec.
>>
>> If any one of the above is managed then the remaining one will be the
>> balancing figure. So we will have to calculate correctly either Added or
>> Consumed items.
>>
>> I want to desing the worksheet in such a manner that I will provide
>> Opening Date and Closing Date fields to the user who will put the desired
>> dates. I will put the data validation at closing date so that it is always
>> greater than opening data and is there in the database. Rest of the grid
>> will be calculated automatically based on the two dates entered by the
>> user.
>>
>> Departments will be my rows and Opening Balance, Added, Consumed and
>> Closing Balance will be my columns of the report.
>>
>> IMPORTANT : Please note that I don't want item code wise totals anywhere.
>> My concern is count of all the items for a particular department
>> irrespective of their unit of measurement.
>>
>> I will keep one data base sheet in which the user will paste the dump from
>> my accounting software for desired dates in chronological order. I will
>> use dynamic range so that each new dump is automatically added up in the
>> data range.
>>
>> Please advise me on this. I have really gone mad thing over it.
>>
>> Regards,
>>
>> Rajesh
>>
>>
>>      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
>> http://in.yahoo.com/
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>> ------------------------------------
>>
>> ---
>>
>>     =========== Footer Revised 28 April 2006==========
>> PLEASE CHECK EXCEL HELP before posting a message to the forum
>>             and ask SMART questions
>> http://www.catb.org/~esr/faqs/smart-questions.html
>>
>> for normal live posts email <ms_excel-normal@yahoogroups.com>
>> for digest email <ms_excel-digest@yahoogroups.com>
>> for no mail email <ms_excel-nomail@yahoogroups.com>
>> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
>> ==============================================================Yahoo!
>> Groups Links
>>
>>
>>
>>
>
>
>
> --
>
> ===========================
> http://www.justgiving.com/thelongwayup2009
> 1017.8 miles from Land’s End to John
> O’Groats in 10 days for the Lingen
> Davies Cancer Relief fund and
> Primrose Hospice in Bromsgrove.
> ===========================
>
>
> ------------------------------------
>
> ---
>
>      =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>              and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo! Groups
> Links
>
>
>
>
>
>
>       The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
> http://in.yahoo.com/
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>      =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>              and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo! Groups
> Links
>
>
>
>

--
Sent from my mobile device


===========================
http://www.justgiving.com/thelongwayup2009
1017.8 miles from Land’s End to John
O’Groats in 10 days for the Lingen
Davies Cancer Relief fund and
Primrose Hospice in Bromsgrove.
===========================

Please find attached the file in CSV format.

--- On Fri, 25/12/09, Dr John C Bullas <john.bullas@...> wrote:


From: Dr John C Bullas <john.bullas@...>
Subject: Re: [ms_excel] Department Wise Items Quantity Summary & Movement
To: ms_excel@yahoogroups.com
Date: Friday, 25 December, 2009, 9:02 PM


please post this data as a CSV file or a workbook in files_2009

have you tried using pivot tables as you can select ranges and indeed
us another worksheet to tag data with values in response to this

if you have another worksheet with boundary values like
01/12/09 and 12/12/09

you can have a cell formula in the main database that uses a vlookup
to test whether (say) a date in one of these colums matches or is
included or is after or before these dates to set a column to BEFORE
AFTER or DURING that can be used to generate the pivot tables

for example, i am interested in a pivot table that only shows data
regarding road sections i want included (usually 20 odd from 500+)  I
set up a sorted list of these sections in another worksheet RELEVANT
SECTIONS and then generate a column called RELEVANT and the cells
contain an IF() based on the results of the lookup of the section name
for that row against the list in RELEVANT SECTION, if the vlookup
using false return nothing found, that cell is set to "NO" if the
vlookup finds the section name it returns "YES"

my pivot only ever displays results where the rows have RELEVANT as equal to YES

The pivot can dynamically recalculate if I change the vlookup list in
RELEVANT SECTIONS

Does this make sense?

Dr B
2009/12/25 RAJESH DESAI <rmdesai73@...>:
> Merry Christmas to All,
>
> I am pasting a sample data below.
>
>
>
>
>
>
>
>
> Date
> Department
> ItemCode
> Quantity
>
> 01-Dec-09
> FV
> Item1
> 10
>
> 01-Dec-09
> FV
> Item2
> 15
>
> 01-Dec-09
> CP
> Item3
> 25
>
> 01-Dec-09
> CP
> Item4
> 30
>
> 01-Dec-09
> FV
> Item5
> 17
>
> 01-Dec-09
> FV
> Item6
> 12
>
> 01-Dec-09
> CP
> Item7
> 8
>
> 10-Dec-09
> FV
> Item8
> 5
>
> 10-Dec-09
> CP
> Item9
> 33
>
> 10-Dec-09
> CP
> Item2
> 13
>
> 10-Dec-09
> CP
> Item3
> 2
>
> 10-Dec-09
> CP
> Item10
> 20
>
> 20-Dec-09
> FV
> Item7
> 5
>
> 20-Dec-09
> FV
> Item11
> 13
>
> 20-Dec-09
> FV
> Item12
> 44
>
> 20-Dec-09
> CP
> Item13
> 50
>
> 20-Dec-09
> CP
> Item10
> 4
>
> 20-Dec-09
> CP
> Item14
> 1
>
> I want to prepare a department wise report to show movement between two dates
ie 1st Dec and 10th dec in follwoing manner.
>
> Opening Balance = Department wise Count of all the items on 1st Dec.
> Closing Balance = Department wise Count of all the items on 10th Dec.
> Above two thing I have managed with array formula using combination of
functions sum and if.
>
> I am unable to manage following two calculations.
> Added During The Period = Items which were not there on 1st Dec but are on
10th Dec.
> Consumed During The Peiod = Items which were there on 1st Dec but are either
fully consumed and not there or their balance has gone down on 10the dec.
>
> If any one of the above is managed then the remaining one will be the
balancing figure. So we will have to calculate correctly either Added or
Consumed items.
>
> I want to desing the worksheet in such a manner that I will provide Opening
Date and Closing Date fields to the user who will put the desired dates. I will
put the data validation at closing date so that it is always greater than
opening data and is there in the database. Rest of the grid will be calculated
automatically based on the two dates entered by the user.
>
> Departments will be my rows and Opening Balance, Added, Consumed and Closing
Balance will be my columns of the report.
>
> IMPORTANT : Please note that I don't want item code wise totals anywhere. My
concern is count of all the items for a particular department irrespective of
their unit of measurement.
>
> I will keep one data base sheet in which the user will paste the dump from my
accounting software for desired dates in chronological order. I will use dynamic
range so that each new dump is automatically added up in the data range.
>
> Please advise me on this. I have really gone mad thing over it.
>
> Regards,
>
> Rajesh
>
>
>      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>     =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>             and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo! Groups
Links
>
>
>
>



--

===========================
http://www.justgiving.com/thelongwayup2009
1017.8 miles from Land’s End to John
O’Groats in 10 days for the Lingen
Davies Cancer Relief fund and
Primrose Hospice in Bromsgrove.
===========================


------------------------------------

---

     =========== Footer Revised 28 April 2006==========
PLEASE CHECK EXCEL HELP before posting a message to the forum
             and ask SMART questions
http://www.catb.org/~esr/faqs/smart-questions.html

for normal live posts email <ms_excel-normal@yahoogroups.com>
for digest email <ms_excel-digest@yahoogroups.com>
for no mail email <ms_excel-nomail@yahoogroups.com>
To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
==============================================================Yahoo! Groups
Links






       The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/

[Non-text portions of this message have been removed]

please post this data as a CSV file or a workbook in files_2009

have you tried using pivot tables as you can select ranges and indeed
us another worksheet to tag data with values in response to this

if you have another worksheet with boundary values like
01/12/09 and 12/12/09

you can have a cell formula in the main database that uses a vlookup
to test whether (say) a date in one of these colums matches or is
included or is after or before these dates to set a column to BEFORE
AFTER or DURING that can be used to generate the pivot tables

for example, i am interested in a pivot table that only shows data
regarding road sections i want included (usually 20 odd from 500+)  I
set up a sorted list of these sections in another worksheet RELEVANT
SECTIONS and then generate a column called RELEVANT and the cells
contain an IF() based on the results of the lookup of the section name
for that row against the list in RELEVANT SECTION, if the vlookup
using false return nothing found, that cell is set to "NO" if the
vlookup finds the section name it returns "YES"

my pivot only ever displays results where the rows have RELEVANT as equal to YES

The pivot can dynamically recalculate if I change the vlookup list in
RELEVANT SECTIONS

Does this make sense?

Dr B
2009/12/25 RAJESH DESAI <rmdesai73@...>:
> Merry Christmas to All,
>
> I am pasting a sample data below.
>
>
>
>
>
>
>
>
> Date
> Department
> ItemCode
> Quantity
>
> 01-Dec-09
> FV
> Item1
> 10
>
> 01-Dec-09
> FV
> Item2
> 15
>
> 01-Dec-09
> CP
> Item3
> 25
>
> 01-Dec-09
> CP
> Item4
> 30
>
> 01-Dec-09
> FV
> Item5
> 17
>
> 01-Dec-09
> FV
> Item6
> 12
>
> 01-Dec-09
> CP
> Item7
> 8
>
> 10-Dec-09
> FV
> Item8
> 5
>
> 10-Dec-09
> CP
> Item9
> 33
>
> 10-Dec-09
> CP
> Item2
> 13
>
> 10-Dec-09
> CP
> Item3
> 2
>
> 10-Dec-09
> CP
> Item10
> 20
>
> 20-Dec-09
> FV
> Item7
> 5
>
> 20-Dec-09
> FV
> Item11
> 13
>
> 20-Dec-09
> FV
> Item12
> 44
>
> 20-Dec-09
> CP
> Item13
> 50
>
> 20-Dec-09
> CP
> Item10
> 4
>
> 20-Dec-09
> CP
> Item14
> 1
>
> I want to prepare a department wise report to show movement between two dates
ie 1st Dec and 10th dec in follwoing manner.
>
> Opening Balance = Department wise Count of all the items on 1st Dec.
> Closing Balance = Department wise Count of all the items on 10th Dec.
> Above two thing I have managed with array formula using combination of
functions sum and if.
>
> I am unable to manage following two calculations.
> Added During The Period = Items which were not there on 1st Dec but are on
10th Dec.
> Consumed During The Peiod = Items which were there on 1st Dec but are either
fully consumed and not there or their balance has gone down on 10the dec.
>
> If any one of the above is managed then the remaining one will be the
balancing figure. So we will have to calculate correctly either Added or
Consumed items.
>
> I want to desing the worksheet in such a manner that I will provide Opening
Date and Closing Date fields to the user who will put the desired dates. I will
put the data validation at closing date so that it is always greater than
opening data and is there in the database. Rest of the grid will be calculated
automatically based on the two dates entered by the user.
>
> Departments will be my rows and Opening Balance, Added, Consumed and Closing
Balance will be my columns of the report.
>
> IMPORTANT : Please note that I don't want item code wise totals anywhere. My
concern is count of all the items for a particular department irrespective of
their unit of measurement.
>
> I will keep one data base sheet in which the user will paste the dump from my
accounting software for desired dates in chronological order. I will use dynamic
range so that each new dump is automatically added up in the data range.
>
> Please advise me on this. I have really gone mad thing over it.
>
> Regards,
>
> Rajesh
>
>
>      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>     =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>             and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo! Groups
Links
>
>
>
>



--

===========================
http://www.justgiving.com/thelongwayup2009
1017.8 miles from Land’s End to John
O’Groats in 10 days for the Lingen
Davies Cancer Relief fund and
Primrose Hospice in Bromsgrove.
===========================

Merry Christmas to All,
 
I am pasting a sample data below.
 







Date
Department
ItemCode
Quantity

01-Dec-09
FV
Item1
10

01-Dec-09
FV
Item2
15

01-Dec-09
CP
Item3
25

01-Dec-09
CP
Item4
30

01-Dec-09
FV
Item5
17

01-Dec-09
FV
Item6
12

01-Dec-09
CP
Item7
8

10-Dec-09
FV
Item8
5

10-Dec-09
CP
Item9
33

10-Dec-09
CP
Item2
13

10-Dec-09
CP
Item3
2

10-Dec-09
CP
Item10
20

20-Dec-09
FV
Item7
5

20-Dec-09
FV
Item11
13

20-Dec-09
FV
Item12
44

20-Dec-09
CP
Item13
50

20-Dec-09
CP
Item10
4

20-Dec-09
CP
Item14
1
 
I want to prepare a department wise report to show movement between two dates ie
1st Dec and 10th dec in follwoing manner.
 
Opening Balance = Department wise Count of all the items on 1st Dec.
Closing Balance = Department wise Count of all the items on 10th Dec.
Above two thing I have managed with array formula using combination of functions
sum and if.
 
I am unable to manage following two calculations.
Added During The Period = Items which were not there on 1st Dec but are on 10th
Dec.
Consumed During The Peiod = Items which were there on 1st Dec but are either
fully consumed and not there or their balance has gone down on 10the dec.
 
If any one of the above is managed then the remaining one will be the balancing
figure. So we will have to calculate correctly either Added or Consumed items.
 
I want to desing the worksheet in such a manner that I will provide Opening Date
and Closing Date fields to the user who will put the desired dates. I will put
the data validation at closing date so that it is always greater than opening
data and is there in the database. Rest of the grid will be calculated
automatically based on the two dates entered by the user.
 
Departments will be my rows and Opening Balance, Added, Consumed and Closing
Balance will be my columns of the report.
 
IMPORTANT : Please note that I don't want item code wise totals anywhere. My
concern is count of all the items for a particular department irrespective of
their unit of measurement.
 
I will keep one data base sheet in which the user will paste the dump from my
accounting software for desired dates in chronological order. I will use dynamic
range so that each new dump is automatically added up in the data range.
 
Please advise me on this. I have really gone mad thing over it.
 
Regards,
 
Rajesh


       The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/

[Non-text portions of this message have been removed]

Hi Robert

Glad you got it solved to your satisfaction.

> Although I've had one request to keep the discussion off list, ...

Goodness knows why.  IMHO it was all perfectly relevant to an Excel list.

> I hope you all have a great holiday and new year.

And to you, and all list members.

Regards, Dave S

----- Original Message -----
From: "Robert Schroeder, Jr." <robert2b2@...>
To: "Excel" <ms_excel@yahoogroups.com>
Sent: Friday, December 25, 2009 7:16 AM
Subject: [ms_excel] rounding and whatnot solved - sort of


> Well, I came up with a method of getting basically what I need and I
> thought
> I'd share.
>
> I have pivot tables that deal with material, use, length and number of
> pieces.  Many items are ordered in custom lengths so they are pretty much
> set.  Those that are ordered in standard lengths (24' for those who hadn't
> paid attention the first time around) end up listed as number of these
> lengths - to two decimals.  Well, items need to be ordered in whole units
> without under ordering or excessive waste - that was the balance that
> needed
> to be struck.
>
> I ended up creating a calculated field in the pivot table which uses this
> formula - Pcs. is the name of the field where units is listed:
>
> =IF(MOD(Pcs.,1)>0,INT(Pcs.)+1,INT(Pcs.))
>
> I then went in through the wizard and removed the Pieces field from the
> display replacing it with the created totals field making sure that
> anything
> that was already listed in whole units isn't affected but those items that
> need to be are broken down to integers and then increased by 1 unit.
>
> Although I've had one request to keep the discussion off list, I think
> there
> might be others out there that might find this method useful and as I've
> gotten a lot from the group, it is only fair to share what I come across.
>
> I hope you all have a great holiday and new year.
>
> Bob
>
> --
> There is a big difference between allowing people the freedom to do as
> they
> wish and forcing others to condone and/or cover, in last resort, those
> chosen actions or behaviors - the first is Liberty and the other, Tyranny.
>
> Never forget that the best way to promote the good of the many is to
> protect
> the rights of the one.
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ---
>
>     =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
>             and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email <ms_excel-normal@yahoogroups.com>
> for digest email <ms_excel-digest@yahoogroups.com>
> for no mail email <ms_excel-nomail@yahoogroups.com>
> To UNSUBSCRIBE email <ms_excel-unsubscribe@yahoogroups.com>
> ==============================================================Yahoo!
> Groups Links
>
>
>


--------------------------------------------------------------------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.430 / Virus Database: 270.14.118/2584 - Release Date: 12/23/09
19:02:00

Well, I came up with a method of getting basically what I need and I thought
I'd share.

I have pivot tables that deal with material, use, length and number of
pieces.  Many items are ordered in custom lengths so they are pretty much
set.  Those that are ordered in standard lengths (24' for those who hadn't
paid attention the first time around) end up listed as number of these
lengths - to two decimals.  Well, items need to be ordered in whole units
without under ordering or excessive waste - that was the balance that needed
to be struck.

I ended up creating a calculated field in the pivot table which uses this
formula - Pcs. is the name of the field where units is listed:

=IF(MOD(Pcs.,1)>0,INT(Pcs.)+1,INT(Pcs.))

I then went in through the wizard and removed the Pieces field from the
display replacing it with the created totals field making sure that anything
that was already listed in whole units isn't affected but those items that
need to be are broken down to integers and then increased by 1 unit.

Although I've had one request to keep the discussion off list, I think there
might be others out there that might find this method useful and as I've
gotten a lot from the group, it is only fair to share what I come across.

I hope you all have a great holiday and new year.

Bob

--
There is a big difference between allowing people the freedom to do as they
wish and forcing others to condone and/or cover, in last resort, those
chosen actions or behaviors - the first is Liberty and the other, Tyranny.

Never forget that the best way to promote the good of the many is to protect
the rights of the one.


[Non-text portions of this message have been removed]

Perhaps, when I can afford one, I will.  When I get to that point I will
also completely redo the estimator on the whole and try to sell it.  Until
then I have to do what I can with what I have.

Thanks.

On Wed, Dec 23, 2009 at 4:21 PM, David Smart <smartware.consulting@...
> wrote:

>
>
> It sounds as though this is a commercial enterprise. Why not do it properly
>
> and get a pro programmer to write it for you?
>
> Regards, Dave S
>
>
> ----- Original Message -----
> From: "Robert Schroeder, Jr." <robert2b2@... <robert2b2%40gmail.com>
> >
> To: <ms_excel@yahoogroups.com <ms_excel%40yahoogroups.com>>
>  Sent: Thursday, December 24, 2009 1:42 AM
> Subject: Re: [ms_excel] rounding question
>
> John,
>
> The only problems are that I do not do any coding and the process needs to
> work in one package - everything in the same workbook, I mean. If this
> means that some built-in over ordering occurs I can deal with it in the
> short term as I take anything that happens to be on hand in consideration
> when placing orders in order to minimize orders and use up stock.
>
> Thanks,
> Bob
>
> On Wed, Dec 23, 2009 at 9:38 AM, Dr John C Bullas
> <john.bullas@... <john.bullas%40gmail.com>>wrote:
>
> > Bob
> >
> > what you need is to be able to input a job list and a VB application
> > to output a bill of quantities to minimise waste
> >
> > there must be some code out there to do this
> >
> > JB
> >
> > 2009/12/23 Robert Schroeder, Jr. <robert2b2@...<robert2b2%40gmail.com>
> >:
> > > You're not sick - you're like me....maybe we're both sick....
> > >
> > > Actually, I panelize wall and floor systems in light gauge steel. We do
> > not
> > > order material to have on hand but just for the job in question and as
> > the
> > > economy is what it is there is no needing of any quantity on a regular
> > basis
> > > - just the amounts needed for the job at hand. From the lunber
> > perspective,
> > > think of it as I'm trying to order top and bottom plate material in
> 24's
> > as
> > > the actual lengths vary too much to order actual lengths needed.
> > Therefore,
> > > I need to know what percentage of a 24 each panel will require and then
> > > total everything up so I order the right number of 24's with minimal
> > waste
> > > and no shortage. Of course, steel has more variables that wood. In
> > wood,
> > > you have a 2x6 but in steel the width might be 6" but the thickness can
> > vary
> > > between 1.375, 1.625, 2.00, 2.50, 3.00 and 3.50 with the thickness of
> > > the
> > > steel itself (being a shape instead of a solid) being chosen from five
> > > possibilities - 33, 43, 54, 68 and 97 mils.
> > >
> > > From your experience you understand what I'm working towards though.
> > >
> > > I've never seen this "solver" but I'll look to see if 2000 has it.
> > >
> > > Thanks,
> > > Bob
> > >
> > > On Wed, Dec 23, 2009 at 9:16 AM, Paul Schreiner <
> schreiner_paul@... <schreiner_paul%40att.net>
> > >wrote:
> > >
> > >>
> > >>
> > >> I've done something similar, but not on this large of a scale.
> > >> It is usually with lumber.
> > >> (is it better to purchase 2x4x12's or 2x4x8 and 2x4x10's...)
> > >>
> > >> The other problem is that you need to factor in the "discarded"
> > material.
> > >> let's say you have a product that requires 33" of material.
> > >> A 24' length yields (8) 33" pieces, with (1) 24" piece left over.
> > >>
> > >> Now, another product requires 23" pieces
> > >> and another requires 11" pieces... it is better to use the leftover
> > >> piece to make the 23" product rather than (2) 11" products,
> > >> since the discarded portion is then 1" rather than 2"...
> > >>
> > >> On the other hand, if you only make (6) of the 33" pieces,
> > >> that leaves 90" of stock, which is perfect for making (2) of the 45"
> > pieces
> > >> without ANY leftover material...
> > >>
> > >> Making a SPREADSHEET make these kind of decisions is difficult.
> > >> But I THINK Excel's Solver application was meant to do that.
> > >> But I'm not sure if I can set it up properly.
> > >>
> > >> If I understand your problem correctly,
> > >> you have several "products" that are made from 24' stock.
> > >> For a given time period (week, month or day) you plan to manufacture
> > >> a specific quantity of each of the different products, and you want
> > >> to know the MINIMUM number of 24' pieces to order to make the
> > >> production run.
> > >> Do you have a factor for scrap-rate?
> > >>
> > >> Would you mind sending me your template?
> > >> and perhaps some sample production quantities?
> > >>
> > >> I've played around with the Solver a couple of times, but
> > >> without a real-life goal to work with, it's just "playing"...
> > >>
> > >> I know it's kind-of "sick", but it actually sounds like fun!
> > >>
> > >> lol
> > >>
> > >> Paul
> > >>
> > >>
> > >> ----- Original Message ----
> > >> > From: "Robert Schroeder, Jr."
<robert2b2@...<robert2b2%40gmail.com>
> <robert2b2%
> > 40gmail.com>
> > >> >
> > >> > To: ms_excel@yahoogroups.com <ms_excel%40yahoogroups.com><ms_excel%
> 40yahoogroups.com>
> > >> > Sent: Wed, December 23, 2009 8:30:42 AM
> > >> > Subject: Re: [ms_excel] rounding question
> > >> >
> > >> > That is an interesting way to do it but I think if I need to know
> how
> > >> many
> > >> > single units (that are 24' in length) that I need to order.the
> > >> > formula
> > >> might
> > >> > read like this instead: int(number/24)+1 - wouldn't it? If not, why?
> > >> >
> > >> > I'll try this method in a test version of the sheet to see how
> things
> > go.
> > >> > To further expand upon the process, I'll add this:
> > >> >
> > >> > On one worksheet a chart is filled out with each row representing a
> > >> product
> > >> > we manufacture and each of the columns represent the various
> > variations
> > >> > possible for each item.
> > >> >
> > >> > On another sheet a table is automatically filled out listing the
> > choices
> > >> > made on the first page. As there is no way to know how many items
> > there
> > >> > will be in a particular order, it is sometimes necessary to drag the
> > rows
> > >> of
> > >> > formulas down to include everything.
> > >> >
> > >> > A third sheet tabulates different types of material that is used in
> > >> > different parts of the overall so that a pivot table on another
> sheet
> > can
> > >> be
> > >> > used to make a chart that distills the totality of the information
> > down
> > >> to
> > >> > what is, essentially, an order sheet of material, length (some is
> > ordered
> > >> in
> > >> > standard lengths and some in custom lengths) and quantities.
> > >> >
> > >> > That which I'm trying to do now is refine and tighten the ordering
> of
> > the
> > >> > items that are ordered in standard lengths.
> > >> >
> > >> > Whew!
> > >> >
> > >> > Thanks again,
> > >> > Bob
> > >> >
> > >> > On Wed, Dec 23, 2009 at 1:49 AM, Dr John C Bullas wrote:
> > >> >
> > >> > >
> > >> > >
> > >> > > So basically you know how much absolute length you need to buy to
> > >> > > complete works in the shop
> > >> > >
> > >> > > so if your works total 35' you must order 48'
> > >> > >
> > >> > > if your works total 49' you must order the next multiple along
> > 48'+24'
> > >> =
> > >> > > 72'
> > >> > >
> > >> > > now you can look at your value divided by 24' and up it to the
> next
> > >> one?
> > >> > >
> > >> > > (int(number/24)*24) +24 will give you the total you need plus the
> > bit
> > >> > > to give you some wastage not a shortage
> > >> > >
> > >> > > number is the total length of work you have need to do
> > >> > >
> > >> > > check this please
> > >> > >
> > >> > > JB
> > >> > >
> > >> > >
> > >> > > >> > My business uses materials that are ordered in 24' lengths
> and
> > >> will be
> > >> > > >> cut
> > >> > > >> > into the needed lengths in the shop. While I do not want to
> > over
> > >> > > order, I
> > >> > > >> > cannot under order given the delay in production that would
> be
> > >> caused.
> > >> > > >> > Does
> > >> > > >> > anyone know how to achieve rounding up by multiples
> > >> > > >> > (fractions,
> > in
> > >> > > this
> > >> > > >> > case) so I can get my ordering done right - in 2000?
> > >> > > >> >
> > >> > > >> > Thanks,
> > >> > > >> > Bob
> > >> > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >> >
> > >> > --
> > >> > There is a big difference between allowing people the freedom to do
> > >> > as
> > >> they
> > >> > wish and forcing others to condone and/or cover, in last resort,
> > >> > those
> > >> > chosen actions or behaviors - the first is Liberty and the other,
> > >> Tyranny.
> > >> >
> > >> > Never forget that the best way to promote the good of the many is to
> > >> protect
> > >> > the rights of the one.
> > >> >
> > >> >
> > >> > [Non-text portions of this message have been removed]
> > >> >
> > >> >
> > >> >
> > >> > ------------------------------------
> > >> >
> > >> > ---
> > >> >
> > >> > =========== Footer Revised 28 April 2006==========
> > >> > PLEASE CHECK EXCEL HELP before posting a message to the forum
> > >> > and ask SMART questions
> > >> > http://www.catb.org/~esr/faqs/smart-questions.html
> > >> >
> > >> > for normal live posts email
> > >> > for digest email
> > >> > for no mail email
> > >> > To UNSUBSCRIBE email
> > >> > ==============================================================Yahoo!
> > >> Groups
> > >> > Links
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> > >
> > >
> > >
> > > --
> > > There is a big difference between allowing people the freedom to do as
> > they
> > > wish and forcing others to condone and/or cover, in last resort, those
> > > chosen actions or behaviors - the first is Liberty and the other,
> > Tyranny.
> > >
> > > Never forget that the best way to promote the good of the many is to
> > protect
> > > the rights of the one.
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > ---
> > >
> > > =========== Footer Revised 28 April 2006==========
> > > PLEASE CHECK EXCEL HELP before posting a message to the forum
> > > and ask SMART questions
> > > http://www.catb.org/~esr/faqs/smart-questions.html
> > >
> > > for normal live posts email
<ms_excel-normal@yahoogroups.com<ms_excel-normal%40yahoogroups.com>
> >
> > > for digest email
<ms_excel-digest@yahoogroups.com<ms_excel-digest%40yahoogroups.com>
> >
> > > for no mail email
<ms_excel-nomail@yahoogroups.com<ms_excel-nomail%40yahoogroups.com>
> >
> > > To UNSUBSCRIBE email
<ms_excel-unsubscribe@yahoogroups.com<ms_excel-unsubscribe%40yahoogroups.com>
> >
> > > ==============================================================Yahoo!
> > Groups Links
> > >
> > >
> > >
> > >
> >
> >
> >
> > --
> >
> > ===========================
> > http://www.justgiving.com/thelongwayup2009
> > 1017.8 miles from Land's End to John
> > O'Groats in 10 days for the Lingen
> > Davies Cancer Relief fund and
> > Primrose Hospice in Bromsgrove.
> > ===========================
> >
> >
> > ------------------------------------
> >
> > ---
> >
> > =========== Footer Revised 28 April 2006==========
> > PLEASE CHECK EXCEL HELP before posting a message to the forum
> > and ask SMART questions
> > http://www.catb.org/~esr/faqs/smart-questions.html
> >
> > for normal live posts email
<ms_excel-normal@yahoogroups.com<ms_excel-normal%40yahoogroups.com>
> >
> > for digest email
<ms_excel-digest@yahoogroups.com<ms_excel-digest%40yahoogroups.com>
> >
> > for no mail email
<ms_excel-nomail@yahoogroups.com<ms_excel-nomail%40yahoogroups.com>
> >
> > To UNSUBSCRIBE email
<ms_excel-unsubscribe@yahoogroups.com<ms_excel-unsubscribe%40yahoogroups.com>
> >
> > ==============================================================Yahoo!
> > Groups
> > Links
> >
> >
> >
> >
>
> --
> There is a big difference between allowing people the freedom to do as they
> wish and forcing others to condone and/or cover, in last resort, those
> chosen actions or behaviors - the first is Liberty and the other, Tyranny.
>
> Never forget that the best way to promote the good of the many is to
> protect
> the rights of the one.
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> ---
>
> =========== Footer Revised 28 April 2006==========
> PLEASE CHECK EXCEL HELP before posting a message to the forum
> and ask SMART questions
> http://www.catb.org/~esr/faqs/smart-questions.html
>
> for normal live posts email
<ms_excel-normal@yahoogroups.com<ms_excel-normal%40yahoogroups.com>
> >
> for digest email
<ms_excel-digest@yahoogroups.com<ms_excel-digest%40yahoogroups.com>
> >
> for no mail email
<ms_excel-nomail@yahoogroups.com<ms_excel-nomail%40yahoogroups.com>
> >
> To UNSUBSCRIBE email
<ms_excel-unsubscribe@yahoogroups.com<ms_excel-unsubscribe%40yahoogroups.com>
> >
> ==============================================================Yahoo! Groups
>
> Links
>
> ----------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.430 / Virus Database: 270.14.117/2582 - Release Date: 12/22/09
>
> 18:22:00
>
>
>



--
There is a big difference between allowing people the freedom to do as they
wish and forcing others to condone and/or cover, in last resort, those
chosen actions or behaviors - the first is Liberty and the other, Tyranny.

Never forget that the best way to promote the good of the many is to protect
the rights of the one.


[Non-text portions of this message have been removed]

Hi Experts,

I am looking for two solutions:

I have a file and Column A has part names, each name is more than once.

Example:

A2=abc
A3=abc
A4=abc
A5=bcd
A6=bcd
A7=cde

1) I want to apply a formula to change color of row A4 based on formula IF
A4<>A5.
2) I want to GO TO each row based on above formula, directly.

Thanks for your help,
Regards,
Dinesh






       The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/

[Non-text portions of this message have been removed]