Posts by MDBJ

    So I have a program that exports a calendar in HTM, for multiple rate types.
    I can import individual pages data as if it was another workbook (scraping from locally generated htm files)
    I want to do so elegantly. I have a top row of header information


    the link for the scrape looks like this in cell a2, then repeats across and down and works
    ='D:\IQ\[IQ_rates_ufo.htm]IQ_rates_ufo'!A1


    ufo is specific, there are others, I would copy worksheets and have it pull the code
    I want to, in my scraping workbook, have UFO be on my top row (cell a1)
    and have what file is scraped be contained in a formula, I thought it would be
    ='D:\IQ\[IQ_rates_"&$a$1&".htm]IQ_rates_"&$a$1"'!A1
    but no good.


    I've tried every text connector I can think of such as
    ='D:\IQ\[IQ_rates_"+$a$1+".htm]IQ_rates_"+$a$1"+'!A1




    How?

    I'm building a table that shows a range of numbers for each date on a calendar
    to keep it tidy, I'm putting a high-low range under each date on the final calendar- in a single cell. (keeping it down to a single cell on the final calendar is important)


    the formula for a sample cell right under the date cell (date is in m7) reads

    Code
    =HLOOKUP(M7,'rate extract'!$B$2:$GF$20,9,FALSE)&"-"&HLOOKUP(M7,'rate extract'!$B$2:$GF$20,10,FALSE)


    this puts a result into my calendar that looks like 204-1206 directly below the date (the first hlookup pulls the 204, the second the 1206)
    I would prefer it to be displayed as 204-1,206


    if I use just one hlookup in the cell the displayed result will bring forward the same exact display format used on the original result from the array....
    if the source is formatted with the comma, it brings it forward so 1,206 comes in as 1,206


    since I'm adding the ampersand"-" and the second hlookup, it will only display the result as a straight number my 1,206 on the source is converted to 1206


    advice? thanks...
    I've tried custom formatting the cell as #,### #,### and variants to no avail

    I have a hlookup that works for all but four rows. I don't know why it fails there.
    I've attempted to reduce the size to attach here and failed to get it small enough.
    so I have put it on webspace of my own. (267 kb for the workbook)


    http://www.ocean7hotel.com/bingo.htm
    inside the date/roomtype array of 'roomavg..' sheet every cell formula is the same
    except for the very first col reference changes for each col based on position
    (so under 201 B$4.. under 205 it is c$4 etc....)
    =HLOOKUP(B$4,'booked rev by night clean'!$CF$4:$CO$47,(ROW()-3))


    (I apologize that the date on "booked rev" is so far to the right- I deleted all the non relevant data
    from that page and just deleted & pasted as values my cf4:co47 data-but left it in the same space)


    and it works! everywhere except for my FFL and FFS and I don't know why.
    it works before those cols, and after those....
    the actual workbook is also available
    http://www.ocean7hotel.com/bingo.xls


    what is different about this that it errors out? I've run the evaluation steps- and I fail to see the problem/fix....
    I have run manual step by step hits on the cells equal to one another and that returns true...

    I have to be very careful about balancing reservations at my motel.
    I can generate from my hotel software a list that includes the check in and checkout date for a room- and the number of nights for the stay, and the total dollar amount of that stay.


    if anyone is willing to look what I have so far,
    I am attaching the smallest sample set of data I can generate and have it useful
    and I have succeded in using vlookup succesfully to transfer the net room amount onto the first night of each stay on the grid.


    worksheet "export tape chart" is the data generated by my hotel software-
    (except for Col a-- I use this for my two way vlookup match)
    I can reorder the output as needed for vlookups & offsets


    worksheet "first night rate total NA" is where I have pulled the total room rate into the first night of the stay in a grid


    worksheet "first night rate total clean" is just a simple cleanup of the first pulling for iserror


    this works somewhat for getting what I need (balancing bookings) close


    I need better...
    the refinement I am looking for and failing to get- is how I can make my clean sheet grid
    contain the single night rate (and here an average is good enough)
    for example, right now f12 contains 250, and f13 is empty
    f12 represents the 250$ total of a two night stay-
    I want my clean sheet to show instead f12 to contain 125, and f13 to contain 125


    I've spent the day trying nested vlookups & match statements-- and wrapping my head around arrays to no avail.


    I do know that no room will be redundant for a given date, except that checkout on one can equal checkin on another-- so it's better if comparisons to checkout date are always LESS THAN, (<) not EQUAL TO OR LESS THAN (=<) and for checkin equal to or greater than =>



    Any feedback or assistance appreciated.