All Questions
196
questions
11
votes
2
answers
76k
views
How can I insert variable into formula in VBA
Can anyone solve this?
Sub test
Dim i as integer
For I = 1 to 10
ActiveCell.Offset(0, 2).Formula = "=Sum(E15,&i&)"
Next I
End Sub
- 313
94
votes
9
answers
437k
views
How to turn a string formula into a "real" formula?
I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?
- 1,115
22
votes
6
answers
71k
views
Count Unique values with a condition
In column A I have list of different names. In column B, I have values either 0 or 1.
I want to get a count of all the unique names from column A which have 1 in column B.
Using below array formula ...
- 2,192
29
votes
6
answers
36k
views
Get formula from Excel cell with python xlrd
I have to port an algorithm from an Excel sheet to python code but I have to reverse engineer the algorithm from the Excel file.
The Excel sheet is quite complicated, it contains many cells in which ...
- 1,697
31
votes
5
answers
95k
views
Getting formula of another cell in target cell
How does one cell obtain the formula of another cell as text without using VBA? I can see this question has already been asked many times and the answer is always to write a custom function in VBA.
...
- 2,325
20
votes
4
answers
342k
views
Fill formula down till last row in column
I'm trying to draw down the formula that's in cell M3 to the end of the data set.
I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells with a ...
- 377
10
votes
4
answers
86k
views
How can I run VBA code each time a cell gets its value changed by a formula?
How can I run a VBA function each time a cell gets its value changed by a formula?
I've managed to run code when a cell gets its value changed by the user, but it doesn't work when the value is ...
- 1,115
3
votes
1
answer
6k
views
Concatenate column headers if value in rows below is non-blank
I have table with data in the format below.
Data
I want the finished table to have the blank column to be populated like the highlighted one below.
Finished Table
So, I need a formula or VBA that ...
- 49
4
votes
2
answers
26k
views
Optimizing Excel formulas - SUMPRODUCT vs SUMIFS/COUNTIFS
According to a couple of web sites, SUMIFS and COUNTIFS are faster than SUMPRODUCT (for example: http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html). I have a worksheet ...
- 3,487
4
votes
1
answer
26k
views
VBA to open Excel hyperlink does not work when hyperlink generated with a formula
There seems to be a bug with Excel hyperlinks which are generated with a formula. I'm using Excel 2010. I have a spreadsheet with cells containing URLs, and my goal is to do the following two things:...
- 2,491
6
votes
4
answers
215k
views
Comparing two columns, and returning a specific adjacent cell in Excel
I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula.
Comparing two columns for matches is easy enough. the tough part has ...
25
votes
8
answers
112k
views
Simulate string split function in Excel formula
I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g.
string words = "some text".split(' ');
The problem is that I can't be sure that there ...
- 12.1k
20
votes
7
answers
47k
views
HTML to Excel: How can tell Excel to treat columns as numbers?
I need to achieve the following when opening an HTML in Excel (Response.contentType="application/vnd.ms-excel") :
force Excel to consider content of td cells as numbers
make the above so that any ...
Wheelwright
17
votes
5
answers
80k
views
How to define a non continuous range in COUNTIF
A B C
1 Β Β
2 Β Β
3 Α Α
4 Α Α
5 Β Β
6 Α Α
7 Α B
8 Β Β
9 Β Β
10 Α Α
11 Β A
12 Α Α
13 Α Α
14 Β ...
- 30.3k
2
votes
2
answers
24k
views
Array formula result concatenated into single cell
Is it possible to take the return values from an array formula and concatenate them into a single cell? For example, I have a simple spreadsheet with rows for project tasks which looks like this:
...
- 1,812