+ Reply to Thread
Results 1 to 14 of 14

Specify a variable table

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    UK, Newcastle
    MS-Off Ver
    Office 365
    Posts
    97

    Specify a variable table

    I am trying to create a spread sheet to allow easy data entry into another piece of software.
    Firstly a DSI file is exported from the software which can be opened in Excel.
    The first block of data needed appears in the first column between rows which are "!terminals" and "!".
    As the "!" entry is not unique I may have to use the next row of "!Harness extra node components".
    Is there a way to specify the data between the two mentioned above as a table for lookup purposes?
    I need the data hi-lighted in green to be formatted into a table as per TAB 2 (connector table).
    Only the first two parts of the row are required, the connector reference CONN1, SP001 Etc and the cavity after the : 1, 2,3,4,L,R,X Etc
    They should appear in the table in order if possible (see TAB 2 connector table).
    thanks for looking.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Specify a variable table

    something like this?

    Connector.1 Connector.2 Connector.5 Connector.9 Connector.10 Connector.13 Connector.14 Connector.15 Connector.16 Connector.18
    CONN2 1 TERM 1 no no FALSE System Calculated
    CONN2 2 TERM 1 no no FALSE System Calculated
    CONN1 1 TERM 1 no no FALSE System Calculated
    CONN1 2 TERM 1 no no FALSE System Calculated
    CONN1 3 TERM 1 FALSE FALSE FALSE
    CONN1 4 TERM 1 no no FALSE
    SP2001 L TERM 1 no no no System Calculated
    SP2001 R TERM 1 no no no System Calculated
    SP2001 X TERM 1 no no no
    CONN2 01413019 1.0 W2S
    CONN1 01413072 1.0 W4S


    or

    Connector.1 Connector.2
    CONN2 1
    CONN2 2
    CONN1 1
    CONN1 2
    CONN1 3
    CONN1 4
    SP2001 L
    SP2001 R
    SP2001 X
    Last edited by sandy666; 11-14-2022 at 08:04 AM.

  3. #3
    Registered User
    Join Date
    02-29-2012
    Location
    UK, Newcastle
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Specify a variable table

    Hi Sandy, like the second example but in order of column 1 then column two. So same as yours but Conn1 at top of table.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Specify a variable table

    to be sure

    Connector Cavity
    CONN1 1
    CONN1 2
    CONN1 3
    CONN1 4
    CONN2 1
    CONN2 2
    SP2001 L
    SP2001 R
    SP2001 X

  5. #5
    Registered User
    Join Date
    02-29-2012
    Location
    UK, Newcastle
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Specify a variable table

    yes exactly like that :-)

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Specify a variable table

    with Power Query

    if you are happy hit Add Reputation and make me happy also
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-29-2012
    Location
    UK, Newcastle
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Specify a variable table

    Hi Sandy, I see the tables in the second Tab but unable to see how this was performed. I cannot see how the table has works in terms of only selecting data between !terminals and !

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Specify a variable table

    click once somewhere in the green table
    look at menu , you should see Query Tools, then in this Query hit the first left icon (Edit) and it will open PQ Editor, on the right side you'll see steps but if you want to see M (code), in the PQ Editor find Advanced Editor for whole M

    EDIT:
    or Data tab, show queries, on the right side you'll see pane with table then double click on it, and the rest is the same as above
    Last edited by sandy666; 11-14-2022 at 12:06 PM.

  9. #9
    Registered User
    Join Date
    02-29-2012
    Location
    UK, Newcastle
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Specify a variable table

    Ok, yes so I had seen that, however the data used to create the table could be any name and not just CONN and SP200. Hence the reason to be able to create a table by using the constants of !terminal and i.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Specify a variable table

    in this case:
    if you change value(s) in IF step then - yes
    if you thinking about different "names" in source then - no
    or you can define much more "names" in the IF step

    EDIT:
    you can play with text filter but there is no option: Between
    anyway this is a TEXT, not NUMBERS

    maybe try with Contains :::::
    will be easier later

    an "unique" element is necessary, common for searched elements with CONN, SP or any other

    you know what you have, I don't
    Last edited by sandy666; 11-14-2022 at 01:03 PM.

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Specify a variable table

    here is example without IF and CONN or SP
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-29-2012
    Location
    UK, Newcastle
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Specify a variable table

    Hi Sandy, many thanks for your continued efforts on this, its much appreciated. In this example did you have to manually create table2?
    I copied the connector table page to a new workbook and refreshed the query but an error no Table2 came up.
    If I copy over the page into the new wiresonlybrigedout 2 sheet does this not automatically create Table2 from the first tab?
    The idea would be that i would place a newly generated sheet (as per tab 1 in the example) in a specific folder reserved only for this function.
    The sheet would be renamed so always the same, for example DataImport and then the main spreadsheet would refer to this and pull in the data so it can then be further manipulated.
    So basically as provided here with the Tab 1 contents in a folder and the tab2 (connector table) in separate sheet.

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Specify a variable table

    did you do Ctrl+T on your source column?
    did you check the name of this table?
    in the M the name of the table is Table2 so you will need to change it to the actual name of the source table or change the name of the source table to Table2 then try again

    If you are writing about something, it is best to attach the relevant excel file
    Last edited by sandy666; 11-15-2022 at 05:38 AM.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Specify a variable table

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to do a Table with 3 variable
    By gjjh25 in forum Excel General
    Replies: 15
    Last Post: 06-08-2021, 11:40 AM
  2. Excel table filter by date variable through data picker date variable
    By SamanH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2018, 01:09 PM
  3. Select column from table using variable table name -- VBA
    By jscott15 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-03-2016, 06:28 AM
  4. Replies: 1
    Last Post: 10-27-2012, 02:54 AM
  5. Replies: 3
    Last Post: 07-05-2012, 01:49 AM
  6. Variable File Reference and Variable Table Array in VBA VLOOKUP
    By Gingeiko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2011, 11:08 PM
  7. How can i Append to Table using table Variable name
    By champs in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-18-2009, 04:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1