Verify range is square
Greetings. I am needing to do MMULT on a variable square range an unknown
number of times. I have it working, but I want to be able to verify the
range is square. From the below sub, I can not figure out a way to verify
that MatrixRange is actually square. Also, if InputBox("Power") is null, it
throws an error. I can't figure out how to deal with that either. Thank you.
Dim OffsetCount As Integer
Dim Power As Long
Dim MatrixRange As Range
Dim ResultRange A...Use sheet names in column in COUNTIF formula?
I have the names of my worksheets in Col A. I want to do a COUNTIF
for all these sheets in the same range on every sheet. Is there some
way I can incorporate the cell with the sheet name into the formula?
Col A Col B
And then of course copy it down the column to pick up all the sheet
you use the INDIRECT function to do this, like so:
Then copy down.
Hope this ...Copying formulas #11
Can't seem to find this tip, probably calling it the wrong name. Want
to copy a formula (x/y) from cell to cell, but keep the denominator (y)
pointed at the same cell each time.
DQ1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26841
View this thread: http://www.excelforum.com/showthread.php?threadid=400847
Put a dollar sign in front of the constituents of the fixed cell. For
example, in a simple fractional equation
The contents of cell B23 will always be the d...need formula to search column for a word and return another word
I have a multi-worksheet workbook where the 1st worksheet is an assets
summary page and the other worksheets are for tracking individual assets.
Each row of the individual asset worksheet represent an asset request. A
column on the assets worksheet titled "order status" will have one of three
inputs for each request: "Open", "Pending", or "Complete". On the summary
worksheet, each row represents an individual asset. There is a column on the
summary worksheet titled "Item Status". I need a formula to look at the
asset worksheet "...VERIFY REPORT Question
Ive a question about reports.
I appreciate that to enable you to filter on custom fields from
standard crm reports you need to verify the database. Now, does this
mean that every time i add in a new column to crm i have to go through
and verify each of my reports??
that is correct. until you do this each report will not know about the new
Microsoft CRM MVP
"Jo Hughes" <email@example.com> wrote in message
> Ive a question ...Access Forms displaying records with mathemical formulas
I need to create a MS Access database to manage a collection of
roughly 5,000 memos. Each memo is essentially a text, anywhere from 50
to 1,000 words, and most memos contain mathematical formulas requiring
specific formatting. Currently, the memos are scanned pictures and I
have designed an MS Access form enabling me to view each picture
according to where it is located.
I would now like to have all my pictures converted into searchable
documents (such as MS Word files) that I could easily manipulate and,
most importanlty, still view from within an MS Access database. My
general high-lev...can't change formula toolbar height
Whenever I open either a new sheet or an existing
worksheet the formula toolbar/namebox is way to high, the
fonts inside the namebox are 20 and the font size in the
Normal style is 20. I can change the Normal style to 10pt,
but it doesn't change the formula height or the font in
the namebox. And it doesn't stay changed. When I close the
program and restart it the huge fonts are back.
You should change the font name/size specified under Tools, Options,
General. Then restart Excel. This will change the definition of the
default Normal style for _new_ workbooks only...working with length formula
I am looking at a cells that only contain numbers. (4,5,6, or 7 digits) Any
number that is 6 digits or less, leave as is. IF the number is 7 digits, I
want to remove the right most number i.e. Tx
1234 result = 1234
12345 result = 12345
123456 result = 123456
1234567 result = 123456
Portland, Oregon USA .
(editorial review of special sort excel add-in (30 ways to sort)
wrote in message
>I...Big challenge!! Formula for count last 10 columns since today!
I want to count the numbers of cells with a certain value in it, in a range
which starts and ends based on the current date.
I have this list of attendence: For every person we register if they were at
training or game: training are on wednesday and friday, games sometimes on
saturday. For each 'appointment' (training/games-) we have a column. For
each player we have a row. If they were at training, we put "1", if they
weren't but they warned us we put a "v", and if we didn't get any news at
all we put nothing in it.
5-08 7-08 12-08 1...VBA Range Question
In VBA how do I find the first and last row number in a range?
I need to perform various calculations on different segments of a range
(single column range) and my first problem is to identify the first and last
row. Then I'll loop through the rows and perform various calculations.
P.S. Is there a VBA reference somewhere that would help me answer questions
like above? Using the VBA help facility doesn't seem to help, probably
because of my low expertise in VBA.
Look at this:
Dim MyRange As Range
Set MyRange = Range("A10:A20")
FirstRow = MyRa...Formula for making a number 3 digits less...
If I have a value, say $1,000,000 and I want to show it as $1,000 (w
note at the top its in thousands)... I know i can just divide by 100
to get that result, but is there an actual formula name that doe
this... just curious
Message posted from http://www.ExcelForum.com
On Thu, 1 Apr 2004 12:30:41 -0600, johnnyrad
>If I have a value, say $1,000,000 and I want to show it as $1,000 (we
>note at the top its in thousands)... I know i can just divide by 1000
>to get that result, but is there an actual formula name that doe...Formula for changing a cell format
How do I change a cell format if certain text is displayed in a nother cell?
e.g; cells A4 and B4 (will change to a black pattern background) if C4
- select A4:B4
- goto 'Format - Conditional Format'
- enter the formula:
- choose your format
> How do I change a cell format if certain text is displayed in a
> nother cell?
> e.g; cells A4 and B4 (will change to a black pattern background) if
> displays "Test"?
...make formula look at diff sheet
I have this formula that works great if the data is on the same
I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
And it returns the 2nd row on the sheet the formula is on rather than
Any help would be appreciated. Not exactly sure how this bulletin
board works. Thanks.
Try it like this...
=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B1:M1,0)+1,,,&...conversion formula for TV Frame rate's
Hi, I no there's probably a really easy way to do this, as I can work i
out in my head without any trouble, but I'm trying to work out
formula in Excel that converts lets say 100 frame's into 00:04:0
format. Can it be done? and am I really stupid
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
I assume you want a formula for 24 frames per second.
Try the following formula:
=3DTEXT(INT(B18/1440),"00")&":"&TEXT(INT((B18-I...need help on macro/VBA programming selecting a range
I would like a macro to allow a user to select a cell in column A on Sheet 2.
If the user selects A6 I would like the macro to select the Range (A6:BF6) in Sheet2 and copy that to Sheet1 starting in cell A1.
Thank You in advance.
Range(ActiveCell, Range("BF" & ActiveCell.Row)).Copy _
Gord Dibben MS Excel MVP
On Thu, 18 Jun 2009 16:58:32 -0500, Ron Berns
>I would like a macro to allow a user to select a cell in col...Excel shows formulas, notthe value
I looked in Tools/options and under view I do not have formulas selected.
What else might cause this?
Text format in the cells with the formulas?
Select the cell with formulas, format as general under>format>cells>number
do edit>replace find what = replace with = (you replace an equal sign with
an equal sign, that usually forces
the formulas to calculate)
"Sherry" <firstname.lastname@example.org> wrote in message
> I looked in Tools/options and under view I do not have formulas selected.
>...Cannot paste a block of formulas from one sheet to another
I am using Excel 2003, SP3
I used to never have problems pasting a block of formulas from one
spreadsheet to another (paste special, formulas).
Today it is pasting the values from the original spreadsheet instead of the
The spreadsheets are nearly identical except for file name (using batch
numbers as file names.).
Do you have both workbooks open in the same instance of excel?
If you have two instances of excel running, you can see this.
> I am using Excel 2003, SP3
> I used to never have problems pasting a block of formulas fr...Missing formula bar in Excel
I have Office v.X for Mac
Just today, I noticed that I can no longer get the formula bar to appear --
even if I try to show it via the View menu and/or to check it in the
Preferences menu. I do not know what I may have done to make this happen. I
have not been using Excel for several days. I have tried re-installing just
the Excel application from my original Office v.X disk (via custom install
of Excel only) and it did no good.
Can anyone help me find my formual bar?
All I can think of is do you have the full screen mode active.
Check to see if View > Full Screen has been...scrollbar with huge range
I have a sheet where I use at least the first
300 rows, but the scrollbar span an area of something
around 60000 rows. I tried the following command:
Worksheets(1).ScrollArea = "A1:IV500"
but it does not shrink the scrollbar to a reasonable
range. What it does is
1. Only the range A1:IV300 can be modified!
2. The scrollarea span an area of A1:IV60000 altought
there are no data in the cells!
Hope somebody can help me!
Thanks in advance!
Select all the rows following your last data rowTo do this, select the first
unused row (click in the r...Excel
:rolleyes: I have two Excel columns (over 6000 rows) - one ha
numerical values and the other has either 0's or 1's. What I need is
way to select a range of cells in the first column (col A) depending o
when the second column (col B) switches from value 0 to 1. So let'
say that the first row of col B contains a 1 (which it always will)
and the next 6 rows are all 0's, and the 8th row is a 1. Then th
range I need to select in col A is from A1 thru A7. Next, I need t
calculate the median of A1 thru A7 and paste that in a third column.
My next range selected will then begin w...Variable in Range
This is my code:
Dim intRow As Integer
intRow = InputBox(Prompt:="Enter Age", Title:="Age")
Application.Worksheets("Mold Flg Thk").Rows("10:19").Insert
I would like to use the intRow variable in the .Rows range. But,
Application.Worksheets("Mold Flg Thk").Rows("10:intRow").Insert
doesn't work and neither do a few other variations.
Can the range be defined using the intRow variable?
Application.Worksheets("Mold Flg Thk").Rows("10:" & introw).Insert
&g...Formula for adjacent row sums?
Hello. I have a speadsheet that has numerous rows and columns of amounts
that need to have an automatic sum entered in the middle of each row.
Inv Date Part Labor Tax Total Other Paid (auto sum) other other
23 1/4 nut 27. 2. 29. -3. 22. ( )
Message posted from http://www.ExcelForum.com/
...Please help on formula that will not work (If-And formula)
Excel 2007. I cannot seem to get this formula to work:
I am trying to do an if statement to enter a "Y" or blank based on my AND
parameter. The first AND logical test is checking to see if the vlookup
number is greater than the number in cell C28 which is a number 4. This part
of the formula seems to work as I tested it by itself. It is the second
...Formulas do not update
Excel 2003 - Formulas on one worksheet (pMon) referring to another worksheet
(pCSRs) all-of-a-sudden stopped updating when the information on the second
worksheet changed. These are simple formulas without calculation, such as
=pCSRs!F92. Calculation is set to automatic, and even when I press F9 they
This has worked for a long time, but I have recently been making changes to
other worksheets by importing data from another similar workbook. However
none of the problem formulas involved have a reference to the other workbook
(nothing like ='[Workbook2]pC...VBA or Formula Needed ????
The vlookup table automatically updates when a the next destignate
AF:AH orange background is updated.
I now need help with a formula or VBA that will identify from thi
1. which digit (0-9) in the EC column has the highest value in the E
place that digit in the next orange row's W cell (for example W436).
2. which digit (0-9) in the EC column has the highest value in the E
place that digit in the next orange row's X cell (for example X436).
3. which digit (0-9) in the EC column has the highest value in the E
place that digit in...