Tagged Questions
0
votes
0answers
12 views
Runtime Error 1004 Unable To Set the Locked Property of the Range Class
Have searched around but I could not find the answer. The only thing it pointed to was merged cells however I do not have any. If someone wants to look at the file, I can only email as its too big to ...
0
votes
1answer
35 views
Is it possible to build a cumulative formula that will sum on new worksheet added daily?
I have a worksheet that the user opens each day and clicks a button that copies the current sheet to a new sheet and renames (Day 1, Day 2, etc.).
I want to keep a cumulative count in some of the ...
1
vote
1answer
30 views
LINEST function in excel vba
I am trying to figure out the 2 order equation (Y = ax^2 + bx + c) for a set of data values. When I use the inbuilt function in excel
=LINEST(C2533:C2537,B2533:B2537^{1,2})
I get the following ...
2
votes
2answers
244 views
How to implement MATCH and INDEX functions together in Excel VBA?
I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
...
1
vote
2answers
119 views
Passing Range to ABS function in VBA
I would like to perform a function which looks like this
Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))"
Since my range keeps varying I would like to pass the range as a variable which looks ...
0
votes
1answer
67 views
creating multiple plot per level of factor (trillis graph) in excel
Is there way to automate the process to make graphs like the following (also known as trallis graph) in excel ? I know there are some R interface with Excel (some of them need to be purchased), but I ...
0
votes
2answers
101 views
How can I turn a Calculated Column back into a data column in an Excel Table?
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
3
votes
2answers
265 views
Excel - concatenating cells where reference number matches [duplicate]
I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to ...
2
votes
1answer
72 views
Is there any way to programmatically stylize part of a string in a calculated cell in Excel 2010?
Let me explain what I mean.
I have a cell that contains something like this:
="String of text that's moderately long" & YEAR(NOW()) & "."
And I want to know if there's any way that, via ...
0
votes
1answer
89 views
How to achieve something like this in Excel?
So I have a table full of data which looks like this:
Subject A Subject B Subject C Subject D Count
Person A F F F 3
Person B F ...
0
votes
0answers
89 views
Creating a Macro or Formula to copy data through a query
Here is my formula that I'm looking to build off of:
=IFERROR(VLOOKUP($A1,Table2,10,FALSE),"")
I don't know if I can use just a formula or if it has to be a macro. I am trying to create a query ...
2
votes
1answer
124 views
Show (copy) data at “X” time and stop update
I have two sheets.
In the first sheet, cell F4, I have 00:00:00 (countdown). G9, G10 and G11 are cells that receive live data (decimal numbers).
In the second sheet, I have three cells linked from ...
0
votes
2answers
1k views
Using an insert if formula in excel with 3 conditions
Trying to do an insert if formula in excel like the following:
In my column K I want three conditions:
Date is Today or older (ie project is due today or earlier) = RED
Date is Today + up to 7 days ...
2
votes
0answers
278 views
How to have a cell calculate only once
I have cell N29 calculating some values based on streaming data. Once a condition is met in cell Q29, the calculation or final value should be passed to cell O29 and no longer calculate in either ...
0
votes
1answer
698 views
Selecting the date of every Monday and Wednesday from a date range
I need a formula or a macro to help automate a grade sheet's dates.
We have class every Monday or Wednesday only. I would like to vlookup from an input table of each quarter's date range, for ...
1
vote
3answers
1k views
How do I transpose data in columns to rows?
How do I transpose data in columns to rows?
If the data is:
Joe
some city
some state
Bob
no city
no state
and I use Paste Special ยป Transpose, I get
Joe some city some state Bob ...
4
votes
3answers
13k views
How to combine values from multiple rows into a single row in Excel?
I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...
2
votes
2answers
1k views
Excel VBA Function for Value AND Format Lookup
I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text ...
1
vote
1answer
751 views
Prevent Excel 2010 user from pasting formatting into cell
Is there a way to lock a cell so that a user can copy/paste a value into it, but not copy formatting to it? Locking the worksheet doesn't prevent the user from changing the formatting of the cell by ...
0
votes
2answers
1k views
Pass values instead of range to VBA Function
I am creating VBA functions in Excel 2007. One has a prototype:
Function ToLevelCode_Range(val As Double, ByRef R As Range)
I can call it this way just fine and it gets the correct result:
...
1
vote
1answer
2k views
excel 2010 vba macro ActiveCell.Formula = produces an empty cell when run
This macro formula (in a loop to place a Vlookup formula for each newly inserted worksheet) results in an empty cell when run:
ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", ...
3
votes
3answers
368 views
Is there a way to set the name box of a cell programmatically in Excel
I would like to programmatically set the name box of a cell in a worksheet I'm working on, either through a formula or through a macro. The end point is that I don't want to set it by hand.
In my ...
5
votes
5answers
5k views
How to return everything between two characters in Excel?
I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped.
I have a column of file names ...
2
votes
1answer
481 views
How to change a function/formula in a whole column where I also have empty cells
I have a column with a formula/function that has other cells as parameters. However in the column, every few rows I have a subtotal and blanks, for readability.
Now I decided to change the ...
2
votes
1answer
979 views
In Excel, doing a “Freeze Panes” on the right-most column instead of the left-most?
My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s).
However, I have a table where a very important column needs to be located ...
1
vote
3answers
251 views
Determining whether VBA is necessary in Excel, or if a pivot chart/table can suffice
I wish that I had a more precisely descriptive title for this question, but I am not very knowledgeable with Excel spreadsheets or VBA (I'm primarily a Java developer, and don't play much in the ...
1
vote
4answers
2k views
How to input only unique values in a column in Excel 2007
I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't ...
