Hi Richard, thanks for the response, the sample data should be sufficient to show what i'm after. There are around 130 meters in total so once up and running there will be a fair bit more data. Apologies if the request seems a little naive but excel is not my background so i really do appreciate the input.
Hi,
Does the column L month not relate to one of the Date columns B or C?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard,
No column L is a user selection in the MSform columns B & C are automatically generated by the MS Form to report when the MSform was started and completed.
How open are you to recording your data in a slightly different way thus opening analysis by Pivot Table?
i.e. Largely as now except that the month column would contain a proper Date number, e.g. always say the first of the month, and column M would contain the Reading.
Better still since you mention wanting to analyse data by energy type I'd suggest a column for the meter type, i.e. Gas, ELectric, Water and alongside it a column for the meter reference.
So altogether
Columns A:F as now
G: Meter Type, e.f.g Gas , Water Electricity
H: Meter Ref
I: Month e.g. 1/1/2021
J: Reading e.g. 2626251
Some of those columns could be drip down data validation to assist date entry
The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.
Hi Apologies if i'm a bit slow but i'm not clear on how this can be acheived. The data on the base data tab is fixed. As it is populated with the responses from the ms form that the engineers complete on their phone / tablet. There is no scope to change the form so i have to somehow acheive my results from that data. Really appreciate your time helping with this, I know excel is really powerful so am sure there is a way.
My suggestion was not seeking any new data, just recording what you already have but in a slightly different way. The only 'difference' is that I'm suggesting an entry like the word 'January' is entered on the base data sheet as a January date number proper, say 1/1/2021
How is the data populated on the Base Data sheet? I can't see any connections or links which implies it's being manually added. If that's the case and since no new data is different it could presumably be added as I described.
Perhaps it would help if you uploaded the ms form that the engineers use with an indication of how it moves from the phone to the Base Data.
I first created the MS Form from my company onedrive account so it will automatically update each time a response is submitted. When i created the 1st one from the normal forms application I needed to manually download any updates but learnt that by creating it from my company's sharepoint it would be updated dynamically. Unfortunately it won't let me share the target workbook outside my organization The copy i uploaded for you was a dummy i put together.
tinyurl.com/pm6t8ph3 -- link to form is the best i can do sadly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks