A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

formatting a spreadsheet help



 
 
Thread Tools Display Modes
  #1  
Old May 1st, 2010, 03:08 PM posted to microsoft.public.excel.worksheet.functions
HJ
external usenet poster
 
Posts: 24
Default formatting a spreadsheet help

I have a few different dilemas going on here.
First: Can a conditional format be set up in Vista or Windows 7 both 64 bit,
and saved in 97-2003 excel saving the conditional formats?
Second: I have a spreadsheet column A is my received date, Column B is Stock
#, Column C is Serial #, Column G is my finished date.
What I need to be able to do: When I enter information into Column C (Serial
#), I need some type of notification if that same number has been entered
within a six month time period from my Column G (Finished date).

  #2  
Old May 1st, 2010, 03:23 PM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default formatting a spreadsheet help

1. Yes. However, there are some formats which only Excel 2007 can handle.
But when you save the file, Excel will tell you if you've done anything that
can't be supported in 2003. If you get that message, then fix those items.

2. You'll likely need to give an example to get a decent answer to this
question. How do you want a serial number (column C) compared to a date
(column G)?

Regards,
Fred

"hj" wrote in message
...
I have a few different dilemas going on here.
First: Can a conditional format be set up in Vista or Windows 7 both 64
bit,
and saved in 97-2003 excel saving the conditional formats?
Second: I have a spreadsheet column A is my received date, Column B is
Stock
#, Column C is Serial #, Column G is my finished date.
What I need to be able to do: When I enter information into Column C
(Serial
#), I need some type of notification if that same number has been entered
within a six month time period from my Column G (Finished date).


  #3  
Old May 1st, 2010, 05:04 PM posted to microsoft.public.excel.worksheet.functions
HJ
external usenet poster
 
Posts: 24
Default formatting a spreadsheet help

Thank you Fred for your quick response. I have to track the serial # for
warranty purposes within a six month period. A customer gives me something to
fix, I give him a six month warranty, but if the same part (tracked by the
serial #, column C) comes back to me within six months from the finished
date(column G) and I enter the same Serial # into my spreadsheet is there a
way to let me know such Serial #(column C) was entered within the last six
months?

"Fred Smith" wrote:

1. Yes. However, there are some formats which only Excel 2007 can handle.
But when you save the file, Excel will tell you if you've done anything that
can't be supported in 2003. If you get that message, then fix those items.

2. You'll likely need to give an example to get a decent answer to this
question. How do you want a serial number (column C) compared to a date
(column G)?

Regards,
Fred

"hj" wrote in message
...
I have a few different dilemas going on here.
First: Can a conditional format be set up in Vista or Windows 7 both 64
bit,
and saved in 97-2003 excel saving the conditional formats?
Second: I have a spreadsheet column A is my received date, Column B is
Stock
#, Column C is Serial #, Column G is my finished date.
What I need to be able to do: When I enter information into Column C
(Serial
#), I need some type of notification if that same number has been entered
within a six month time period from my Column G (Finished date).


.

  #4  
Old May 1st, 2010, 10:30 PM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default formatting a spreadsheet help

I would use a formula like:
=sumproduct((c$1:c100=c101)*(g$1:g100today()-180))
and put this in, say, column H. Adjust the ranges to suit.

This will give you the number of times the given serial number appears in
the previous 180 days. In other formulas (or conditional formats) you can
check to see if the number is greater than 0. If you really need 6 months,
rather than 180 days, use: date(year(today()),month(today()-6),day(today())

Regards,
Fred

"hj" wrote in message
...
Thank you Fred for your quick response. I have to track the serial # for
warranty purposes within a six month period. A customer gives me something
to
fix, I give him a six month warranty, but if the same part (tracked by the
serial #, column C) comes back to me within six months from the finished
date(column G) and I enter the same Serial # into my spreadsheet is there
a
way to let me know such Serial #(column C) was entered within the last six
months?

"Fred Smith" wrote:

1. Yes. However, there are some formats which only Excel 2007 can handle.
But when you save the file, Excel will tell you if you've done anything
that
can't be supported in 2003. If you get that message, then fix those
items.

2. You'll likely need to give an example to get a decent answer to this
question. How do you want a serial number (column C) compared to a date
(column G)?

Regards,
Fred

"hj" wrote in message
...
I have a few different dilemas going on here.
First: Can a conditional format be set up in Vista or Windows 7 both 64
bit,
and saved in 97-2003 excel saving the conditional formats?
Second: I have a spreadsheet column A is my received date, Column B is
Stock
#, Column C is Serial #, Column G is my finished date.
What I need to be able to do: When I enter information into Column C
(Serial
#), I need some type of notification if that same number has been
entered
within a six month time period from my Column G (Finished date).


.


  #5  
Old May 4th, 2010, 08:04 PM posted to microsoft.public.excel.worksheet.functions
Billns
external usenet poster
 
Posts: 88
Default formatting a spreadsheet help

On 5/1/2010 2:30 PM, Fred Smith wrote:
I would use a formula like:
=sumproduct((c$1:c100=c101)*(g$1:g100today()-180))
and put this in, say, column H. Adjust the ranges to suit.

This will give you the number of times the given serial number appears
in the previous 180 days. In other formulas (or conditional formats) you
can check to see if the number is greater than 0. If you really need 6
months, rather than 180 days, use:
date(year(today()),month(today()-6),day(today())

Regards,
Fred

"hj" wrote in message
...
Thank you Fred for your quick response. I have to track the serial # for
warranty purposes within a six month period. A customer gives me
something to
fix, I give him a six month warranty, but if the same part (tracked by
the
serial #, column C) comes back to me within six months from the finished
date(column G) and I enter the same Serial # into my spreadsheet is
there a
way to let me know such Serial #(column C) was entered within the last
six
months?

"Fred Smith" wrote:

1. Yes. However, there are some formats which only Excel 2007 can
handle.
But when you save the file, Excel will tell you if you've done
anything that
can't be supported in 2003. If you get that message, then fix those
items.

2. You'll likely need to give an example to get a decent answer to this
question. How do you want a serial number (column C) compared to a date
(column G)?

Regards,
Fred

"hj" wrote in message
...
I have a few different dilemas going on here.
First: Can a conditional format be set up in Vista or Windows 7
both 64
bit,
and saved in 97-2003 excel saving the conditional formats?
Second: I have a spreadsheet column A is my received date, Column B is
Stock
#, Column C is Serial #, Column G is my finished date.
What I need to be able to do: When I enter information into Column C
(Serial
#), I need some type of notification if that same number has been
entered
within a six month time period from my Column G (Finished date).


.


Give the customers a break! Make it 183 days, which is half a leap year.

Bill
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.