In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?
The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.
GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.
$A$2:$A$332 is the column which contains the G/L account number to be matched.
$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.
$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.
I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.
I am looking for a solution to dynamically create named ranges according to the contents of cells in a particular column. The following code works for 1 word names, but in many cases the title cell contains numerous words separated by spaces. Is there a way I can adapt this code so that it will name the ranges with the spaces removed? For example, where cell C2 contains the narrative 'Sales Ledger Control', I would want the range name 'SalesLedgerControl'.
Range("R2:Z2").Name = Range("C2")
The named ranges are referred to in numerous other worksheets, where selecting a particular narrative from a listbox creates a dependant drop-down in the adjacent cell (eg if Sales Ledger Control is selected, the dependant list contains names of customers). FYI, the data validation in the other worksheets ignores the spaces in the range names, ie: =INDIRECT(SUBSTITUTE(G2," ",""))
It would not take me long to name the ranges manually, but a macro is preferable because the narratives in the title cells will often change and the range names will obvioulsy also therefore change.
Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:
I have a worksheet that has a column labeled "Sort Order". It is always the farthest, right-hand column. I also have a macro that sorts and re-numbers the data in that column.
My problem is that I am constantly inserting columns, so the label/count of the actual column to be sorted changes. How do I write the VBA code to automatically determine the last column?
So I have got an identifier and a corresponding column of values. I need to calculate sum of those corresponding values for each identifier, so if identifier is 1993, I need to have a sum of all the corresponding values. These identifiers repeat, and I do not want to calculate sum for all of them, just the immediate identifiers (i.e. you have 1993, after that you may have 1994 etc etc and then at one point you will have 1993 again, but that "new" 1993 will have a different sum of course). Also, identifiers do not go in sequence sometimes (however, most of the case they do), for example 1993 may jump suddenly to 2004. The trick with all of this is variable "row width" of these identifiers...
Sheet1 contains column A as "Product Number", Column B as "Product Family". Rows contain about 20,000 lines of data. A Range has been created to cover both columns, and named "ProdRange"
Sheet2 contains Column A (blank), Column B with "Product Number"
What I am trying to do is start at the top of Sheet 2 "Product Number" (B2 is first cell ref), and use this value to do a Vlookup on Sheet 1, to extract the "Product Family" code on the Offset 1 - and insert it back into Sheet 2 Column A.
I have this below :
For i = 2 To LastRow Range("A" & i).Value = "=VLookup("B" & i, ProdRange, 2)" Next i
BUT for some reason it doesn't like the [ "B" & i ] part of the equation.
I cannot seem to get the worksheet function sum to work with varables in this module. It is placing zeros where the summed data should be. The variable are showing proper start and end ranges for summation....
Need to pull data from Sheet4 to sheet1 by Sales Person based on a validation list cell on sheet1.
Each Sales person has a different number of accounts listed on Sheet4. The data is setup like this (my apologies for not knowing how to copy and paste the data)
Will Use Jane Doe and John Smith As examples-
A1, Jane Doe, Customer Name, Data, Data, Data, etc. A2, Jane Doe, Customer Name, Data, Data, Data, etc. A3, John Smith, Customer Name, Data, Data, Data, etc.
So Jane Doe has 2 customers total, and John Smith has 1 customer.
I am dealing with a total of 300+ Sales People and over 4,000 customers, all with a different number of customers per sales person.
How in the world can I write a code that will pull all of Jane Doe's customers when she is chosen from the validation list, and paste those customers and their coinciding data where I need it to paste?
I have the validation list working, so all I need at this point is help with the copy/paste code.
I need to run a formula each month which calculates the ‘product’ for a range of cells in a column, where the cell range will change each month. The starting cell for the range is static, but the range end will always change - additionally, the last valid cell will always be followed by a null value cell. The attached example shows the basic format of my data – a range of values, by row, which will always end with a null value cell. The formula I’m using (to match my example) is: =(PRODUCT(A10:A13) –-> and I want to display the result in cell C10.
While I’ve been able to identify the ‘ending good row’ several ways, I haven’t been able to figure out what to do with this information – I seem be be lost in the translation of OFFSET values to CELL REFERENCE values. Has anyone encountered this specific scenario and found a solution?
Need formula which can sum Amounts from varying Weekly time periods and the result be recorded in the appropriate month? I've attached a simple example of the way the output needs to look and a sample data table below.
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2() Dim WhatToPrint As String WhatToPrint = Sheets("Reports").Cells(3, 6).Value 'sets the variable to equal the contents of cell D3 which contains the formula 'summarising the print ranges I want to print Sheets("Reports").Cells(3, 6).Select ActiveCell.FormulaR1C1 = WhatToPrint ' pastes the variable in cell F3 - just to check that it looks like I want it to Sheets("Reports").PageSetup.PrintArea = WhatToPrint 'uses the variable to set print area - this is where it fails! 'if you replace the variable with the contents of cell F3 the macro will work ActiveWindow.SelectedSheets.PrintPreview End Sub
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB: 'activate sheet and chart Worksheets("Home Page").Activate ActiveSheet.ChartObjects("Histogram").Activate
I am trying to use a combination of dependent named ranges and variable length ranges, so one can select in eg Col A truck name from a drop down list, and then in Col B, the engine variant only for that make of truck. The indirect(substitute) function works well if I define the dependent range name (the engine variant) using actual cell refs. However if I use the variable length name definition as described in Ozgrid (ie with offset function), I get an error message and the dropdown menu freezes. Is there anyway to overcome this without resorting to VBA?
I have attached the xls. I have an input sheet with 3 columns to enter data. Each column is linked to a separate worksheet with a formula (Carrys 1000 rows long). I need to be able to pull the populated data from those 3 worksheets and paste into 1 column continuously on another worksheet so all data is on top of another without any spaces.
I made an if statement so that if there isn't data pulling from the input sheet the a blank cell is left to indicate the last row to copy data from and paste on the final sheet.
Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.
For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct # Date Time City Called from State Called From Called # City Called State Called................
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test() Dim StartRng As Range Dim Buffer As Range
Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes
I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.
There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.
I basically am trying to just reference another cell (from a pivot table) from another sheet. ='SHEETNAME'!G5
But I Need the G to change... I basically need ='SHEETNAME'! + E29 + 5 to equal
='SHEETNAME'!G5 assuming G is in E29 but am not sure how to do this.
The other option is to use =GETPIVOTDATA("Sum of Amount_19",'SHEETNAME'!$A$3,"Label","Title") but I need "Sum of Amount_19" to change based on another cell, lets say F29.
I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...
How would I go about dynamically picking up the sheet name. and referring to it?
For example if I have a column with all the different sheet names in it:
I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.
Is it possible to execute the code below when the user selects print rather than when opening the tab?
In the attached sample workbook I am trying to add the vendor number down the column H for each PO. I have copied a few of the numbers into the column, (in red), to show the desired result.
Basically, I will have a report daily with a variable number of suppliers, and variable PO's for each supplier. I need to add the vendor # to each row.
I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.
I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.
