Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Need Excel button (macro) to consolidate information

    Hello,

    I have had a previous post on here with great success and would like to try to get assistance again.

    This time I have one page (2nd tab) of data which I would like to be hidden (containing all of the applicable data) yet on the first page (tab) have 4 possible macro buttons which will gather (and if necessary, average the data) and put this information in its appropriate cell (red font in spreadsheet) based on the "ID" tag provided to it (also in red font).

    For now, because I don't have data in this second tab, just the basic code to start would be appreciated and I can play and tweek it later.

    A copy of the spreadsheet is attached.

    Thank you all in advance!
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    Could you be more specific and provide some sample data to do any testing?

    For averaging you could just use a formula (eg for avg TVD based on value in C1:
    =AVERAGEIF('Data (Hidden)'!$A$7:$A$1000,$C$1,'Data (Hidden)'!$C$7:$C$1000)

    But it is not clear (at least to me) from the other items what you are looking for exactly...
    Steve

  4. #3
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Hi Steve,
    All data in the 2nd tab will be of numerical value with exception to column A which will have a specific ID tag i.e. 100/01-02-003-04W5/00 as per the first tab (in red). The data tab will be filled in with up to 5000 rows of data. Each of the "red" items I would like pulled from the data tab and put into the first tabbed page (like a vlookup) which I can tweak to satisfy my needs but I don't know how to set up code on the buttons to get it to initially pull any information.
    I hope this clarifies some for you.
    Greg

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    It is not clear where you are supposed to pull from for each item. Many may be created with a formula which would not require VBA.

    But regardless, you need to tell us what and where the information is supposed to come from for each item...

    Steve

  6. #5
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Let me reformat the spreadsheet which will help with this.
    I will repost the thread when it's ready.
    Thanks.

  7. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    PM,

    Hello again.

    I have a basic idea of what you are looking to do but it is not clear which column of data or formulas relating to that data are mapped with which value cells on the Frac details sheet. Some questions:
    1. Will there be more than one instance of the same ID record on the Data sheet?
    2. If so, how will you want multiple pieces of the same data for the same ID handled, averaged?
    3. When you enter an ID into cell C1 on the Frac detail sheet, do you want to populate each section with the button click or all simultaneously?
    4. I would recommend a dropdown box on Frac details sheet that lists unique ID values on the data sheet. Selecting the ID would be much easier and less error prone than typing in the ID.
    5. If you could post with sample data in sheet 2, that would be very helpful.

    Maud
    Last edited by Maudibe; 2014-04-28 at 19:35.

  8. #7
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Hi,
    I have attached a newer spreadsheet which alligns the buttons with the data tab.

    Maud - this will answer your questions, and after clarifying what our team needed to accomplish, this is what we want the sheet to do:

    When a "UWI" and Formation is entered into the first sheet then we would like it to pull the (averaged) information from the second tab ... this is where the problem lays: The "UWI" is a unique number value, yet the averaged "data" to be pulled will only be based upon the middle parts of this number +/- 2, therefore the "averaged" output on page one will be more so based on the numbered values as follows (plus or minus 2 for any of the numbers) xxx/xx-02-003-04Wx/xx (the "W" never changes so it's not relevant) which is pulled from the data on the second tab.

    1. Being said, question #1 will be yes.
    2. Same data averaged.
    3. I think for ease, one button should do yet I would ask that it remains categorically as shown.
    4. Unfortunately I don't want some people to randomly be able to pull this data as it would be highly confidential. If need be, the UWI can be broken down into its applicable parts if this would make it easier to pull the data from the second tab (let me know if you want me to do this).
    5. Sample data for one UWI (Well ID) has been put into the new attachment.

    Thanks All!
    Attached Files Attached Files

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    It seems to me that AVERAGEIFS will work for averaging with the 2 inputs. I still don't see the need for code and what it would do that a formula would not and a formula would be live.

    Steve

  10. #9
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks Steve,
    I have never worked with averageifs and am pretty novice with formulas in Excel but still I am trying to figure it out ...

  11. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    Greg,

    Here is the workbook written with code that will move all the data from the hidden sheet for a particular well and average it where applicable. Each button as you requested controls a section of values. There is an adjacent button to its right to clear the values. There are 3 fields that need clarification:

    1. Average Formation which is a "string" by your mapping to column D
    2. Fluid Composition which is also a "string" mapped to column L
    3. Proppant Type, "string" type mapped to column O

    If these fields change with the multiple instances of the same well, how do you want them handled? All other fields are set up to average.

    Since you said that the data was confidential, I have placed code in the Workbook_Open event subroutine that will lock the sheet on opening (without a password) but allow the code to modify the sheet. Cell C1 is not locked so the well ID can be placed. If this is not your intent then just delete that code in the ThisWorkbook module. Remember, that if you lock the sheet manually, you will have to make sure that the cells being valued by the code are also unlocked. If you would like to add a password to the code then change:

    Worksheets("Frac Details)").Protect UserInterfaceOnly:=True

    Worksheets("Frac Details)").Protect Password:="Hello", UserInterfaceOnly:=True

    I would suggest making the "Data (Hidden)" sheet very hidden (xlSheetVryHidden) but do not change the names of the sheets.

    HTH,
    Maud

    Wells1.png
    Attached Files Attached Files
    Last edited by Maudibe; 2014-04-29 at 21:51.

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    ProjectMgr (2014-04-30)

  13. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    Something like:
    =AVERAGEIFS('Data (Hidden)'!$C$7:$C$1000,'Data (Hidden)'!$A$7:$A$1000,$C$1,'Data (Hidden)'!$D$7:$D$1000,$G$1)

    will average the values in the range C7:C1000 when the values in A7:A1000 are equal to C1 and the values in D71000 are equal to G1. You can add additional criteria if desired.

    Steve

  14. #12
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Thanks again Maud! Huge help as always!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •