+ Reply to Thread
Results 1 to 6 of 6

Inputting data into a reusable form that then populates rows

  1. #1
    Registered User
    Join Date
    02-09-2023
    Location
    Yorkshire, England
    MS-Off Ver
    2016
    Posts
    5

    Inputting data into a reusable form that then populates rows

    I have previously used spreadsheets that do this in previous jobs, but never written one myself. I have used macros at a basic level in Excel courses in the past but that was a long time ago.

    I currently put the data from my Solar PV inverter into a spreadsheet that I made to calculate savings etc..., previously I was on an electricity tariff that was the same unit cost all of the time. From my inverter I would just export daily figures and copy them into my spreadsheet and my formulas would do their thing. Now, I'm on a new tariff that has 3 different unit costs at different times of the day.

    The inverter exports half hourly data for a full day in spreadsheet format, what I would like to do is copy this data into a reusable form on my spreadsheet that then outputs the totals for each unit cost into 3 rows for that day and then repeat for the next day into the 3 rows below. I hope that this makes sense.

    I have attached a short version of my spreadsheet and yesterdays spreadsheet from the inverter (Energy Flows).
    Last edited by TRBob; 02-09-2023 at 12:45 PM.

  2. #2
    Registered User
    Join Date
    02-09-2023
    Location
    Yorkshire, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Inputting data into a reusable form that then populates rows

    Is this post in the correct place, should it be in the General Excel forum instead?

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

    Re: Inputting data into a reusable form that then populates rows

    I don't think I understood everything you are trying to do here, but I will throw some scattered thoughts at you.

    1) You could use macros/VBA to do this, if you prefer VBA (or other macro language) as a programming language over spreadsheet functions. What I understood would be well within the abilities of spreadsheet formulas and utilities, so I see no real need to use VBA. Just a choice of preferred programming language. I generally prefer spreadsheet functions, so I would generally avoid VBA.

    2) I technically see nothing wrong with "copy a day's worth of data into a sheet, analyze it, store the analysis results, and then overwrite with the next day's data" approach. At 50 rows per day and an Excel spreadsheet has 1E6 rows, so a single spreadsheet can hold over 50 years worth of data. When I have undertaken similar projects, I have opted for a "store all of the data in a good database in a spreadsheet, then use my spreadsheet's pivot table and function tools to summarize and analyze the data." It's up to you, but there can be value in storing all of the data together in a single spreadsheet and analyze from there rather than storing and analyzing each day separately. Pay attention to good database design, and you can summarize the data by day, by month, by quarter, by year, or whatever. Also, with a good database design, you simply append new data to the database sheet, then refresh pivot tables (and formulas if you decide to set calculation to manual).

    3) I notice that your date/time values are stored as text strings. This forum is full of problems that come from storing dates/numbers as text. Your times are also stored using a 12 hour clock, but without an AM/PM indicator. If you can add an AM/PM indicator to your date/time stamps, you should be able to easily get Excel to recognize these date/time values and store them as real numbers (Text to columns would be one strategy). You will then find them easier to work with (like when you want to identify different rates at different times of the day).

    4) Assuming you can store your date/times as numbers, identifying "tiers" at different times of day could easily be done using a lookup function with the approximate match option. For example, I could build a lookup table like this:
    Please Login or Register  to view this content.
    . A lookup function like =VLOOKUP(MOD(date/time_stamp,1),lookup_table,2,TRUE) would return the tier ID, and =VLOOKUP(MOD(date/time_stamp,1),lookup_table,3,TRUE) would return the rate associated with that tier. If you are unfamiliar with how the approximate match lookup option works, this could be useful: https://www.ablebits.com/office-addi...ximate-vlookup

    I apologize that I have not provided specific solutions, but these are some things to consider. I would suggest that, before we start to talk specifics, we figure out how you want to resolve the dates stored as text issue.
    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
    02-09-2023
    Location
    Yorkshire, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Inputting data into a reusable form that then populates rows

    Quote Originally Posted by MrShorty View Post
    I don't think I understood everything you are trying to do here, but I will throw some scattered thoughts at you.

    1) You could use macros/VBA to do this, if you prefer VBA (or other macro language) as a programming language over spreadsheet functions. What I understood would be well within the abilities of spreadsheet formulas and utilities, so I see no real need to use VBA. Just a choice of preferred programming language. I generally prefer spreadsheet functions, so I would generally avoid VBA.

    2) I technically see nothing wrong with "copy a day's worth of data into a sheet, analyze it, store the analysis results, and then overwrite with the next day's data" approach. At 50 rows per day and an Excel spreadsheet has 1E6 rows, so a single spreadsheet can hold over 50 years worth of data. When I have undertaken similar projects, I have opted for a "store all of the data in a good database in a spreadsheet, then use my spreadsheet's pivot table and function tools to summarize and analyze the data." It's up to you, but there can be value in storing all of the data together in a single spreadsheet and analyze from there rather than storing and analyzing each day separately. Pay attention to good database design, and you can summarize the data by day, by month, by quarter, by year, or whatever. Also, with a good database design, you simply append new data to the database sheet, then refresh pivot tables (and formulas if you decide to set calculation to manual).

    3) I notice that your date/time values are stored as text strings. This forum is full of problems that come from storing dates/numbers as text. Your times are also stored using a 12 hour clock, but without an AM/PM indicator. If you can add an AM/PM indicator to your date/time stamps, you should be able to easily get Excel to recognize these date/time values and store them as real numbers (Text to columns would be one strategy). You will then find them easier to work with (like when you want to identify different rates at different times of the day).

    4) Assuming you can store your date/times as numbers, identifying "tiers" at different times of day could easily be done using a lookup function with the approximate match option. For example, I could build a lookup table like this:
    Please Login or Register  to view this content.
    . A lookup function like =VLOOKUP(MOD(date/time_stamp,1),lookup_table,2,TRUE) would return the tier ID, and =VLOOKUP(MOD(date/time_stamp,1),lookup_table,3,TRUE) would return the rate associated with that tier. If you are unfamiliar with how the approximate match lookup option works, this could be useful:
    I apologize that I have not provided specific solutions, but these are some things to consider. I would suggest that, before we start to talk specifics, we figure out how you want to resolve the dates stored as text issue.
    Thank you very much for your response! I have attached my full spreadsheet to show what I am currently doing, but what you suggest sounds like a better way of doing it. Although it's a bit over my skill level to create, when I used to work with sheets like this previously I could 'reverse engineer' them for other applications, but not create one from scratch.

    1) I do not know VBA of any programming languages unfortunately, learning Python has been my New Year's resolution the past few years, but it hasn't happened yet. So using Excel formulas and utilities sounds great.

    2) This is an excellent plan and is indeed the way that the spreadsheets that I used previously worked. Again , I have little idea how to set this up.

    3) I pull the data from the manufacturer's online portal so am unable to change the source formatting. I don't hold much hope of them altering it, they are flat out and even getting technical support is a challenge. Is there a way to instruct Excel to add in an AM/PM indicator given that the data will always have the same structure?

    4) Sounds great, thank you.

    No need to apologise, I've searched on Youtube for tutorials, but I'm not sure how to describe what I am trying to do in Excel terms so I haven't been able to find a specific how to for what I'm trying to accomplish.
    Attached Files Attached Files

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

    Re: Inputting data into a reusable form that then populates rows

    I notice that your latest file is very different from your previous file. In this file, you have data for each date. In the previous file, you had data for every half hour of each date.

    2) I don't do any real database work, so I'm not familiar with Excel's and Power Query's tools for building and managing a database in Excel. In the end, I suspect that there will be better ways than I can offer for building, adding data to, and managing this data base.

    3) Yes, we can instruct Excel to add AM/PM indicators to text strings. It's a simple concatenation. =A3&" AM" for the morning half of each day and =A3&" PM" for the second half of each day. I would then copy/paste special/as values over column A and text to columns (or other strategy to make sure it converts the date/time text to numbers) on column A. While you are at it, are the "Totals" rows also part of what the manufacturer's file includes? You will probably want to delete these rows from the source data as well. Again, while what I know is to use Excel's concatenate/copy/paste tools for this, I suspect that Power Query can also do these steps. When you get a Power Query routine set up that appends new data onto the database, you will probably program these kinds of steps into the Power Query routine that appends new data to the existing database.

    At this point, I think I am going to bow out of the discussion, because I just don't know enough about good database management to give good recommendations. Hopefully, I can get someone to step in and provide some good direction in database management.

  6. #6
    Registered User
    Join Date
    02-09-2023
    Location
    Yorkshire, England
    MS-Off Ver
    2016
    Posts
    5

    Re: Inputting data into a reusable form that then populates rows

    Ah yes apologies, the first file had saved on the second sheet by the looks of it which I used as a test.

    2) What do you suggest? I'm open to any method, preferably the easiest given my limited Excel skills.

+ 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] Command button opens Form on one sheet populates data on another
    By juanzo007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-05-2021, 09:42 AM
  2. form populates the wrong data sheet
    By zak.horrocks in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2014, 10:25 AM
  3. Create a User form that populates another sheet in excel with data from Access.
    By laras08 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2013, 04:03 PM
  4. Need help creating form that populates with data in row
    By benji912 in forum Excel General
    Replies: 12
    Last Post: 03-10-2013, 10:36 PM
  5. need help inputting data from a form
    By jimmy21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-03-2013, 12:26 PM
  6. Getting data from web by inputting form
    By captain smith in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-03-2012, 05:55 AM
  7. Creating a reusable form...
    By st19 in forum Excel General
    Replies: 2
    Last Post: 05-10-2012, 03:33 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