Posts by naira

Re: Modify Macro to copy formula to new range and update cell references

Thanks for the reply S O,

The code almost works. In the sense that the first row where the code is copied now takes A2=AA2+AB2 BUT the increment in next rows is in Twos..

So that when I run the above code, A2=AA2+AB2 (which is correct and as required) ... but A3 is now =AA4+AB4, A4 is now =AA6+AB6

Am attaching a sample workbook with the data and code in it.
Can you please take a look and advise.

PS: My request was not a typo. I want to copy the formula in Row 10 (A10:D:10) to rows 2 to 9 i.e. to the range A2:D:9 just as would happen when one would select cells A10:D10 and drag them up or down (your code understands my requirement exactly).

Hi!

I need a macro that copies formulae from multiple cells in a row to multiple rows and also updates the cell references.

For example, if cell A10 has formula =AA10+AB10, cell B10 has formula AB10+AC10, I need to copy these formula upwards to rows A2:B9 (each formula is to be copied upwards in the same column), so that formula in A9 is =AA9+AB9, in B9 is AB9+AC9, in A8 is AA8+AB8, in B8 is AB8+AC8 and so on as below:

A2...
...
A8=AA8+AB8; B8=AB8+AC8 (New copied formula)
A9=AA9+AB9; B9=AB9+AC9

A10=AA10+AB10; B10=AB10+AC10 (original formula)

The following macro copies formula in a range to a new range, but the starting reference is the same as in the original formula. For example, if the formula in cell A10 is AA10+AB10, the formula in Cell A2 will be AA10+AB10 (same as in cell A10) and is copied down so that formula in Cell A3 is AA11+AB11 instead of AA3+AB3

Sub formula ()
With Sheets ("Sheet1")
 .Range("A2:D9").formula = .Range("A10:D10").formula
End With
End Sub

Is it possible to modify it to update cell references after copying to new range

Re: Formula to chart every nth row data using named ranges (without using another col

Tried NBVC's solution and first solution works great with charting individual rows.

Second formula however is a bit off the mark. Lots of trial and I still don't seem to be able to get it to work...

What I was looking for is that if I entered 3, then I wish to sum up values for every 3 rows and then plot a column chart having sum of every 3 rows in a column in each individual column.
For example, the desired result when using the data range given in my original post (and entering 3 in cell A1), my chart should be plotting bars values of:

Bar 1: 154 (10+26+18) (viz. sum of row nos. 1,2,3)
Bar 2: 48 (16+20+12) ,(viz. sum of row nos. 4,5,6)
Bar 3: 54 (24+5+25) ,(viz. sum of row nos. 7,8,9)
Bar 4: 46 (14+9+23) (viz. sum of row nos. 10,11,12)

However, the formula =SUM(OFFSET($A$3,((ROW(Sht!$A$3:INDEX(Sht!$A:$A,21))-3)*Sht!$A$1),0,$A$1,1)) is summing up the values of every 3rd row and giving me a single bar having the sum of every third row, i.e. I am getting a single bar summing up values of row nos. 1,4,7,10...

Interestingly, when I enter the formula =SUM(OFFSET(sht!$A$3,((ROW(sht!$A3:INDEX(sht!$A:$A,21))-3)*sht!$A$1),0,sht!$A$1,1)) in a cell in excel and drag it down, I get the desired resulting values. But it does not seem to be working when entered as a formula in a named range for mapping a chart.

Could you please help me figure out what am I missing?

Sample file showing result of the formulas when used as a named range attached for reference.

Re: Formula to chart every nth row data using named ranges (without using another col

Hi Ger,

Thank you for your reply.
However, I do care how we arrive at the solution, since as I had specified in my original Post, I already have a working solution

Quote

One way of doing this is to first pull the value of every nth row into another column and then plot the chart using this new range. However, this is needless duplication of data, (and given that I will be plotting a no. of series and a no. of charts, this is creating a lot of duplication) and slowing my charts down.

Unfortunately your solution is even more cumbersome than what I am already doing.

Fortunately, I have found that Excel has usually not disappointed the wildest fantasies that come up in my head. Its just been a matter of time and looking hard enough. In one case, I found solution to a question posted on this forum, after two years.

Fortunately again, I have found a working solution to my current question and in exactly the form that I was looking for (although I am still looking for help to fine-tune the solution).

The formula in the named range for non-contiguous data arranged in a column looks like this:

=N(OFFSET(Sht!$B$3,0,(COLUMN(Sht!$B$3:INDEX(Sht!$2:$2,8))-2)*Sht!$A$1))

with the reference cell where I enter the no. of columns to skip being cell A1

However:

1. When I convert this to a row formula

=N(OFFSET(Sht!$A$3,(ROW(Sht!$A$3:INDEX(Sht!$2:$2,8))-2)*Sht!$A$1,0))

my charts are plotting only the first 2 data points. Anyone has any ideas how to fine-tune this so that all data points are plotted with data in on-contiguous rows?

2. I am also looking for a solution to further fine tune this formula to sum up groups of n cells and plot a column chart with the summed up values.

- New sample file showing the working solution for plotting data in non-contiguous columns attached

Regards,

Naira

Hi!

This post consists of 2 inter-related questions:

1. I have a column of data as below from which I wish to plot a chart of every nth row, using Excel's named range as my series. The named range should be where I should be able to enter a formula to pull data every nth row.

Data
10
26
18
16
20
12
24
5
25
14
9
23
17
14
29
7
4
13
25

For example, if the data is arranged in column B, and I enter a value of 3 in cell C1, I want the chart to plot only data in the 1,3,7,10th row and so on...
So, from the above data range, if I entered 3 in cell C1, I want excel chart to plot the values 10,16, 24, 14 ....

One way of doing this is to first pull the value of every nth row into another column and then plot the chart using this new range. However, this is needless duplication of data, (and given that I will be plotting a no. of series and a no. of charts, this is creating a lot of duplication) and slowing my charts down.

How do I plot every nth row using Excel's named range as my series with a formula to define the values to be pulled in the series?

2. Another question is that if I entered 3, then I wish to sum up values for every 3 rows and then plot a column chart having sum of every 3 rows in a column.
For example, using the above data, my bars will be plotting values of 54 (10+26+18), 48 (16+20+12), 54 (24+5+25), 46 (14+9+23).....

Is this also possible with named range as my series with a formula to define the values to be pulled in the series?

Sample file attached.

Regards,

Naira

Re: Vlookup Backwards

Not sure if I should be adding something to a more than 9 year old thread, but I have had to struggle a lot every time my data setup changes and I have to literally spend hours and days moving everything around. Also, found a lot of people asking if Reverse Vlookups are possible.

So in the spirit of helping, answer is Yes!
Use this formula:

=VLOOKUP(C1,CHOOSE({1,2},B:B,A:A),2,FALSE)

Credits to Ace_XL at http://www.excelforum.com/exce…up-reading-backwards.html
Detailed explanation as to how this formula works is at http://chandoo.org/wp/2012/09/06/formula-forensics-no-028/

Hope this helps a lot of guys....

Hi!

Following code is used to arrange the layout of the 3 windows opened in the same instance of excel so that all 3 are visible together on the same screen in a particular sequence overlapping each other.
Window(1) of Book1 will be "Sheeta", Window(2) will be "Sheetb" of Book1 and Window(3) will be Sheet1 of Book2.

Option Explicit
Sub win()
On Error Resume Next
    Dim w1 As Window, w2 As Window, w3 As Window
    Set w1 = ThisWorkbook.Windows(1)
    Set w2 = ThisWorkbook.Windows(2)
    Set w3 = Workbooks("Book2.xlsx").Windows(1)
    With w1
        .WindowState = xlNormal
        .Top = -13
        .Left = 0
        .Height = Application.UsableHeight * 1.25
        .Width = Application.UsableWidth * 0.25
    End With
    With w2
        .WindowState = xlNormal
        .Top = 300
        .Left = 0
        .Height = Application.UsableHeight * 0.75
        .Width = Application.UsableWidth
    End With
    With w3
        .WindowState = xlNormal
        .Top = -13
        .Left = 255
        .Height = Application.UsableHeight * 0.75
        .Width = Application.UsableWidth * 0.75
    End With
Worksheets("sheeta").Activate
Windows(2).Activate
Application.Windows("book2.xlsx").Activate


End Sub

I need to run the above code every minute using the OnTime Function so as to regularly re-arrange the layout in the in case the layout changes for whatever reasons (such as saving the workbook, clicking on some other sheet of one of the workbooks etc.).

Problem is that the above code needs to be placed in ThisWorkbook module. If I place it in a standard module along with the OnTime Function the code following code gives error

Worksheets("sheeta").Activate
Windows(2).Activate
Application.Windows("book2.xlsx").Activate

In order to run the OnTime Function, I need to place the code running OnTime in a Standard Module from where I cannot call the above code.
My OnTime code looks like this:

Sub Save()
    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual: Application.EnableEvents = False
        ThisWorkbook.Save
        TimeToRun = Now + TimeValue("00:00:17")
        Application.OnTime TimeToRun, "Save"
    Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic: Application.ScreenUpdating = True
End Sub

Any ideas how to solve this?

Sample Workbook having the above code attached.

Thanks

Re: Modifying a VBA to set size & position of window of another workbook

Those looking to use the above code may find the following codes useful as well:

This one is used to activate another window (apart from the window from where the VBA code was initially run:

Windows(2).Activate

where the no. in the bracket is the window no. one would see in the workbook caption.

This code would be useful if one wants to activate (bring in front) another workbook, other than the workbook from where the code was initially run:

Application.Windows("book2.xlsx").Activate

Re: Modifying a VBA to set size & position of window of another workbook

Thanx, works nicely.

Hi!

Below macro when run, opens a new excel window of the same workbook (lets say Book1.xlsm) and is used to set the size and position of both the worksheet windows on the screen.

However, besides setting the size and position of both the windows of the current workbook, I also need to set the size and position of another workbook (lets say Book2.xlsx) with the same macro. Book2.xlsx will be open at the time of running the macro.

Sub win()
    Dim myWindow1 As Window, myWindow2 As Window
    Set myWindow1 = ActiveWindow
    Set myWindow2 = myWindow1.NewWindow
    With myWindow1
        .WindowState = xlNormal
        .Top = -13
        .Left = 0
        .Height = Application.UsableHeight * 1.25
        .Width = Application.UsableWidth * 0.25
    End With
    With myWindow2
        .WindowState = xlNormal
        .Top = 300
        .Left = 0
        .Height = Application.UsableHeight * 0.75
        .Width = Application.UsableWidth
    End With
End Sub

Can someone please modify this code so as to do this?

PS:
1. I know that my requirement can be met by using the Save layout option of Excel, but there are a few reasons why that is working for me.
2. Also, Book2's size and position can also be set by running a similar macro from within that, but I cannot change the file format of the other file from .xlsx to .xlsm.

Sample file attached

Thanx,

Naira

Re: Setting customised zoom level for individual sheets at Workbook Open?

Quote from iwrk4dedpr;720710

You have the loop .... just have to set the zoom per each sheet upon activating it.

Thanx!!! Works like a charm!

For those wondering or new to VBA, add the below code above iwk4dedpr's solution and the zoom levels will be set automatically to the desired level at the time of workbook opening:

Private Sub Workbook_Open()
    Dim ws As Worksheet

Hi!

I have a quite a few worksheets (let us say Sheet1, Sheet2, Sheet3) in my workbook, each with individual levels of zoom.
I have the following code that can set zoom levels for individual worksheets each time a worksheet is accessed:

Private Sub Worksheet_Activate()
 Windows(1).Zoom = 80
End Sub

However, the problem (as with all macros) is that the above code disables the undo function in case I change back and forth from individual sheets, since it is run every time I access a sheet.

Following macro sets a default zoom level for all worksheets at the time of opening the workbook:

For Each ws In Worksheets 
 ActiveWindow.Zoom = 85 
 Next ws 
End Sub

But this second code sets a single zoom level for all workbooks.

I was wondering how to combine these 2 codes to set customised zoom level to each of the worksheets (Sheet1, Sheet2, Sheet3). But I wish to set this ONLY at the time of opening the workbook and not afterwards. In other words, can I point the zoom level for each worksheet at the time of workbook open?

Regards,

Naira

Re: Hiding Workbook title bars when 2 workbooks arranged horizontally?

No working solution received on this forum so thread also posted at Excel forum at http://www.excelforum.com/exce…instance.html#post3663273

Re: get data from right after last comma

Thanx for the replies guys.

Why I would want to reduce the size of the formula is because I am running about 2.5 million calculations on a single workbook and my work sheet is being updated by real-time sub-second data, so I try to squeeze out anything that's not useful.

Any ideas why the first formula isn't working? May be someone could explain what is wrong with the logic of the first one?

Hi!

My data in Col. A looks like this:

1,2,3,432
1,2,3,4
1,2,3,43

I wish to get data after the last comma in each row (my data does not have any spaces). So my result would look like this:

432
4
43

I have 2 formula which I feel should do the work:

1. =RIGHT(A1,(FIND(",",A1))) --> However, this is only giving the last 2 characters in the result (including comma). Any advise to fine tune it?

2. =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)) --> works nice and gives exact result. However, this was designed for extracting data from string with spaces, so not sure what trim and substitute would be doing here. Any advise on how to make it simpler/ reduce its length by chopping off the un-necessary code?

Regards,

Naira

Re: Hiding Workbook title bars when 2 workbooks arranged horizontally?

yup, already did tht and it doesn't help.
tht's what I meant when I said I m not luking for a simple vanilla full screen

Hi!

I am using 2 excel workbooks arranged horizontally to view a lot of data on a single screen and have a requirement to use as much space as possible.
In case a single excel workbook is open, the name of the program viz. Excel and the Workbook name are displayed in a single title bar. Using the code below, I have been able to remove this single Excel Title bar.

However, in case 2 workbooks are open (and arranged horizontally) in the same instance of Excel, there is an overall Excel window with a title bar (which my code has been able to remove), and then each workbook opens in a window of its own and each workbook has a title bar.

I need help in removing these 2 Workbook title bars. Am posting the code in the hope that it would give some idea to remove the excel workbook title bars as well.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000
Private Const WS_MAXIMIZEBOX = &H10000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WS_SYSMENU = &H80000
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Enum ESetWindowPosStyles
  SWP_SHOWWINDOW = &H40
  SWP_HIDEWINDOW = &H80
  SWP_FRAMECHANGED = &H20
  SWP_NOACTIVATE = &H10
  SWP_NOCOPYBITS = &H100
  SWP_NOMOVE = &H2
  SWP_NOOWNERZORDER = &H200
  SWP_NOREDRAW = &H8
  SWP_NOREPOSITION = SWP_NOOWNERZORDER
  SWP_NOSIZE = &H1
  SWP_NOZORDER = &H4
  SWP_DRAWFRAME = SWP_FRAMECHANGED
  HWND_NOTOPMOST = -2
End Enum
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
 Private Type RECT
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type
Sub Title_Show()
  ShowTitleBar True
End Sub
Sub Title_Hide()
  ShowTitleBar False
End Sub
Sub ShowTitleBar(bShow As Boolean)
  Dim lStyle As Long
  Dim tRect As RECT
  Dim xlHnd As Long
  xlHnd = Application.hwnd
  '// Get the window's position:
  GetWindowRect xlHnd, tRect
  '// Show the Title bar ?
  If Not bShow Then
    lStyle = GetWindowLong(xlHnd, GWL_STYLE)
    lStyle = lStyle And Not WS_SYSMENU
    lStyle = lStyle And Not WS_MAXIMIZEBOX
    lStyle = lStyle And Not WS_MINIMIZEBOX
    lStyle = lStyle And Not WS_CAPTION
  Else
    lStyle = GetWindowLong(xlHnd, GWL_STYLE)
    lStyle = lStyle Or WS_SYSMENU
    lStyle = lStyle Or WS_MAXIMIZEBOX
    lStyle = lStyle Or WS_MINIMIZEBOX
    lStyle = lStyle Or WS_CAPTION
  End If
  SetWindowLong xlHnd, GWL_STYLE, lStyle
  Application.DisplayFullScreen = Not bShow
  '// Ensure the style is set and makes the xlwindow the
  '// same size, regardless of the title bar.
  SetWindowPos xlHnd, 0, tRect.Left, tRect.Top, tRect.Right - tRect.Left, tRect.Bottom - tRect.Top, SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED
End Sub

Sample file having code attached for reference.
Also, it seems Ozgrid does not allow upload of pictures so I am unable to upload screenshot of the Workbook title bars that I want removed, but if you open 2 workbooks, arrange them horizontally (use Alt+W+A+O and Enter) and then run my code, you will know which bars to be removed.

Please note: I am not looking for a simple full screen mode alone, but more than that.

Regards,
Naira

Re: Finding avg weight of varying no. of sales designs

Works great.
thanks a lot

Hi!

I have a no. of sales designs whose no. keeps varying in my database. I want to find out the Avg weight of sales of each design and compare it with the sales of that design by each individual sales person, without sorting out the data in the col.

For example, sales person A sells 20 pieces of design 4 and B sells 10, so my result should say 15 in front of col. for design 4 for both A & B

Sample file with sample result attached

My data is as follows:
[TABLE="width: 203"]

Sales Person

[TD="align: right"]Design no.
[/TD]
[TD="align: right"]Pieces sold
[/TD]

B

[TD="align: right"]4
[/TD]
[TD="align: right"]10
[/TD]

C

[TD="align: right"]3
[/TD]
[TD="align: right"]11
[/TD]

D

[TD="align: right"]4
[/TD]
[TD="align: right"]13
[/TD]

E

[TD="align: right"]2
[/TD]
[TD="align: right"]18
[/TD]

F

[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]

G

[TD="align: right"]4
[/TD]
[TD="align: right"]7
[/TD]

H

[TD="align: right"]1
[/TD]
[TD="align: right"]6
[/TD]

I

[TD="align: right"]5
[/TD]
[TD="align: right"]3
[/TD]

J

[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]

K

[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]

L

[TD="align: right"]2
[/TD]
[TD="align: right"]19
[/TD]

M

[TD="align: right"]1
[/TD]
[TD="align: right"]7
[/TD]

N

[TD="align: right"]5
[/TD]
[TD="align: right"]12
[/TD]

O

[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]

P

[TD="align: right"]2
[/TD]
[TD="align: right"]9
[/TD]

[/TABLE]

Re: 2 Sparklines in a single cell in excel?

Note: Since no working solution given on this forum, hence question also posted at http://www.excelforum.com/exce…in-excel.html#post3596136 If a working solution is available, will update on both the threads