Just a few questions.

[GermanyKorea]

So I wrote this nice data entry and log spreadsheet w/ macros for my job. A few questions have cropped up during the testing of it.

1) The data that we want to sort the log sheet by comes in this format
1-08, 2-08, 3-08, 4-08,....,1-09,2-09,3-09 and so on. Now, the only thing I've managed to get excel to do is sort them as text or they get turned into dates. When sorted by text, it sorts by the number before the dash first. I need it to first sort by the number after the dash. Is there a quick way to do this? The sort command is in the VBA code.

2) When the coworker that was testing this did a save-as and changed the file's name for a second log, the data that he entered in the new one logged into the old one as well. Does anyone know how this could've happened and/or how to prevent it from happening?

Thank you much!

 

[Azerty99]

Excel and dates - the old classic.

Try setting the column read to text only when reading the data into Excel.

 

[GermanyKorea]

No, that's just it, they aren't dates, I don't want them to be dates, they're lot numbers. The problem is that I need them to sort as if they were dates. They are being copied into the log file as text right now.

 

[ChrisM]

I'd say your best bet is to make a new column right next door, and reformat your data, then use the new column to do your sort.

So if your data is in column A, you make a new column B, with a formula:

=Right(A1,2)

and drag it all the way down, then sort using column B.

 

[GermanyKorea]

Thanks, ChrisM, I just put in the update macro to insert the column, put that formula in each of those cells, then sort it by that new column, then delete the column =P.

Can anyone help with question 2?

 

[GermanyKorea]

I really do hate double-posting, but I'm working on the same spreadsheet and need to know this as soon as I can.

Ok, so now I've got all the shinies of th sorting and logging. Now we're making plots. I've already figured out how to make the chart dynamically update its data, with new data entered and only showing the last X-entries. The problem is finding X.

I want the chart to show up to the last year's worth of entries. However, the dates are not one of the data lines on the chart. Also, the dates are a bit erratic and not something I can easily say, "Oh well I'll just put in the last x-number, that's always a year's worth."

What I want to know is if there is a good way of counting the number of dates in a list that are greater than the last date of the list-1 year? I'd even settle for a method of finding the earliest date in a list that occurs after a specified date. I tried using the MATCH function, but it only works with that aspect correctly if the list is in decending order, and this list needs to stay in ascending order.

Quick answers are appreciated! Thank you!

 

[Azerty99]

OK given a chance to repreive:

Countif function

?

 

[GermanyKorea]

I've tried countif, the problem is that as far as I can tell, it can only do criteria in relation values that don't change, i.e. countif(D:D,">0"), or countif(D:D,"=23"), or to exact contents of cells, like countif(D:D,E2).

What I need is to make it do something more like =countif(D:D,>E2). And I havent' been able to figure out how to make it do that.

 

[Azerty99]

Ok the long way:
Insert a column
if date >= specified date (e3) then 1 else 0
sum = count