Converting Data Automatically In Excel

[susejdrol]

Team,

I have an Excel workbook that is used to generate a seating chart that displays relevant data by code. Currently, the way that it works is that I copy and paste the data from another workbook, then I do a past special with values to eleimate formulas, and then run a coversion macro that I built which coverts the information to numbers.

For example, in the original sheet, schedules are listed as "8AM to 5PM", "7AM to 4PM", etc. and the macro converts those to numeric values between 1 and 11 depending on which schedule it is with a series of find and replaces. It does the same thing with a few other columns. Then, the seating chart sheet pulls the relevant data and displays it.

What I would like to do is eliminate the need to copy and paste by bringing the sta over automatically, which is not a problem.

The piece that I am puzzled on, though, is how I can then get the data to be automatically converted to the proper numerical equivalaent without having to do the paste special (which would eliminate all of the formulas that I have used to to pull the data and put it in the correct palces) and without having to run the macro. That way I can make changes to the source document and the seating chart will automatically be correct at all times.

Let me know if anyone has any ideas.

Thanks for the help in advance.

CP

 

[Zathras]

If I understand you correctly, you are now using formulas in one book to reference the data from another. So all you need is a formula that you could use like this:
[blue]

  =MYSPECIALDATEFORMAT([Book1]Sheet1!$B$3)

[/color]
In that case, post the details of how you convert one date format into the other and I or someone can write a User-Defined function that will do the job.

 

[susejdrol]

The macro literally just does a find and replace. After I do a paste special so that there are no fomulas in a call, it goes in and replaces every instance of "5AM to 9AM" with 1, every instance of "5AM to 2PM" with 2, etc. up though 11 differant schedules. Then it coverts 18 differant team lead names to numbers as well.

What I am trying to is write it so that it refercnes the other book, and when the lead and schedule information come in, it will convert everything to the appropriate numerical value.

This way, I don't have to do the paste special (which would lose all of the fomulas that did the referenceing) and I don't have to run the macro. This would allow me to update the links when I open the file and then know that everthing will be right.

Let em know what you think

Thanks

CP

 

[Zathras]

As I indicated, you can use a User-Defined function. If you put this in a code module:
[blue]

Function TimeCategory(TimeRange As String) As Integer
  Select Case TimeRange
    Case "5AM to 9AM": TimeCategory = 1
    Case "5AM to 2PM": TimeCategory = 2

[green]

    ' etc.

[/color][/code]
Case Else: TimeCategory = 0
End Select
End Function
[/code][/color]
then you can use this in the worksheet:
[blue]

  =TimeCategory([Book2]Sheet1!B5)

[/color]
A similar function can handle team lead names.

Or, if you don't want to use a UDF, you can use VLOOKUP formulas with the conversion data stored somewhere out of the way in your worksheet. Might actually be better that way because it is easier to maintain when the conversion criteria change.