3 Replies Latest reply: May 15, 2014 10:16 AM by econ664 _ RSS

    Lookup values in an excel file?

    econ664 _

      Hello,

       

      Having opened an excel file as a database, I would like to make a new calculated field that simply gets the value of a specific cell. This cell is always going to be in the same position, say A5. How would I go about that? I am not very familiar with all the different functions in Monarch.

       

      I saw that you can do a Lookup as a calculated field. Is there a function to return the value of the specific cell that it looks up?

       

      Thanks,

        • Lookup values in an excel file?
          Olly Bond

          Hello Econ664,

           

          I'm afraid that can't be done easily.

           

          Monarch doesn't know what row 5 is in the Excel file.

           

          You could at a push define a named range containing A5 but that on its own won't be enough. You'll need at least one other cell to act as the joining key.

           

          I sent you a PM suggesting a contact who might be able to help - did you get that safely?

           

          Best wishes,

           

          Olly

          • Lookup values in an excel file?
            Grant Perkins

            To clarify things here - if you read in an Excel file as a database there is an assumption that what you will be bringing in is a data table that presents a reasonable set of fields in predictable columns or can be made to do so with a little additional work.

             

            In that case you would simply be seeking a value in column A  - which would be a field of some sort that, hopefully, always contains consistent and consistently useful information.

             

            A Lookup would normally imply comparing the value of a field in that database with another value, either from an Internal Lookup Table or an External file that offers a means to make the connection via a Lookup key, and deriving a value for a new field. That value may simply be a direct lookup (for example a Tax code that connects to a tax rate), or a calculated value  - say a tax code AND a date that looks up the rate for the day of a transaction and has a formula included to return a calculated tax amount.

             

            Reading your post I think you are simply looking for a value in your imported Excel file which will now be presented in tabular form. So you are looking for a specific value somewhere on column A. If that is the case you probably just need a calculated field that duplicates the value found in A but perhaps only when it has a specific value. Is that about right?

             

            Or is "A5", as imported, the equivalent of some sort of Page Header field rather than a straight data table? If so does it represent data that needs to be appended to all of the other data rows? If that is the sort of thing you are looking for then a different approach would be required and there are several options available to address the need - it would be good to have a slightly more certain understanding of the need before suggesting how to approach the challenge.

             

            Would it be possible to post an example or a mocked up example of the roughly how the table looks after the Excel file has been imported?

             

             

            Grant

              • Lookup values in an excel file?
                econ664 _

                Thanks for your responses. Olly I got your PM and sent you a message.

                 

                Grant, basically what I am trying to do is use Monarch to rearrange the excel file's data into a nice .csv, without all the formatting and blank spaces that are in the original file. This file will be given every month with the same exactly layout, and I know which cells are the ones I need for the data. I would like to do two "passes", one that picks up what I would call MetaData at the top of the file, and one that picks up Data below.

                 

                Maybe I'm trying to use Monarch for the wrong purpose, and would be better off writing a script with an excel macro that can do all this. Right now I'm trying to write filters that can grab the data I want.