Posts by sar1

    Re: WEB PAGE from EXCEL data: is there a working example!


    I've SOLVED it, the most simple way ever! So who needs a Html Search Page querying SQL when you can have 1 of these now in minutes!


    Embed includes DV Combo Boxes & can be viewed in either Framed or Non Framed pages!


    My simple way: ONE DRIVE, UPLOAD, r/c EMBED, EDIT WORKBOOK, EXCEL ONLINE, FILE, SHARE, EMBED, HIDE GRIDLINES/HEADERS, EXC DOWNLOAD LINK, INC FILTERS, INC START CELL. COPY EMBED CODE.


    You might say why not just OneDrive Upload then Embed? Because my way there are more OPTIONs on Embed!


    see my online Spreadsheet Ex: Framed Pages & Non Framed Pages:
    select from 4 Combo Boxes highlighted in Yellow on r/h,
    watch the main body values changes as you make your selections!


    http://www.hkrebs63.karoo.net/xl/


    Also my spreadsheet is open source of just Formulas of countif / index / match, so no hidden VB Macro!

    Re: Web Page > Xl Hosted Web File > Xl Table Range?


    I've SOLVED it, the most simple way ever! So who needs a Html Search Page querying SQL when you can have 1 of these now in minutes!


    Embed includes DV Combo Boxes & can be viewed in either Framed or Non Framed pages!


    My simple way: ONE DRIVE, UPLOAD, r/c EMBED, EDIT WORKBOOK, EXCEL ONLINE, FILE, SHARE, EMBED, HIDE GRIDLINES/HEADERS, EXC DOWNLOAD LINK, INC FILTERS, INC START CELL. COPY EMBED CODE.


    You might say why not just OneDrive Upload then Embed? Because my way there are more OPTIONs on Embed!


    see my online Spreadsheet Ex: Framed Pages & Non Framed Pages:
    select from 4 Combo Boxes highlighted in Yellow on r/h,
    watch the main body values changes as you make your selections!


    http://www.hkrebs63.karoo.net/xl/


    Also my spreadsheet is open source of just Formulas of countif / index / match, so no hidden VB Macro!

    Re: Distances and DriveTimes


    Hi all here's an update, GoogleMapCode, easy to use:


    www.hkrebs63.karoo.net/files/DrivingDistance&Dir.xls
    To & From, Distance:Miles & HH:SS, Directions


    www.hkrebs63.karoo.net/files/DrivingDistanceVia6.xls
    To & From, Distance:Miles & HH:SS, for 6 different addresses with option for Via
    HINT: Don't save addresses after search, as won't update, unless column blanked.

    www.hkrebs63.karoo.net/files/DrivingDistancex100.xls

    To & From, Distance:Miles & HH:SS, for 100+ addresses
    HINT: Don't save addresses after search, as won't update.

    Re: Calculate Geographical Distances between multiple address using Google Maps or si


    Hi the following I find easy to use:


    www.hkrebs63.karoo.net/files/DrivingDistance&Dir.xls
    To & From, Distance:Miles & HH:SS, Directions


    www.hkrebs63.karoo.net/files/DrivingDistanceVia6.xls
    To & From, Distance:Miles & HH:SS, for 6 different addresses with option for Via
    HINT: Don't save addresses after search, as won't update, unless column blanked.

    www.hkrebs63.karoo.net/files/DrivingDistancex100.xls

    To & From, Distance:Miles & HH:SS, for 100+ addresses
    HINT: Don't save addresses after search, as won't update.

    Re: Indirect Vlookup > 3 Cell Nested Formula? > Data Validation > Defined Name Range


    BAD DATA VALIDATION:
    The examples above, the best being INDIRECT SUBSTITUTE, means creating countless DEFINED NAME RANGES referring to "endless FIELDS" of UNIQUE DATA to "pre-empt" feasible/prominent INDIVIDUAL POSSIBILITIES is a flawed approach, it's just not good practice, even in a small data set where all categories, and sub categories are known.
    It's time consuming, hard to follow in most data sets, constantly moving either Right > Left or Down > Up, and thinking where am I, what was I looking at, going round continuously in circles, it's open to human error, as you may leave out all eventualities, especially if you're unfamiliar with the data set as you might not think of it as "right or wrong", as it would "all look the same to me".


    GOOD DATA VALIDATION:
    I've devised a much better way, the DATA VALIDATION is automated to sort A to Z fields of existing data, and it's all in Formula, no VB.
    Hence no pre-population table of unique records is needed, and only 4 DEFINED NAME RANGES, 1 per Column, A-D Categories.
    How? Column A Data is sorted A to Z to exclude Duplicates for 1st Search Combo Box, and so on.


    http://www.hkrebs63.karoo.net/files/CarAZ#1-2-3-4.xls


    0.7mb's. This is real "A" Team stuff, you could even call it OPEN SOURCE, it's PURE GENIUS.

    Re: Indirect Vlookup > 3 Cell Nested Formula? > Data Validation > Defined Name Range


    The Best Data DATA VALIDATION from my perspective is INDIRECT SUBSTITUTE, it's copy & paste is so GENERIC.
    The only flaw is that an ongoing HIERARCHY to the right or below, gets harder to follow with many fields, and Defined Name Range row range refs with this formula can only go down & not to the side. Maybe they're all like that, but in all the DV methods I've seen this is the easiest method, why make it harder to edit!


    These are my best edits of DATA VALIDATION:
    http://www.hkrebs63.karoo.net/files/DataValIndSubOffV.xls
    59kb's


    & another is this MACRO versions, as it has self populating SUB FIELDS, but the VB in MODULE 1 needs editing to expand it's current COLUMN:ROW RANGE.
    right click SHEET1, VIEW CODE, PROJECT EXPLORER, MODULES, MODULE1:
    http://www.hkrebs63.karoo.net/files/DataValExpMacro+Edit.xls
    36 kb's


    Re: Indirect Vlookup > 3 Cell Nested Formula? > Data Validation > Defined Name Range


    Hi that formula you suggested does work with this example, but that code isn't GENERIC for any DATA SET for COPY & PASTE.


    Where as this is GENERIC for COPY & PASTE =INDIRECT(SUBSTITUTE(A2&B2&C2," ",""))


    With that IND SUB you carry on "PAIRING UP" indefinately with the previous 2, all done by COPY & PASTE as not absolute cell refs, the only real task is DEFINED NAME RANGES for SPECIFIC FIELDS, alot easier then quoting defined names all the time in FORMULAS, mr anderson, etc!

    Re: Web Page > Xl Hosted Web File > Xl Table Range?


    After finding the right way to reference XL CELLS & ROW RANGES in HTTP, it loads only on IE seperate TABS.
    But not in FRAMED PAGES, as it PROMPTS to DOWNLOAD XL FILE!


    Also if the file name includes already includes # then it won't load.


    Anyway here is the right HTTP REF for XL:


    IE HTTP XL Row Range: FRAMED HTM prompts DOWNLOAD hence EMBED or XL WEB PG.
    http://www.hkrebs63.karoo.net/files/db.xls#Sheet1!A1:E8
    <a href="http://www.hkrebs63.karoo.net/files/db.xls#Sheet1!A1:E8">XL Sheet1 A1:E8</a>

    IE XL Defined Name Range: FRAMED HTM prompts DOWNLOAD hence EMBED or XL WEB PG.
    http://www.hkrebs63.karoo.net/files/db.xls#Sheet1!MODELS
    <a href="http://www.hkrebs63.karoo.net/files/db.xls#Sheet1!MODELS">XL Sheet1 Defined Name Range: Model </a>


    So to summarise, as above, to display hosted XL in WEB PAGE direct HTML quoting in FRAMED PAGES prompts download XL FILE, so instead either:
    EMBED via ONEDRIVE EXCEL ONLINE, but unlike the XL PROGRAM there's no UNDO, so like they say in "THE MATRIX", there's no going back, if you make an ERROR, in "A" COLUMN!


    So inbuilt XL feature it is, save as WEB PAGE, OPTION include AUTOFILTER or not, and option to REPUBLISH HTM files on saving XL file.

    XL > SAVE WEB PAGE:
    Quicker then EMBED
    TABLE XL STATIC WEB PAGE: FILE, SAVE AS WEB PAGE, Publish, Selection Sheet, Publish. FTP.
    www.hkrebs63.karoo.net/files/dbnonaf.htm


    CHART XL STATIC WEB PAGE: CHART, r/c, FILE, SAVE AS WEB PAGE, Publish Chart, Save. FTP.
    Interactive IE8 works on non FRAMED PAGES: www.hkrebs63.karoo.net/files/dbaf.htm
    Option: Add interactivity to include AutoFilter. IE may not recognise in FRAMED PAGES.
    Option: AutoRepublish every time this workbook is saved.


    Hence XL > WEB PAGE, is probably best done via XL, SAVE AS WEB PAGE!


    And as for WEBSITE using your xl DATA via COMBO BOXES, I guess this involves migrating your xl DATA to a SQL DATABASE,
    love to see a simple example of this working!

    Re: Web Page &gt; Xl Hosted Web File &gt; Xl Table Range?


    To be honest I do know a way, that's easy, for a WEB PAGE to READ or WRITE to XL HOSTED FILE:
    ONEDRIVE > UPLOAD > EMBED.
    WEB PAGE > REF > EMBED CODE: READ XL RANGE of XL ONLINE FILE
    Ace, but display time is slow!


    But how to integrate this with a WEB PAGE of COMBO BOXES to Search Fields is another matter! Any ideas?
    Anyway below is 2 summaries: XL <> WEB PAGE: READ or WRITE


    WEB PAGE: READ > XL
    ONEDRIVE https://onedrive.live.com/
    UPLOAD, r/c, EMBED, copy code, CUSTOMISE,
    WORKBOOK/RANGE: A:D, Dimensions: edit Width/Height, copy EMBED CODE.


    then in your WEBSITE, COPY & PASTE EMBED CODE in FRAMED HTML PAGE. Hence if you EDIT/UPDATE your EXCEL ONLINE FILE, and refresh the EMBED CODE, then the WEB PAGE TABLE of EMBED CODE will change to show the current data of your EMBED XL FILE.


    in this example, XL spreadsheet table, Columns AtoD = A1:D10 Data is 9 Rows of CARS inc Header:
    https://onedrive.live.com/embed?cid=9EC5688DD90EEC6C&resid=9EC5688DD90EEC6C!2659&authkey=ANvcsX5nQNV5Z8M&em=2&wdAllowInteractivity=False&Item=%27CarDB%27!A%3AD&wdHideGridlines=True&wdDownloadButton=True

    And the other way round
    WEB PAGE:
    WRITE > XL:
    CREATE SURVEY: ONEDRIVE https://onedrive.live.com/ CREATE, EXCEL SURVEY, COMPLETE, SHARE SURVEY.
    or CREATE SURVEY in EXISTING XL ONLINE WORKBOOK: EXCEL ONLINE, OPEN WORKBOOK, HOME, SURVEY, NEW SURVEY
    COMPLETE SURVEY: Open Survey WEB LINK, Complete & Send & then view source Excel Online File, see new record has appeared from previous LAST ROW.
    ALT EDIT: Excel Online, Open Workbook, Edit in Excel Online, Copy & paste from XL Program WorkBook> XL Online.


    This is great, HOWEVER, surely there's an FASTER way for page loads and edits, other then using ONEDRIVE?


    Hopefully that's as easy as ONEDRIVE METHOD, but for a conventional XL HOSTED FILE without username/password, on a WEB SERVER via FTP.


    Hence the following is SOURCE CODE of the TABLE retrieved from an ONEDRIVE XL FILE, the SOURCE FILE/TAB is mentioned amongst the CODE but how to EDIT to a conventional hosted XL FILE & CELL RANGE?


    Such as
    TABLE such as:
    XL HOSTED FILE: http://www.hkrebs63.karoo.net/files/CarAZ#1-2-3-4.xls
    CELL TABLE RANGE: =Data!A1:X25

    Simple solution for WEB PAGEs to display a specific XL RANGE such as TABLE or CHART by referring to EXCEL HOSTED file that is updated often daily by FTP?


    TABLE such as:
    XL HOSTED FILE: http://www.hkrebs63.karoo.net/files/CarAZ#1-2-3-4.xls
    CELL TABLE RANGE: =Data!A1:X25

    CHART such as:
    XL HOSTED FILE: http://www.hkrebs63.karoo.net/files/COMBOBOXgraphs.xls
    CHART NAME: =CHART1
    Alternatively charts per TAB: COMBOBOXgraphs

    EXCEL FILE will be updated by FTP, and WEB PAGE to be viewed on any INTERNET BROWSER EXPLORER such as IE8 or FireFox / Windows or Android Phone. In this instance the XL spreadsheets aren't password protected, because it would be information of interest. Since records will change the end row reference will change, or list further down row range to accommodate the greater population of data.

    So when the EXCEL FILE is updated with new/edit of ROW RECORDS then the WEB PAGE TABLE will change on RERESHING to show the new data, of the same table range cells. Hence not a static web page from XL such as SAVE AS WEB PAGE, as this would just be a static snapshot of the data, at that certain DATE&TIME.

    Few WEB PAGES need specific programs or add ins to work, apart from JAVA or FLASH, so preferably the WEB PAGE would need few or no prerequisites such as IIS & Microsoft Office Web Component.


    Perhaps there is an existing Program, Add In that creates HTML to reference to XLS HOSTED HTTP file & cell range, the closest example I've seen so far, is XL save as WEB PAGE or spreadsheetconverter.com CONVERT XL > HTML to include DV Combo Box, but again this a static snapshow of data, ie the TABLE won't change when the SOURCE DATA changes of the XL HOSTED FILE updated by FTP.

    So far I've not seen any working examples demonstrating this with source files & instructions, I guess the real answer to this is that since FILE EXTENSION can be easily SEARCHED through "VIEW PAGE SOURCE" and EDIT/FIND ".XLS" and the SOURCE FILE & LOCATION would be easily identifiable for ALL OF THE DATA, but so what, in this instance it's just to display specific information, such as a table cell range examples at top of this email.

    If most web sites, don't refer to Excel to populate TABLES, CHARTS or DEFINED NAME RANGEs of PRE-SORTED available DATA, then where is SAVED DATA? That is where does a WEB PAGE DROP DOWN BOX look to refer to available FIELDS? Are XL AC databases not used for WEB PAGE drop down down data & relevant fields that match that criteria, as data is saved to a WEB FILE such as HTML, ASP, PHP, etc?

    But where else would the data be stored other then an Excel or Access DB? I guess the answer to this is that in most instances, it's all online, what would you a summary database for.

    Re: Multi-level Data Validation


    On looking further in Module1, this code simple enough to edit, but expanding DATA VALIDATION beyond current A-D doesn't work, however populating categories works if follow formula use:


    Re: Multi-level Data Validation


    Hi SHG, that is an impressive SELF POPULATING of CATEGORIES, this is a massive time saver!


    I like to make an EDOT if it. At a glance I don't see any DEFINED NAME RANGE or MACROs, how have you done this?

    forum.ozgrid.com/index.php?attachment/68258/


    Indirect Vlookup > Nested 3 Cell & so on ? D2 = A2+B2+C2 or a shorter version? D2 = B2+C2

    See attached SPREADSHEET: 4 Column spreadsheet selecting data validation fields in Columns: A B C D
    ColA: Make, ColB: Model, ColC: Type, ColD: Colour.

    In Column D, Cell D2, unsure how to construct the 3 CELL or 2 CELL Indirect Vlookup:
    3 CELL Nested Index Formula of either B2+C2=D2 returned defined range value?

    A2 =ColA
    B2 =INDIRECT(VLOOKUP(A2,ColALookup,2,0)&"")
    C2 =INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,&"Lookup"),2,0)&"List")
    D2 =INDIRECT(SUBSTITUTE(A2&B2&C2," ",""))


    Currently D2 is a INDIRECT SUBSTITUTE, need a correct edit of D2 in a INDIRECT VLOOKUP.


    I know what you're going to say that carry on that way....


    I could just carry on using this SUBSTITUTE,
    it's great, it works & is easy to refer to in Defined Name Range,
    hence easy task to remember the 2 columns previous wording,
    obviously you can keep going like that, indefinitely,
    what was the last 2, etc.

    HI looking for simple solution for WEB PAGE displaying FIELDS from a SPECIFIC EXCEL WORKBOOK/TAB/CELLS/NAMED RANGE.


    Such as INDIVIDUAL RECORDS of EXCEL DATA, saved to website, hence not on a computer! I have brilliant EXCEL SPREADSHEETS with MACRO BUTTONS, DROP DOWN BOXES & INDEX/MATCH VALIDATION, but EXCEL ONLINE doesn't work with these, simply, WORKSHEET TAB 1st ROW is HEADER, following ROWS 2ND & onwards is DATA: http://www.hkrebs63.karoo.net/…earch11&AddEditDelete.xls


    I realise at 1st glance this would be easy with a DATA ACCESS PAGE with FRONTPAGE/WEBEXPRESSION, but never seen these work.
    I guess even though there is a feature, I guess the true issue is that source code may stipulate the excel file location, ftp username & password, & wouldn't sort cryption easily, hence reluctance of true secure solutions, but examples of working solutions would be great!


    I know EXCEL ONLINE, can EMBED files, but I'm not looking to display the whole spreadsheet, just individual records, or sorts, or charts. However I know EXCEL ONLINE, has Survey Form which automatically UPDATES DATA ROWS with new fields submitted.


    I have browsed various solutions which offer spurious code, I'm looking for a PROGRAM SOLUTION of just SELECTION of EXCEL WORKBOOK/TAB/FIELDS/OPTIONS & FTP DOMAIN, and not coding in HTML, CSS, SQL, JAVASCRIPT, after all don't know about other people, I'm not a SOFTWARE DEVELOPER and I didn't design the EXCEL PROGRAM and INTERNET BROWSERS, so I use programs!


    This simple spreadsheet, of 4 COLUMNS is like all other spreadsheets, the 1st row is the HEADER, and following rows are data fields.
    http://www.hkrebs63.karoo.net/files/Cardb.xls