Go Premium for a chance to win a PS4. Enter to Win

x

Microsoft Excel

132K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I just placed a combobox that lists the 12 months of the year on an Excel worksheet.  The user is to select the applicable month.  Is there a way to reference the selection so that it appears in the middle header section when printing the worksheet?   Hopefully it can be referenced in some way without having to set up a macro.

Thanks
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Sheet1 contains 9 columns - the key ID is the Postcode. Each postcode isbe repeated several times with different  contents in the cells on each row for that postcode number.

Sheet2 contains 2 columns - Postcode, Local Zone. Each postcode is repeated several times with different numbers in the Local Zone column.

The requirement is to copy the row by postcode from Sheet1, into the row with the same postcode in Sheet2, and to repeat the copy as many times as there are different Local Zone numbers, and the create a new set of the same postcode/local zones for the next postcode from Sheet1.  Each row in Sheet1 is created in Sheet2 for as many times as the LocalZone number occurs.

The example file shows the first postcode contents from Sheet1, copied manually into Sheet2.
Example-masthead-to-local-zone-2017.xlsx
0
Hi

How do I open a new workbook in the same window. When I open a workbook it does it in a new window.
This is for my Excel VB.net Add-in which uses task panes.

Thanks
0
Hello,

Quite some time ago, I obtained the following two-part VBA code for Excel which I now want to include in a new workbook:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
	
End Sub

Open in new window

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
	
    If Not Intersect(Target, Range("AA:AE")) Is Nothing Then
        rowsel = Target.Row
        Range("AC1").Value = Range("P" & rowsel).Value
    End If
End Sub

Open in new window


The function of the code is as follows:

When a cell in a particular column is selected, the cell at the top of that column displays the content from a different column which resides in the same row as the selected cell.

For example, in the following screenshot, cell D7 is selected so cell D1 displays the content from cell B7 (ie from the same row as the active cell):

2017-11-15e.png
And then when a different cell in column D is selected — whether by use of the Up/Down arrows or by a mouse-click — the heading in cell D1 automatically changes to display the content in column B of the new active row. In other words, the spreadsheet does not have to recalculate for the heading to change. It simply occurs by changing which cell is active (eg D11 in this screenshot):

2017-11-15f.png
Here is a screenshot of how the code appears in the older workbook:

2017-11-15g_vba-for-Scripture-headin.png
I know I need to save the workbook as a macro-enabled (.xlsm) file but which Insert option should be used and where should each part of the code be pasted?

2017-11-16e.png
Also, which parts of the code needs to be modified if I want to change the selected cell, heading, and/or content column(s)?

Thanks
0
hi,

I'd like to take a list of intervals defined by pairs of numbers, eg
3,5
2,4
9,12
10,11
15,17

and convert this into a consolidated list counting any overlapping intervals as one, so the above example would give:
2,5
9,12
15,17

See attached drawing that illustrates this.

I want to do this with array/matrix operations, if possible, so I can implement in google sheets without coding.

Thanks
diagram.jpg
0
Please see attach spreadsheet for example,

Can you please help me automate the counting of records using a macro.

There are three worksheets
1. Priorities - Lists all the Priorities in column A that will be used in responses on the Responses worksheet
2. Responses - Each row is a set of responses by a user and there may be up to 50 user responses.  I will populate these manually.  The worksheet has some examples.  The responses going across the row can go up to 150 after the name in column b.
3. Results - Counts the responses for every row by user that is in the responses worksheet. The responses are are totaled in the last column. (or on a separate worksheet if easier)

When you look at the attached file you can see two examples.
CountResults.xlsx
0
Hi,


I would like to create a button that will toggle the values of either true or false of all checkboxes within the entire row.

I have multiple rows of checkboxes which i use to calculate values

thanks
0
I have a chart which is an imported graphic with a log scale. I want users to be able to slide the bars (named 1 and 2) to ascertain the value from the chart. I want to restrict the slide action to vertical and horizontal if possible. I have been trying to think laterally i.e. underlaying another graph to allow crosshairs to appear, but this hasn't yielded any real bouts of inspiration. Another thought was activating the shift key on the worksheet getting focus so you can only go vert and hor'. See attached file.
graphic.xlsx
0
Hi all,

From last few hrs i have been trying to make a Pivot table, Column "C" "Task" based Person based Count.

Wanted the work done by
How many Voice, Editing, Camera, Anchor work did "Micheal" do

If anyone can help me would be great
attached sample file
Thanks in advance
Sample.xlsx
0
I will try to explain this the best I can from the information a user gave me.

So apparently he has a macro on Excel 2016 that runs which fixes a raw .xlsx file by formatting it to the end users specifications. So to break it down a program outputs a .xlsx in raw format then he opens the raw .xlsx file on his computer and runs the macro to fix the formatting.

The question we have is can we automate this process in task scheduler or something similar to run the macro on the raw .xlsx file on someone's computer or a server? Most likely the user will not have Excel open because this will occur around 5 am when no one is working.

(Program outputs .xlsx  to network drive > user's computer or server runs the automated macro to the format data > end user is able to use the output.)

Thanks for helping!

- Excel 2016
- Windows 10 Pro x64 or Windows Server 2012 R2
0
Free Tool: SSL Checker
LVL 11
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I had this question after viewing VBA modification help needed from earlier solution by Rgonzo1971.


Rgonzo1971 was so kind helping with this great piece of code.

Sub Create_RangeNames1()
     'Creates dynamic named ranges based on header row information only Selection
    Dim wbk As Workbook
    Dim sht As Worksheet
    Dim rng, rng2 As Range
    Dim cl As Range 'Object
    Dim c As Long
    Dim strAddr As Variant
    Dim strShName, strHdrName, strCol As String
    
    Set wbk = ActiveWorkbook
    Set sht = ActiveSheet
    Set rng = Selection
    For c = rng.Column To rng.Column + rng.Columns.Count - 1
        If Cells(1, c).Value <> "" Then
            strShName = Replace(sht.Name, " ", "_", 1)
            strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
            strCol = Cells(2, c).EntireColumn.Address
            Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
             wbk.Names.Add Name:=strShName & strHdrName, _
                RefersTo:="=OFFSET('" & sht.Name & "'!" & Cells(2, c).Address & ",0,0,SUMPRODUCT(MAX(('" & sht.Name & "'!" & strCol & "<>"""")*ROW('" & sht.Name & "'!" & strCol & ")))-1,1)"
       End If
    Next
End Sub

Open in new window


lets say from the attached file .  i selected column E and run the above code.  it created named range with this formua.
=OFFSET(Sheet1!$E$2,0,0,SUMPRODUCT(MAX((Sheet1!$E:$E<>"")*ROW(Sheet1!$E:$E)))-1,1)

i need help with modifying this, so that it creates the column dynamic formula  like this.  

so the "ORDER ID" inside Match function should take from column header name

=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1))-1,1)
EE.xlsb
0
I had this question after viewing VBA modification help needed to revise the formula.

Rgonzo1971 was so kind to help me with this code that generates dynamic range.

Sub Create_RangeNames1()
     'Creates dynamic named ranges based on header row information only Selection
    Dim wbk As Workbook
    Dim sht As Worksheet
    Dim rng, rng2 As Range
    Dim cl As Range 'Object
    Dim c As Long
    Dim strAddr As Variant
    Dim strShName, strHdrName, strCol As String
    
    Set wbk = ActiveWorkbook
    Set sht = ActiveSheet
    Set rng = Selection
    For c = 1 To rng.Columns.Count
        If Cells(1, c).Value <> "" Then
            strShName = Replace(sht.Name, " ", "_", 1)
            strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
            strCol = Cells(2, c).EntireColumn.Address
            Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
             wbk.Names.Add Name:=strShName & strHdrName, _
                RefersTo:="=OFFSET('" & sht.Name & "'!" & Cells(2, c).Address & ",0,0,SUMPRODUCT(MAX(('" & sht.Name & "'!" & strCol & "<>"""")*ROW('" & sht.Name & "'!" & strCol & ")))-1,1)"
       End If
    Next
End Sub

Open in new window


this generates the  only for column A  . =OFFSET(Sheet1!$A$2,0,0,SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A)))-1,1)

which it should create name range only for columns that i select . meaning should run on "Selection of columns"

but this has problem. it only creates column A regardless even if i select column C. the named range is always column A.
0
I had this question after viewing How to get the INDIRECT function and an R1C1 notation for entire column?.


please see attached file.   because it uses the column A for counting non blank cells.

now if my Order ID title gets moved to column E.  the formula below works. but it has a problem.  when i have less rows with data in column A. then the named range leaves out cells.  i need help with making the column used in COUNTA(Sheet1!$A:$A) also dynamic.

EE.png=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(Sheet1!$A:$A)-1,1)
EE.xlsx
0
Hello,

Other than inserting an image, is there a way to fill a range of cells with a textured color in Excel (2010)?

The following screenshot (from a website) shows three shades of textured background:

2017-11-14a_background-texture.png(Edit: this image is about twice as large as the screenshot and thus appears more coarse than the original.)

Is there a way to get a similar texturing effect for a range of cells in Excel?

The regular fill options include a couple of effects:

2017-11-14b.png
2017-11-14c.png
...but is there another setting or method to create a texture similar to that shown above?

Thanks
0
Hi,

I have a spreadsheet (attached) with a list of Jobs with corresponding employee number as well as the date that the employee performed a particular job.

I need to know how many weeks that each employee performed work on five or more days in a given week. The number of jobs performed is not important for this analysis. Rather, we just need a list of ID numbers with a corresponding number of weeks where there were at least one job in five or more days that week.

Secondarily, there is a cut-off date (which will vary per employee) for which I need to ignore any jobs and/or weeks before that date.

I know how to use a pivot table to get a list of the unique ID numbers but the conditional count here is beyond my Excel abilities. Access may be able to do this easier but I am utterly without skill in that program.

Any ideas?

Thanks in advance.
2017-11-14-Sample-3--00324866xBDC2C.XLSX
0
I had this question after viewing How to change this dynamic row named range to also become dynamic column?.

I found Neils answers very useful in the referenced link.    I do not know how to use the INDIRECT function and an R1C1 notation  for the entire column Reference for example this part COUNTA(Sheet1!$A:$A)

can u help plz?

=INDIRECT("Sheet1!R2C" & MATCH("Order ID",Sheet1!$1:$1,FALSE) & ":R" & COUNTA(Sheet1!$A:$A) &"C" & MATCH("Order ID",Sheet1!$1:$1,FALSE),FALSE)
0
I had this question after viewing Formula modification help needed, Rgonzo1971 & Ryan Chong earlier solution.

Rgonzo1971 was so kind to help me in an earlier post, (referenced above link)
the formula has an issue when i added new quarter for another year 2014, it somehow calculates incorrectly, Please see attached file  the total is 90773 while your formula now results 91855

any help is appreciated.

thanks.
EE-4.xlsx
0
In excel, I have used vba's beep function to use that in cells. Now, the cell value changes incrementally (the cell value may incrementally increase or decrease).  The incremental value is +/- 100 only.  For eg., if the cell value is 1200 from the data feed, it positively increments either to 1300, 1400, 1500, etc., or negatively increments to 1100, 1000, etc.  I am only concerned about "A" particular cell in an excel sheet because the data feed happens only in ONE particular cell.

For every increment, I have used the VBA beep function to alert me.

Whenever the beep happens, I have to manually change the formula (=if(D5<>1500,beepnow,"") to the increment that just had happened. Else, it keeps on beeping.

What I require is, the formula should automatically beep once - that's all!  Then, the beep should happen only when there is incremental "change" in the cell D5. Otherwise, the beep shouldn't happen.  The data feed happens during most part of the day, but I wouldn't know when it would happen; hence this beep alert is a kind of helping me instead of sitting always in front of the system waiting.....

Can it be done with a formula or can it be done via VBA?  Kindly, help me on this.  Thank you! Prabhu
Sample.xlsm
0
Hi.  All of a sudden, Excel is allowing me to close it without saving changes I've made.  So, if I make a change, and click the red X to close Excel, Excel does not prompt me to save my changes -- it just closes.   See video at HERE.

I uninstalled/reinstalled all of Office 2106 (this is an Office365 subscription), but that didn't fix it.

Any suggestions?
Thanks!
0
[Webinar] Cloud Security
LVL 11
[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

This worksheet captures the head to head results on the Legend Votes tab.  When it gets to the last vote it displays a message that the voting is complete.  

I would like to add the following please.

1 . At the end of the voting have a pop up window to capture the users name and email address.
2 . Email the results to a specific email address defined in the macro and CC the user the results from the
      captured email.
      Subjuect = Fullname from step 1 with Voting Results.  Example "John Doe Voting Results"
3. Clear the results on the spreadsheet to start a new vote.
ExcelVoting.xlsx
0
I can open the all my workbooks. They are not corrupted.
I can open them from the recently used list or go to open file.
When click on a Excel shortcut Excel will open but the workbook will not.

UNRegserver/REGserver appears to do nothing when Excel.exe is started from the "run" box.
0
I am ranking (column AH)  the points for a group of members based on if they meet all percentages greater than or equal 100.  My problem is when the point total (column AG) for a non-qualified member is greater than the qualified member.  I cannot figure out how to exclude the non-qualified member from the formula.  I've attached my spreadsheet and my formula is:  

=IF(NOT($G2>=100%),"Not Qualified",IF(NOT($M2>=100%),"Not Qualified",IF(NOT($S2>=100%),"Not Qualified",RANK($AG2,$AG2:$AG4)+SUMPRODUCT(--($AG$2:$AG$4=$AG2),--($D$2:$D$4>$D2)))))
Ranking_EE_111417.xlsx
0
Hi All,

I'm trying to tidy up a excel document  Sample.xlsx so that the rows under the column _category, are all in their own column rather than row.  There are a different number of rows for each product. Example of how it should look below.

I've tried vlookups and generally been scratching my head for an hour but can't figure out how to do it.

So it should look like:  

Products1 | simple | Products/Nozzles | Products/Nozzles/Manual | Brands/Quality |
0
I wish to add a tab to a tabstrip, dynamically at runtime, in an excel 2016 UserForm:
I need the Add Method:
Set tabXY = TabStrip1.Tabs.Add(tabName, tabCaption, tabIndex).

I got my code model from:
https://msdn.microsoft.com/en-us/library/aa733700(v=vs.60).aspx:
        Dim tabX As Tab
        Set tabX = tabData.Tabs.Add()

But, when I declare:
dim tabXY as tab, I am warned: "expected NEW, or TYPE NAME"

My VBE/VBA Project/References include:
Microsoft Forms 2.0 Object library
Microsoft Visual Basic for Applications Extensibility 5
The file has been saved and  reopened after adding ref '.... Extensibility 5'
Can you please explain?

thanks
Kelvin
0
Hi,

Excel 2016 provides correct row number of shape plus sign where as same code is not providing correct row number in excel 2013.
Below is the code used in macro.

Set b = ActiveSheet.Shapes(Application.Caller)
    With b.TopLeftCell
        lngMyRowNumber = .Row
    End With

Please suggest.

Thank you.
0

Microsoft Excel

132K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.