+ Reply to Thread
Results 1 to 26 of 26

manual J program

  1. #1
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    manual J program

    Good morning, I am working on a project that I thought was something I could do myself. Turns out i was way off. I am working on a spreadsheet that I want to use as a digital template to determine the heating and cooling needs of a house based on materials used and sizes. I have all of the laborious work done, however, the programming aspect has proven tricky. I do think what I want can be done, but sadly it is out of my scope. Hoping that some experts would be able to assist in this project. I plan to make it open source as the commercial programs are at minimum, hundreds of dollars and there are no complete and free sizing applications. I am linking to my github as I don't know how I would post a small sheet that can show the whole picture. The .ods file is currently the "master" file. I would prefer to keep it out of the microsoft domain but if I need to buy a new license to make getting help easier, I will.


    the project will involve VBA, I think. And I have started commenting cells and what the purpose of them needs to be. Hopefully this makes a bit of sense, and I will do whatever is needed to make this happen. Thank you

    Edit: so I am unable to post a link to the github where the file is.

  2. #2
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    A little off topic, but if this gets done in Excel, does that render it unusable on mobile unless I get a microsoft app for excel?

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: manual J program

    Using an .ods file extension, and other clues in your post, it seems that you might be wanting this to be usable across multiple spreadsheet platforms (Excel, LO Calc, Apache Calc, others). If this is true, then I think you will want to avoid VBA, because relying on VBA macros will pretty much lock you into Excel only. IMO, if you want the broadest compatibility across spreadsheet platforms, you need to avoid any macro language and try to program the spreadsheet using only spreadsheet formulas (being aware of which formulas are universally available and which are not). Once you start writing macros (in whatever language you choose to write them), you make cross-platform compatibility more difficult.

    If you decide that you do need to move some of the programming to other languages, you might consider languages like javascript over VBA. Most spreadsheets have support for javascript as a macro language (Google sheets might only support javascript as a macro language).

    All of that assumes that cross spreadsheet compatibility is important. If not, then we can work on an Excel specific application, but it's probably important to decide just how important cross platform compatibility is to your project, before we start proposing solutions that will only work in Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Fair point. accessibility is pretty up there as I would like anyone to be able to use this. But at the same time, being open source, anyone would be able to make changes down the road. Functionality would be top priority at this point so I will use whatever language I'm told.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: manual J program

    Since we cannot see the actual project, can you tell us what specific functionality you are having trouble programming into the sheet?

  6. #6
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    So the premise of the program is to get relevant data input. (wall sizes, building materials making up the wall, insulation, siding, window construction, etc.) before, this was done on a paper form and you would use table values from a reference book to get what is called an HTM (heat transfer multiplier) and run manual calculations based on HTM * area. Now it is mostly digital and done in proprietary programs. I have made all of the tables containing the various construction materials and their corresponding HTM values. It will be a form that you enter values in and my goal is to get the app to pull the correct value based on selected construction. The tables that I have, which are more or less a copy from the book, that I manually entered. The issue is the tables are not friendly to a novice spreadsheet user that would like to pull data from them. It may be I need to reformat the tables, however with the large table sizes of varying construction, I don't quite know how to go about doing it. Apologies because its difficult to elaborate without being able to show exactly what I'm referring to. Once I can post the github link, I hope this will make a bit of sense.

  7. #7
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    I think I am able to attach the file now, hopefully this lets anyone get an idea of what's going on.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: manual J program

    So the premise of the program is to get relevant data input. (wall sizes, building materials making up the wall, insulation, siding, window construction, etc.)
    That shouldn't be a problem. It seems like it should mostly be choosing cells for the relevant data and making it clear to the user that those are input cells and what data goes in each cell.

    you would use table values from a reference book to get what is called an HTM (heat transfer multiplier) and run manual calculations based on HTM * area
    Getting the HTM sounds like mostly a lookup type of operation. VLOOKUP(), HLOOKUP(), and INDEX()/MATCH() are nearly universal lookup functions across spreadsheets. The real programming question would be whether or not you can get your lookup tables into a format that works well with any of these lookup functions. Then, getting the HTM should be straightforward. I'm not certain what you mean by "area" (though I would guess surface area of the building), which is mostly geometry and programming the relevant geometry formulas into the spreadsheet.

    The issue is the tables are not friendly to a novice spreadsheet user that would like to pull data from them.
    If they are just lookup tables as I am guessing, then it seems that the main programming question is how to design the lookup tables/lookup formulas so that the user has no need to interact with the tables. Help us understand the nature of the tables, and I expect we should be able to help with designing good lookup tables and lookup formulas.

  9. #9
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Should note, this doesnt have the comments in place, construction details page is where most of the work will be done. Where there is currently a button, that is where the construction details from tables 2-4 would go. for example, button 1 gets clicked. and it brings up a list of doors, wood builds, stone builds, etc. user selects a choice, then it would have the user pick a more specific choice based on the prior choice. after the selection is found, the choice populates the initial cell with the construction number, for example: 10E if they selected a solid core wood door with a storm door. and the HTM value associated with that choice would then be multiplied by the size of the door in the area or length cell and that value would be placed in the BTUH column of said room. calculating both the heating and cooling values.

  10. #10
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Also, Sheet 19 in the program is more or less all of the tables merged into one. I thought this would make lookup easier but now, thinking about it. Reference to the associated U-value(HTM) will still be needed so not sure if this is feasible.

  11. #11
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Going over, another thing I would like to implement is in table 1, a dropdown box or selection box of some sort will allow the user to type the city and would pull the relavent data from table 1 and apply it where needed. For example, when finding the HTM for glass based construction, the daily range (found in table 1) would be a applied and would detewrmine the correct HTM after the selections based on the window type are found. Also of note, when picking glass, direction is needed, so based on the cell that is being filled in from the construction details page, it would already have the direction variable known.

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: manual J program

    Where there is currently a button, that is where the construction details from tables 2-4 would go. for example, button 1 gets clicked. and it brings up a list of doors, wood builds, stone builds, etc. user selects a choice, then it would have the user pick a more specific choice based on the prior choice. after the selection is found, the choice populates the initial cell with the construction number,
    This sounds like you are still visualizing this data entry process as something that consists of user forms and buttons and the macros that run them. It can be done, but, as I noted before, I doubt that any spreadsheet with that kind of macro controlled data input process is going to be compatible with other spreadsheets (around here, that means Excel). I can't help with that kind of input method, so others will have to help with that. Are you ready to lock yourself into Excel only for this project?

  13. #13
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    I would commit strictly to excel. I have copies of the barebones project in other formats so anyone that wanted it done in another language would be able to see the raw code to get the desired results. Biggest thing now is functionality.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: manual J program

    It looks like a fairly substantial project. Seems best to divide and conquer. What part do you need help with first?

  15. #15
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Well, i guess a good place to start would be on the construction details page. For cell F5, Im thinking it would be a drop down box using data validation that would bring up row 1 on sheet19. after the base material is chosen(the "No. xx") another dropdown or event that allows to proceed to the more detailed selection(the cells that have a letter). And once that is selected it will pull the HTM from the relevant table and place the pulled value in the corresponding HTM cell(on the construction details page, H5 and I5 in this case).
    I have done this before but i was never able to get the dependency based second drop down to work correctly, let alone having it populate other cells based on selection.

  16. #16
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    I'm not even sure if a dropdown list would be best, I have also tried to make it a button that brings up a window with dropdowns but that didn't pan out very well.

  17. #17
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Made corrections on the form in construction details page

    https://github.com/mooshman812/mooshman812

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: manual J program

    I feel that you are more likely to get responses if you attach the file showing "corrections" directly to the site, as you did in post #7, rather than github as some contributors will not be able to view due to firewalls.
    I looked at the file and am confused.
    Cell F5 displays DIRECTIONS ROOM FACE, is this where you would like a drop-down listing Glass, Doors... that are displayed in row 1 on Sheet19?
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  19. #19
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Updated to attach file to to post.

    And I did misspeak, looking at the new upload, the list would be in cell F8 on the construction details page.
    Attached Files Attached Files
    Last edited by Mooshman812; 03-26-2022 at 02:09 PM.

  20. #20
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Also, would it be a bad idea to use sheet19 as the reference for the lists? I ask because it doesn't have any of the values that I would need to pull from the table(s) pages.

  21. #21
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Attached is the physical form of the worksheet for reference. Looking at the construction details page of the scan, in the const no. cells, you would find the material number that represents the material that the dwelling is made with. In the unshaded areas under HTM, you would transfer that value found on the corresponding table, using the outdoor temperature to find the exact HTM value. Ideally this would autofill after the selection process in const no.

    Lot of info to take in, but hopefully that makes a bit of sense. I'm just not sure how to break this down to do it bit by bit as there's so much that I think needs to happen.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Hello again, I made cell F8 on the construction details page a drop down list. 2 questions. is it possible to make it a self contained dependant dropdown? And is there any way to make the spaces in between the choices in the dropdown go away?
    Attached Files Attached Files

  23. #23
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: manual J program

    To get rid of the spaces in the drop down populate consecutive cells, such as AA2:AA4 with =G1,=M1 and =O1
    Change the source of the named range Exterior to: =Sheet19!$AA$2:$AA$4
    Depending on whether or not more columns may be added to Sheet19, you may want to put the list on a separate sheet.
    Let us know if you have any questions.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    is it possible to do a drop down list that bring up another drop down in the same cell but populate the second list based on the selection from the first list?
    Regarding your input, thank you. if, on sheet19, I listed the general materials in their own cells on their own column and all of the subselections for each specific material to the right, would that make getting list data easier?

  25. #25
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: manual J program

    As to putting a second drop down in the same cell, I believe that the answer is no, at least I have never done that nor seen it done.
    As to putting materials lists in their own column, I feel that this would make things easier to work with now and easier to make additions in the future.
    For example, with Exterior materials listed in column AA (Sheet19) the source for the drop down in cell F8 (CONSTRUCTION DETAILS sheet) could be changed to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This would reduce the need for the step of producing a named range (Exterior) and allow for new materials to be added to the list in cells AA5:AA9
    Let us know if you have any questions.
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    03-23-2022
    Location
    Omaha, NE
    MS-Off Ver
    2010
    Posts
    18

    Re: manual J program

    Thanks for the input. I modified the tables in a more logical manner. I think doing it like this achieves what you were suggesting and makes it seem easier to be able to work with. Also made an add room button that populates another room.
    Attached Files Attached Files

+ 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. [SOLVED] Calc set to manual with VBA!
    By Keibri in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-23-2016, 09:35 AM
  2. Trying to count rows from manual page break to manual page break
    By LuckoftheLefty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2014, 06:53 AM
  3. VBA-How to program sizing and placing Excel window at the start of the program
    By dr.prakash in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2013, 11:45 PM
  4. Need someone to program / help program a VBA / Excel Macro
    By JoeMcDome in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-04-2013, 04:03 PM
  5. Find program line and Run program from defined line
    By Saurabh guddu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2012, 11:01 AM
  6. NPV vs Manual NPV
    By karaf in forum Excel General
    Replies: 3
    Last Post: 04-16-2010, 08:35 AM
  7. photoshop manual?
    By seababy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2006, 01:47 PM

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