3 Replies Latest reply on Feb 28, 2014 10:28 PM by synergy46

    Excel range names not recognized ... huh?

    synergy46

      I was successfully importing an Excel v 11 file into my FM data then I discovered that Excel allows for the creation of 'dynamic' range names. Kewl!

       

      So, I change my Excel range definition to =OFFSET(INFO!$R$12,0,0,COUNTA(INFO!$R$12:INFO!$AD$612),13) and suddenly FM 13 does not recognize my ranges?

       

      Yes, if I make the range a typical $A$1:$G$22 the range name works.

       

      Waz up with that?

       

      Is there a work around? As is I define the range as 1000 rows, import all 1000 rows then proceed to delete the rows that have just a PK... What a waste...

       

      Thanks for your thoughts.

        • 1. Re: Excel range names not recognized ... huh?
          mikebeargie

          The workaround would be to export your named range from excel as a flat file, or re-create the conditions in post-processing after import of the non-dynamic range in FileMaker.

           

          I would not expect FM (or ANY program aside from excel) to recognize dynamic ranges. It's more of a program feature, not a file format feature.

           

          Are you doing a recurring import from said excel document, or using it as a data source?

          • 2. Re: Excel range names not recognized ... huh?
            synergy46

            It is a 'one shot' data source.  ie, the users have stored their historical data in Excel and need to bring it into my app.

             

            The easiest solution for me is to import all the data (including the blank records) then delete all the blank records and reset the Primary Key...

             

            setserial.png

            Thanks for the idea.

            • 3. Re: Excel range names not recognized ... huh?
              synergy46

              Here is an update:

              I removed the dynamic ranges fromt he spreadhseet and instead created 2 standard ranges;  they work.

               

              When I run the import script with the "PERFORM WITHOUT DIALOG" box *NOT* checked, FM prompts

              for the range.  I select it and all is well.

               

              However, if I check "PERFORM WITHOUT DIALOG", FM fails to remember the previously selected range

              and seems to select only the 1st range.

               

              wodialog.png

              Question: "Is there a way to get FM to remember the previously selected Range?

               

              Thanks

              Ron