worksheet-function

How to Round in MS Access, VBA

Whats the best way to round in VBA Access? My current method utilizes the Excel method Excel.WorksheetFunction.Round(... But I am looking for a means that does not rely on Excel. ...

How do I slice an array in Excel VBA?

What function can I use in Excel VBA to slice an array? ...

Excel charts - setting series end dynamically

Hi, I've got a spreadsheet with plenty of graphs in it and one sheet with loads of data feeding those graphs. I've plotted the data on each graph using =Sheet1!$C5:$C$3000 This basically just plots the values in C5 to C3000 on a graph. Regularly though I just want to look at a subset of the data i.e. I might just want to look at t...

How to make a reference to a cell of another sheet, when the sheet name is the value of a cell?

Hi, In excel 2007, I have a formula in a cell like the following: =COUNTIFS('2008-10-31'!$C:$C;">="&'$A7) Now I want to make the name of the sheet ('2008-10-31') be dependent on the value of some cell (say A1). Something like: =COUNTIFS(A1!$C:$C;">="&'$A7) // error Is there is way to do this? Or do I have to write a VBA-Macro for...

String manipulation with Excel - how to remove part of a string if another part is there?

I've done some Googling, and can't find anything, though maybe I'm just looking in the wrong places. I'm also not very adept at VBA, but I'm sure I can figure it out with the right pointers :) I have a string I'm building that's a concatenation of various cells, based on various conditions. I hit these in order. =IF(A405<>A404,G405,G40...

Round function in Excel, worksheet function vs VBA

I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round funtion which I found to be retur...

Trying to improve efficiency of array formula

I have a SUM array formula that has multiple nested IF statements, making it very inefficient. My formula spans over 500 rows, but here is a simple version of it: {=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17>0, IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))} As you can see, the first half of the formula checks where the array is greater t...

How to zero fill a number inside of an Excel cell

How do you zero fill a number to 10 positions inside an excel spreadsheet? i.e. If cell A1 has 1234 how can cell A2 display 0000001234 (10 postions). ...

Copy data from one sheet to other, on selection of data from the dropdown list.

Hi, Please help me in writing the Macro.I have no idea writing VB Macro and its SyntaX. The scenario is: An excel document has two sheets.The first sheet contains the marks,grades and project details of students. The second sheet contains the dropdown lists of Project version number, different Company names, and post. Project version...

is there other way of writing range...

hi for example i have to write this in my formula Sheet1!A1:HM232 is there any other way of writing this (for example using only numbers, without any letters) Sheet1!Cells[1,1]:Cells[232,221] thanks in advance! ...

Find first non-empty cell in a spreadsheet row

I've got a fairly simple Excel spreadsheet with many rows of data. Each row contains empty and non-empty cells. Is there a simple way to determine the column letter/number of the cell in each row that contains the first non-empty cell. Ideally I'm after an Excel function to do the job, but I can't find one in the help. A|B|C|D|... 1|3...

MS Excel wildcard lookup

I have a lookup table in Excel with ColA and ColB : ColA ColB ColC va 1 value1 ColC is the actual value table I need to do a wildcard lookup with lookup_value = "value1" I need to perform an operation that if the value of ColC matches the characters in ColA then it should return me ColB value, i.e. 1 I want to use wil...

How to match a string wildcard pattern in an excel macro

I have a test like LEFT('F13',2)='F1' I want to change it from a left-side match to a test that supports wildcards 'F13'='F?3' Excel doesn't support regex except in VBA code but i'd prefer this was done in a macro. I should point out that the actual test isn't a simple string, but cell references (this may be important, I'm not sur...

Array Constants in Excel

I have an array constant defined in cell A1 as {1,2,3}. This displays as "1" (the first value in the array). I would like to have the formula SUM(A1) return 6. However, SUM is using A1 as a single-celled array, rather than the array constant contained inside A1 - and therefore SUM(A1) returns 1. Likewise, I would expect AVERAGE(A1)...

Selecting the max with a condition in excel

I have a range in excel with dates and values EDIT:image added I want to select the MAX value from the HIGH column for each YEAR (2009 would return 404, 2008 would return 390) I've done something similar but it's using SUMIF, not MAX. Any excel people in here that can help me out? ...

Excel Functions

I use this formula to track a recall list; It will tell me if a tool is due,(within a number of days),overdue,or OK. Works fine but I would like to add a "Not Required" if the due date cell,(K3), is left blank. =IF(2*(K3(K3>=$L$2)=0,“OK”, IF(2(K3=$L$2)=1,“DUE”,“OVERDUE”)) ...

How do I count wildcard characters using Excel COUNTIF?

How do I count the number of cells that contain the string "A*" - without treating the star as a wildcard character? ...

match and copy

Hi I am having some difficulty creating a macro - i need a macro whereby when the user input a dept code, the macro will take the number, go to first worksheet, read column B until it match the dept code, then copy all the appropriate rows for the dept code. I then need this to loop until the last worksheet. The workbook has about 9 work...

Pretty Print Excel Formulas?

Does anyone know of a (free) tool to pretty print Excel formulas? A Google search didn't turn anything up. I've got a few worksheets of semi-complex formulas to slog through, so this would make my life a bit easier. I'm just looking to turn something like this AC6+AD6+(IF(H6="Yes",1,IF(J6="Yes",1,0)))+IF(X6="Yes",1,0) into somethin...

Adding up all the positive numbers in Excel

Is there a way to add up all of the positive numbers in a row/column but ignoring all of the negative numbers? Like SUM(), except that it ignores negative numbers. Would I have to use VBA? If so, how would I do it in VBA? If it can't be done in Excel, can it be done in OpenOffice Calc? ...