Can I use "find / replace" to automatically "Insert Rows" in a do.
Have a long column of numbers
Every time I see the number 5, I need to insert two rows. Doing this by
hand is tedious. Can you help me find a shortcut?
The IF function does not do this, right?
Not 25, 35, 5351, just plain old 5?
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
FirstRow = 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "C").Value = 5 Then
...Pivot Table not pulling all fields or data
Without getting too verbose, I have a 900 row by 26 column database I am
applying a Pivot Table to. The code seems to run fine, but it is not
returning some of the fields, and of course the related data. I have tried
reformatting the entire columns I am pulling from but no joy......however,
when I reformat an errant cell from General to TEXT, that row comes
in........and/or, if I change the value in one of the missing cells, it will
bring that row in. The code runs fine, and was mostly derived by recording
a macro in XL2k, but the data cannot be trusted becau...How do I automatiicaly redefinie Source data range for a chart?
I have A Chart Defined by Category (X) values in Collumn B and Y values in
Collumn C. Right now Both are defined by rows 4 to 114.
I would like to be able to have the chart automatically redefine itself so
if I put a value in C115 the chart would automaticaly set Y value range to be
C4:C115 eithout having to manually define chart. Is this possible? If so
how? I've tried OFFSET bu so far no luck.
If you turn your data cells into a List, or Table in xl2007, the chart will
The named range approach will work if you set up the names correctly.
See here for more info...Exchange data backup
I'm worried emails backup of the small office.
Does SBS2008 Exchange server has a direct backup capability of whole mail
How to backup every night Emails from server to external harddisk?
Does Exchange server needs to be stopped before backup?
SBS2008 Takes care of it.
look at the comments section
Russell Grover - SBITS.Biz [SBS-MVP]
MCP, MCPS, MCNPS, SBSC
Microsoft Certified Small Business Specialist
24hr SBS Remote Support - www.SBITS.Biz
Question or Seco...Data Save error
GP Ver 6.0. Recently I am facing an error with normal users unable to save
records, where as the system administrator is able to save the same
information. Please advice on this error, what has to be done.
I have disabled the windows user ID of the Database owner. Thinking of this
made Dynasa as the DBO.
Database is having enough free space available.
How big is the company database? Is it over 10GB?
"Matthews" <Matthews@discussions.microsoft.com> wrote in message
> GP Ver 6.0. Recently I...Returning Nil
I have a timesheet in excel, I would like the final column to work out the
overtime per day which is over 7 hours.
I've entered the formula: =I6-$H$4
I6 is the cell that has the total hours worked per day, in decimals. ie 7.75
H4 contains Number 7.
And this works ok.
BUT if the timesheet hasn't been filled the formula returns -7
What is the formula that returns nil or null if the row hasn't been filled?
BTW in I6 there is the formula: =HOUR(H8)+(INT((MINUTE(H8)+7)/15))/4
MS Excel MVP
"JB" <somehow@somewhere>...Getting a filename into VBA and putting it into cells
Basically what I have is a button that creates a new column that should be
full of linked values. When the user hits the button, he is asked to select a
file from his hard-drive; the code then takes the name of that file and
creates a number of links in the new column. So for example:
1) User hits button and selects the closed excel file
2) Excel creates the new column and populates it with formulas that link to
the file; in A1 it enters
='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters
='C:\Tempfiles\[Testsheet.xls]Budget'...Finding intersection point in rows and columns
I am trying to work a macro which will do the work for me (go figure).
Here is the easy example. I have a worksheet which has a 3 columns an
a bunch of rows: the column headings are " ", "total", "add-on"
the rows then consist of "mike","1,000","200"... then next ro
I would like a different worksheet to have a macro button that ca
take the data and put it into a new table and stuff.
I was thinking along the lines of creating labels on the top and left
and then referencing the ...PO Return Distribution Accounts
Upgraded to GP 9.0 two months ago; have recently noticed that when doing a PO
Return transaction (inventory w/credit) for an item that is in a Quantity
Type = Returned, that the system will use our Inventory Offset account as the
default "PURCH" distribution account; before the upgrade our Inventory
Returns account was used.
Is this an intended change or do we have a posting account setup issue? We
have not made any changes to Posting Accounts, Class ID Accounts, etc.
to my knowledge, this is the way it worked in prior versions (8, 7.5). It
the PO return transaction takes...Auto moving cells
We use Excel 2002 XP at work, and im trying to find out if its possible to
get excel to auto move a cell to the next column.
The spread sheet is setout with 5 columns and is about 20 rows down.
Each cell has a number in it (which is the number of each till in the
store). This gets change quite a few times each week and as they are all in
alphabetical order, if a number needs adding or removed, we have to keep
doing it buy insert shit cells down etc, then the problem is since its only
20 rows down 1 cell will move on to row 21.
Would it be possible to make excel auto maticall move this cell...cell range not changing when refreshing linked data- sumproduct fo
The cell range is not updating correctly to reflect the last row in the data
sheet that is being refreshed. 5878 is the correct number of rows and 5824
is not. I can find and replace in my formulas to correct the problem, but it
does it each time the data is refreshed. Any suggestions as to why?
I gave you the INDIRECT option y'day, but received no feedback from you in
that thread. Looks like you're more interested in knowing why? One simple
hunc...Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms,
modules, and logic) used to CRUD the data in the tables.
1) Is there any way to separate the database from the code into separate
files, eg. the db is mydata.mdb and the 'code is in a separate file, eg.
2) Say you have v1.0 of an app and the tables and attributes are populated
with data. You then go and build v2.0 of the app, adding tables and columns
to what already existed in v1.0, and perhaps moving some of the v1.0
attributes into different tables. What is the recommended way to
unload/reloa...Data Format: how can I re-format
The spreadsheet that was exported for me to use has the information in
It is customer ID name and billing info but it is like a long series of
I want to be able to have them listed under captions like company name,
contact, address, city, etc.. so I can use the data.
There is an empty row between each entry. Each entry is 3 or 4 rows.
There are about 500 records so i dont want to manually set them up.
Try tinkering around with these steps
Assuming your list is in col A,
data in A2 down
(If data starts in A1 down, insert a new row for the co...Return a value when Date x is between Date y and z
I have a table something like this:
A B C
1 From To Result
2 01/07/2009 01/07/2010 0-1
3 01/07/2010 01/07/2011 1-2
4 01/07/2011 01/07/2012 2-3
5 01/07/2012 01/07/2013 3-4
In another column (X) I have a list of Dates and I want return the
"result" from the row where the date lies between. So for example in
X3 I have 16/10/2011 and I can see this date is between the 2 dates in
row 4 in the table above so I want to return "2-3"
I've tried an array formula ...Cell Reference to Page Header
Is is possible to reference a cell in the page header? I
am hoping to have a sheet where demographic information
can be inputted. Name, Address, etc. Then have that
demographic information show in the header of every sheet
only possible with VBA, not with a cell reference.
> Is is possible to reference a cell in the page header? I
> am hoping to have a sheet where demographic information
> can be inputted. Name, Address, etc. Then have that
> demographic information show in the header of every sheet
&...Display comments as data #2
thanks a lot , this worked exactly as required, the comment go
converted to data
Now just one more thing . is the vice versa possible i.e if i have dat
in column A can it be shown as comment in column B.
the reason i need this is bcos i am shifting some data with thei
comments to MS access(Hence the need to convert omments to data)
processing them and again converting them to excel (the need to conver
data back to comments
sonika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...Count invalid data entries
I am using the following macro to identify invalid data entries in
cells with data validation applied.
' Application.CommandBars("Formula Auditing").Visible = True
MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10) &
" Configuration and Parts_TakeOff Sheets")
Is there a way to count the number of invalid data entries (red
circles) and write the number to a particular ce...Sorting and Header Row's
We work with a xls document on a daily basis, and the user's perform a
repeated sort function on that document multiple times a day. Appearently,
Excel used to save what you last sorted for and would also leave the "Header
Row" circle checked, after you closed the app... Now, neither is saved... So
I am just wondering if there is a way to have excel remember what was sorted
last, how it was sorted and to sort it with a header row with out having to
reset all those settings every time you do a sort...?
What version of Excel are you running? I have 2003 and it seems...ROWS Function
In Office Help, there is an example of the FOWS Function. In B2, the fumula is :
"=ROWS(C1:E4)" I believe this one is easy to understand. However, another fumula in cell A3 said:
The description about it is "Number of rows in the array constant". The result is "2". Could you please explain it in a easy way for me to understand? Thanks.
the example mentions 2 arrays. the rows function is supposed to retur
the number of rows in any given array.
C1 D1 E1
C2 D2 E2
C3 D3 E3
C4 D4 E4
this is a 4 x 3 matrix, 4...Getting row reference number
Kindly advise me how to get the row reference number of data which VLOOKUP
If you use the MATCH function, instead of the VLOOKUP function, it returns
the row_index of the searched range.
if you have A, B, C in cells A11:A13
and you use =MATCH("C",A11:A13,1)
that formula will return 3 (because A is the 3rd item in the list.
To get the actual row number, use:
(because you need to add in the number of rows above cell A11)
Does that help?
> Kindly ad...forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view.
All the tables, queries, and macros are there.
When I open up the database to add data, all the forms are there and can be
used. The data is stored. So, it seems that the forms are still there, but
just cannot be seen in design view.
The database, when I open it up in design view, says Access 2000 file object
even though I am using Access 2003 - could that be the problem?
You cannot open an entire database in Design View. Try opening it normally,
selecting a form, and choosing Design View from the V...can IF leave a cell EMPTY ??
I'm attempting some simple (to me at least) data analysis and am running
into trouble with zero values in the data array (region). These zero
values (null string: "", actually) represent non-existant data that I'd
like the chart to ignore, but as the array actually contains formulas
the chart engine does not see an empty cell.
Is it possible to use IF to cause a cell to appear Empty (ie,
IsBlank(cell) == True) ?
Something like: " = if (condition, reference, Empty) "
My raw data consists of date shipped and date recieved; I have created
an array (Mon, Tue...copying non adjancent cells
sorry Ms. debra dalagliesh I missed your reply to my query. I got it later
through google groups search. thank you very much for clarifying my doubts.
I am repeating your message for the sake of readers seeing only this
copy of Ms. Dalgleish's message
from: Debra Dalgleish (dsd@contexturesXSPAM.com)
Subject: Re: copying non adjacent cells
If you want to copy multiple ranges, the selections must match in rows
In each column, the same rows must be selected, or nothing is selected
In each row, the same columns must be selected, or nothing is selected
For example, you...Data validation doesn't work if contents is added by pasting
I've data validation set so that only the number 1 can be entered, but
even this cannot be entered if an adjacent cell is a certain value.
this works for direct entry, but not if the value is pasted in or added
using the "drag handle".
Is there any way to stop this happening as I just know somewones going
to try and fill all the cells by draging the values down.
You could disable "Allow cell drag and drop" in Tools | Options or...
copy the following code, right click the sheet tab and choose
'view code'. Paste the code into the modu...How to get Preview Pane in separate Data File in Outlook 2002
For back-up and portability purposes, we use separate Outlook Data Files.
(File, New, Outlook Data File, ....) Then we use the Organizer Wizard
to move files from the Inbox to the appropriate data file. All of this
The only thing we can't figure out is how to turn on the preview pane on one
of these files. It works fine on the standard InBox or any Personal Folder
that is in the main *.PST file.
We use the described method (View, Current View, Other Settings, check by
Preview Pane) and we don't get any error message. However, we also do NOT