How To Expand Beyond Row 1000 Without Correcting Each Cell
Mar 4, 2012
I have about 300 cells filled with different formula's like the ones written below.
=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$C$1:$C$1000=D21),('Book 1'!$W$1:$W$1000))
=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$D$1:$D$1000=I21),('Book 1'!$S$1:$S$1000))
Spreadsheet works great, but I've realized that I should have expanded beyond the 1st 1000 cells (example: $1000). One book 1, my data entry will extend beyond row 1000.
Is there a way I can replace all $1000's on the spreadsheet with say $3000 without having to correct each of the 300 cells separately?
View 5 Replies
ADVERTISEMENT
Sep 7, 2009
i have a figure of 1000 in cell a1. i want to divide it by a figure in cell b1 and have an answer in c1, or divide it by a figure in c1 and have an answer in b1.
in summary, cell a1 has i figure in it while cells b1 and c1 are empty until i enter a number into one or the other.
View 9 Replies
View Related
Dec 10, 2006
I'm looking up a value and would like to replace the #NA error with text "No Match".
=INDEX(Sales_Assignment_Query_WW0612.xls!$F$4:$F$35384,LOOKUP(10^307,CHOOSE({1},MATCH(M9,Sales_Assignment_Query_WW0612.x ls!$C$4:$C$35384,0))))
I was trying to use =IF(ISNA(entire_formula_above),"No Match",entire_formula_above) but can't get it to work or struggling with closing off brackets with number of arguments.
Another user in the newsgroup suggested this sample code but I've been unsuccessful in figuring out how to use correctly.
=LOOKUP(REPT("z",255),CHOOSE({1},"No Match",INDEX(D2:D10,LOOKUP(10^307,CHOOSE({1},MATCH(F2,A2:A10,0))))))
View 9 Replies
View Related
Jan 8, 2014
Got the roundup part which is ROUNDUP(F2,-3) but having hard time with the other part If the cell (F2) is 0 or below would like to show zero. I am attaching a spreedsheet so you can see what I'm trying to get at.
View 5 Replies
View Related
Apr 9, 2014
I’m running a report on the average hold time of telephone calls. The data I extract for the report exports like this:
3:09
:00
:40
3:48
2:18
:12
:30
1:10
I need to re-format the cells to show like this:
3:09
0:00
0:40
3:48
2:18
0:12
If I only had 15 or 20, I would change them manually. But, this report has about 200 rows. And, I have 7 different reports to run.
I’m lost when it comes to making macros and using Visual Basic. Although, I can follow instructions and copy/paste.
The code mentioned in this thread: [URL] ....
[Code].....
Doesn’t work with this particular format. It’s changing the data to this:
03:09:00
:00
:40
03:48:00
02:18:00
:12
:30
01:10:00
:43
:52
01:32:00
02:07:00
:14
:00
How can I change the VB code to give me what I want?
View 6 Replies
View Related
Dec 22, 2008
when I get the download into Excel, the formatting of everything has been screwed up. I need to get it fixed. I'll take each column one at a time.
Column B (starting in row 3) has dates in the format yy/mm/dd h:mm (e.g., 08/12/21 2:50 for Dec. 21, 2008, 2:50 AM). I need them in mm/dd/yy h:mm (yyyy would be fine, too). Simply requesting that formatting gets me (of course) 8/12/2021.
I have found that if I insert a "20" in the front of the cell, suddenly Excel "gets it" and correctly formats the cell, with the correct date. As this seemed the easiest way to attack the problem, I've been trying to find a way to do that, but have failed.
View 13 Replies
View Related
Mar 27, 2007
I have a formula in a cell '= SUMIF('616'!$B$1:$B$2000;'AP AMT DKK'!$B4;'616'!$D$1:$D$2000)'. But because there's nothing in sheet '616', the value in the cell returns '#VALUE!'. Is there a way to have the formula return e.g. '0' instead?
View 3 Replies
View Related
Feb 11, 2010
How can i format a column of cells so that if a number is less than 10 for eg that that particular cell is then multiplied by 1000.
View 9 Replies
View Related
Mar 3, 2008
Looking to expand grid in Excel 2003,
View 4 Replies
View Related
Jul 6, 2014
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
View 5 Replies
View Related
Jan 24, 2013
I would like to know if there is a way to expand the selection of cells from the current cell you are on to the very bottom cell available in the spreadsheet.
For example, I want to apply a formula from A12 - the very last A cell row in the spreadsheet.
View 1 Replies
View Related
May 15, 2008
I have a cell with data such as: a0001-0004, a12, C8AF7-8, b17, j35-40 and i want it to output in the same cell(basically write-over the data): a0001, a0002, a0003, a0004, a12, C8AF7,C8AF8, b17, j35, j36, j37, j38, j39, j40.
View 4 Replies
View Related
Mar 31, 2009
I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.
View 5 Replies
View Related
Apr 16, 2012
I have a sheet that copies text info (merged and centered cell)from a cell in another sheet (text alt+enter to create more line), the text wrap is on, however i have to manually expand the cell to see all of the text
View 2 Replies
View Related
Aug 20, 2006
When i click commandbutton1:
Copy Active cell (columnB), next cell (ColumnC), next cell (ColumnI), next cell (ColumnO) all of which exist in the same row.
I will then paste the values into another sheet in the next available row which i think i have mastered. the cells will be pasted next to each other i.e. instead of column B,C,I,O they will be pasted as column A,B,C,D.
My dilema is i am not sure if you can use multiple cell offsets to perform this or if it is better to use a loop.
View 4 Replies
View Related
Dec 27, 2006
I have some working code that makes a number of changes to an excel report using VBA. Each time I execute the code I am taken to a different point in the excel report (depending on user inputs). What I now need to do is to change the cell selection from a single cell to three cells.
For example, my code may result in cell B100 being the active cell. What I now want to do is change the selection from B100 to the range B100:B103. I would have thought I could write something simple like:
Sheets("Group").Range("ActiveCell:Offset(0, 2)").Select
I have tried every variation of the above but I can't seem to make it run. Where am I going wrong?
View 2 Replies
View Related
May 15, 2014
I want to create a bar chart so that the X axis scale is based the value in the reference cells. For example, a table created for running profile as follow:
The data table # of minutes2221 pace (MPH)4.06.08.04.0
The chart I need to produce. The data has 4 records, but the chat needs to show bars of total number of minutes. Picture1.png How do I do it?
View 2 Replies
View Related
Jun 13, 2008
I have a workbook containing two worksheets - Sheet 1 being my "data entry sheet" and Sheet 2 is a sort of summary report containing multiple named ranges (all grouped according to similar values in a cell i.e. all cells containing 1-1 is named GRP1, all cells containig 1-2 is named GRP2). Basically, my workbook looks like this:
Sheet 1
A B C D
1 Lea Female 1-1 Lea Wells
2 Leo Male 1-2 Leo Anderson
Sheet 2
A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1
4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2
Hence, when I enter the data as shown in Sheet 1, Sheet 2 should look like this:
A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1
3 Lea Wells Female 1-1
4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2
6 Leo Anderson Male 1-2
View 4 Replies
View Related
Jan 3, 2006
I have a spreadsheet with a master front page, which basically copies the contents of other cells in other sheets for it's own content. ie:-
the formula in cell E16 on the master sheet is ='Sheet 7'!$B$16 (Please note that cell B16 is actually a merged range of B16 to H16)
Everything works fine until such time as the orginal cell contains an amount of text which is deeper than the cell on the master page eg cell B16 on sheet 7 is 330 pixels deep while the cell E16 in the master sheet is only 16 pixels deep.
What I would like to happen is for row 16 in the master sheet to expand in depth by whatever is requried to automatically show the full content of B16 on sheet 7.
View 9 Replies
View Related
Aug 8, 2012
The following code expands a cell width to size 42 when it is clicked on, then minimises it to size 9.29 when clicked elsewhere. Just what I need.
For instance, this would work for column 4.
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(4).ColumnWidth = IIf(Target.Column = 4, 42, 9.29)
End Sub
However, I need this to cover the first 1000 columns. More if possible. I can easily copy this 1000 times, changing the column number to then look like this (First 10 shown for example)
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(4).ColumnWidth = IIf(Target.Column = 4, 42, 9.29)
Columns(5).ColumnWidth = IIf(Target.Column = 5, 42, 9.29)
Columns(6).ColumnWidth = IIf(Target.Column = 6, 42, 9.29)
[Code] .....
The problem is if I do this 1000 times i get a Compile error that the procedure is too large. Is it possible to make the code more compact to basically cover all columns from 4 onward?
View 6 Replies
View Related
Jun 10, 2009
A1 would be the cell that users would enter quantities of 500, 1000, 1500, 2000 etc and based upon what they entered-cell a2 would give a corresponding entry of 10,20, 30 etc.
View 4 Replies
View Related
Jun 22, 2006
how can i do the following equation?
40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ tO 3000$
100$ if the sum is greater than 3001$
C16 X C19 if sum is greater than 12000$
all should go in one cell
View 3 Replies
View Related
Apr 1, 2014
How to stop my 100% from changing to 1000% after it pastes?
Here's the pc of code that is performing the copy/paste from one sheet to another sheet within the same workbook..
Code:
With Sheets("BOM Worksheet").Range("C" & nr)
.Value = c.Offset(, -16).Value
.Font.FontStyle = "Bold"
.Font.Color = RGB(186, 85, 211)
End With
View 6 Replies
View Related
May 20, 2008
Based on the 1st example below, I have a file with 1000's of columns where every 3 columns has the same type of info....
View 9 Replies
View Related
Mar 17, 2009
I use a worksheet full of formulas to know what divisors a number have, but i need to see what numbers have no decimals in about 500-1000 cells. Is there a formula whitch computer can use to see if it shows a number or not (if a number have or dont have decimals)?
View 2 Replies
View Related
Oct 17, 2009
I am having a problem with vlookup. I have the formula working but would like to divide it by 1000 then multipy by 24. When I enter /1000*24 after the colon in the vlookup formula my answer is 0(zero). If I don't try to divide and multiply the answer is correct.
View 9 Replies
View Related
May 26, 2006
I made a thread similar to this earlier but was unable to get to a desired end result.
I am trying to make a formula that will count the number of cells that do NOT have a fill color AND have a value over 1000 AND are greater than 60 days old as of the last day of the previous month.
Attached is a file with sample data so you can better understand
I included 2 formulas in the file that I am already using. Just in case that information is needed/relevant.
View 4 Replies
View Related
Oct 30, 2008
about making my code a bit more useful.
I started with a basic code as below which basically looks at upto 1000 part ids in column A and goes to my database to find corresponding Product Codes from a table in the database call PART
View 14 Replies
View Related
Jun 5, 2014
How would I modify the below code to extract all of this data from multiple files? I have about a 1000 files that I need to extract all of the data from to manipulate in Excel.
Code:
Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in WordDim iRow As Long '
[Code] ...........
View 1 Replies
View Related
Aug 14, 2013
im trying to create a simple random sample from my data set of size n=100 and then i need to repeat that step a 1000 times to make a new data set that i can transfer over to stata. Everything I have seen on the internet involves generating random numbers which isnt what i want. I need to be able to generate a random sample.
Currently my excel spreadsheet consists of 4 columns with 200 entries in each column.
View 4 Replies
View Related
