tranposing every third cell in a row to a column
How do you transpose every third cell in a row of data to a column on a
separate worksheet. There should be no empty cells in the column of the
separate worksheet that this information is being copied to.
Say the data was in Row 1.
This will go from A to D to G ... etc.
Enter this formula anywhere, and copy down:
Please keep all correspondence within the Group, so all may benefit!
I am using Outlook 2007 sp2 A large number of email notifications have
appeared in my inbox - they have no From or Subject and no content they only
show a reveived date and a size nearly all at 326b -- I asssume that they are
some form of spam - how can I get these items to go straight to my junk mail
box other than identifying each one adding to my blocked sender list
If these messages are not caught by the Junk E-mail filter already, try
increasing the scanning level.
If that doesn't work either, create a rule with the action to move all
messages to the Junk Email folder...Adding items in a column if value in a second column is X.
This one is getting the better of me, even though I know I should be
able to figure it out myself!:
I have two columns of data: B and C.
I have one column of ID#: A
I would like to subtract C from B in all rows where ID# is X and
display the sum of these values in a single cell. I would then like to
do the same for all rows where ID# is Y.
Many thanks for your time,
Adjust column ranges to suit, but you can't have a complete column
...subtract amounts from different columns?
I have 3 columns with, income, expenditure & balance. How can I get the
balance to auto fill using the other two columns?
Also want the total at the bottom of the sheet for each column, after
deductions have been made.
Take a look at one of Microsoft's hundereds of templates
offered on-line (freely downloadable):
Look for "checkbook register".
>I have 3 columns with, income, expenditure & balance. How
can I get the
>balance to auto fill us...Can I use variables for workheet name references in Excel functions?
I'm working with Excel 2003 and trying to look for a way to create some sort
of dynamic references in functions between worksheets in the same workbook.
Let me try to explain -
Say I have a workbook with 12 different worksheets with the names -
January, February, March, Etc...
On a new Worksheet I want to write a function that will use a cell as a
reference for the worksheet to use in the function.
I'm going to have a function in that cell that will return a name (In this
example - Cell A2)
In Another cell, I want to write a function that will retrieve the highest
value from a ce...Copy An Apointment
Here is some script I worked hard on with Microsoft. Since it works real
well, I thought I would share. It could easily be recycled for other windows.
This is a method to copy an appointment:
In the ISV.Config File Add:
<ToolBar ValidForCreate="1" ValidForUpdate="1">
<Button Title="" ToolTip="Copy this appointment to a new apppointment"
var date_now = new Date;
//Convert...How to save formulas while copying worksheet?
I'm trying to copy a worksheet i created as a template as a new
worksheet in another workbook without having my formulas change in the
I have tried all the pasting options and all the formulas get change
from ex.: =Material!A4 to ='[Template.xls]Material'!A4
In other words, reference to the template file are added to the
formulas but i don't want that... i just want the copy the original
formulas as they appear in the template.
Any way of doing this or will i have to copy and paste and then edit
all the affected formulas to remove any reference to the template
file?...BLANK FORM BASED ON A QUERY
I Have Created a Dialog Form With a Button That Opens Another form,
the Form That Gets Opened By The Dialog form is Based On a Query, and
The Creteria Is Set To A value Inputed on the Dialog Form,
Which Works Great When I Have Results Turned Up,
If No Results The Form is Blank,
Is Their A Code i can put in the CLick Event of the Dialog form that
Opens the form, if there are no Results Wont Allow me to Open the
form. and a Message..
Ive Tried The Following
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records&qu...Sum cells in columns based on condition
I would like to multiply and add two columns together using an
appropriate function - best if i could avoid anything too complicated
like vba :-P
The criteria for this is to multiply and add rows in the columns
following the A's
Col1 ... Col5
So the sum should read 1x2000+2x2000 = 6000
(Col2 to Col4 also contain values as Col1.)
I'm not sure how to do this...
- I can't put the A's in any other place other than above the numbers,
so the sum product doesn't work
- I've put in a conditional format to colour the ce...better way to copy data from an embedded html box
i have some fields from a browser window pasted into excel. the become
shapes when pasted into excel. i give the shapes names starting with box 1
to box ?, depending on what's been pasted into excel.
when one shape is selected, it shows the following the the formula bar:
right now i have some code that will copy the data from the field:
With ws.Shapes("Box " & i)
SendKeys "^A", True
SendKeys "^C", True
this wor...how to CHANGE THE name "rajeev Chhajer" to "Chhajer rajeev"
I want to change the name and title placements in a cell/ worksheet/excel
file. For ex. the content of the cell is
and i wanna change to
Pl suggest for the same, if u know.
with your name in A1, use
=MID(A1,FIND(" ",A1)+1,99)&" "&LEFT(A1,FIND(" ",A1)-1)
'The Code Cage - Microsoft Office Help - Microsoft Office Discussion'
Rajeev Chhajer;671235 Wrote:
I want to change the name and title placements in a cell/
works...Getting & Using Sheet Names or Index in VBA code
How do you get each Sheet Name in a Workbook so you can use it in code? I
know that each Sheet, regardless of Name has an Index of 1 through n
beginning with the leftmost Sheet but I don't know how to use that
I have one reference book, Excel 2003 VBA, that says there is a Worksheet
Property 'Name' that returns a String and in the Description of that Property
it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell
me how to use that in code.
I don't really care whether I use the name of each Sheet or the Index but if
you c...Column Headings
Firstly, Happy New Year to you all.
In my Outlook 2003 IN BOX I have lost the FROM column.
Can anybody tell me how to get it back?
Right-click any of the remaining column headers, select "field chooser,"
find the "from" field, and drag it to the row of column headers.
And a Happy New Year to you,
> Firstly, Happy New Year to you all.
> In my Outlook 2003 IN BOX I have lost the FROM column.
> Can anybody tell me how to get it back?
...copying formulas from worksheet to worksheet
I need to copy a row of cells from one worksheet to a column of another
worksheet. I want it to reference the cells listed in the row of one
worksheet to the column of the other worksheet. I found out how to do the
reverse - =offset('worksheet name'!$a$1,0,row(1:1)-1) - but it doesn't work
for copying rows to columns? Can you help?
Think the way that your Q was phrased,
an adaptation of the formula you posted should work
Assume you have in Sheet1,in B2:E2,
the numbers: 1,2,3,4
Put in the start-cell, say B2? :
=OFFSET(Sheet1!$B$2,,ROWS($A$1:A1)-1)...To set the whole row in a particular colo. (based on some conditio
I was to set a particular color to a row based on some condition.
ForEx: if the value of a cell E5 is 'closed', then
set that entire row to 'grey' color.
How do I do that?
Use conditional formatting.
Select all the rows (let's assume it is E-M in this case).
Go to CF.
Change Condition 1 to Formula Is
Add a formula of =$E5="closed"
Click Format button and format as desired
(remove nothere from the email address if mailing direct)
"Venkatesh V" <VenkateshV@discussions.microsoft.com> wrote in messag...count how many different numbers in the column and return to the n
I have a sheet
the thing I would like to do is
1 total(amount part is easy, I can use sumif())
the item part is hard, I tried if(countif()>0,item #), but I have 7 items in
total, if I have only 3 items in the colume, I will have 4 empty rows.
A PivotTable would work very nicely here because it would automatically
condense/expand to include the number of different items you need, as well as
summ...Create a column upon insertion or overwriting of data.
What should I read up on, where to look, what is the function or feature
called, to learn how Excel spreadsheet be configured so that upon insertion
or overwriting data in a cell, a new cell will be populated with the results
that display relative difference and a percentage of change ?
You should look at the Worksheet_Change event.
If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:
For information on event ...How to get notified when user inserts cells, rows, or columns in W
I'm making a client to Excel that keeps references to cells on worksheets.
These references must be updated if the user inserts cells to the left or
above the referenced cells. I have not been able to find this information,
neither in the Range that accompanies the Change notification nor in any of
the Worksheet, Workbook, or Application properties. Can anybody help me?
Using the Worksheet_Change event, you could probably evaluate the row and
column values of the target cell against the criteria cell:
If Target <> (Reference Cell) Then
If Target.Row <=( Refere...automatically copying data from one sheet to another, depending on which drop-down code is allocated
I have a sheet where I list each item of my business outgoings. Column C
lists the amount spent. In Column D I allocate a code from a drop-down
list, to allocate that item of expenditure against a particular cost centre.
I have thirty cost centres. So once the code is allocated, I can filter the
list to see all items for any particular cost centre.
But what I really want is this: I have created a separate sheet in the
workbook, with all the cost centres as column headings. I'd like each
amount to be automatically copied to the cost centre sheet, and placed in
the correct column,...number of columns in a customized Form
We need some times more than 3 columns in a row on a customized form, is
this possible? has any one tried this out? any hint is highly appreciated.
not possible in the standard forms and not heard of any solution to increase
Microsoft CRM MVP
"Max.f FirstCity" <firstname.lastname@example.org> wrote in message
> We need some times more than 3 columns in a row on a customized form, is
> this possible? has any ...Hide pg1 row1 or suppress pg1 header?
I am running Excel 2002 and need to either hide the first row of m
spreadsheet on the print out (it contains column headers) or I need t
suppress the header on only the first page of my print or perhaps ther
is a way to show the headers in the "normal view" of the spreadshee
while in edit mode? Can someone please assist me
Message posted from http://www.ExcelForum.com
...dates change when I copy and paste
I have a report that has columns for Day of week and a column for th
date(10/15/04) but when I copy and paste to another excel worksheet th
date changes from ex: 10/15/04 to 10/13/08. Any ideas why this woul
happen. It's a pretty lengthy report and I hate to always have t
change each date. Help. :
Nesia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1534
View this thread: http://www.excelforum.com/showthread.php?threadid=26965
both workbooks have different date setting (...Internet header question
Does anyone know of any tool that could be ran against an outlook
folder full of internet mail that would extract the SMTP address sent
from for all of them and export it to any format?
I'm going through spam from various users, and between the firewall
and the antivirus software on my exchange server I want to add the
IP's to a block list. But it's a pain opening them one at a time,
getting to options, copying the IP, and pasting it into an excel
Thanks for any suggestions.
JDTHREE [MVP] <email@example.com> wrote:
> I'm going th...NAME BOX
How do I delete a cell reference that is in my mame box??
one way: goto 'Insert - Name - Define' and delete the name in the
> How do I delete a cell reference that is in my mame box??
Go to the Insert menu, choose Name, the Define. In that dialog,
select the name and click Delete.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"FLKulchar" <firstname.lastname@example.org> wrote...sum using or (if Blank cell in Column)
My Basic problem seems to be that i cannot run a variable against a blank cell.
My set up as below:
Item 50cl 70cl 1.5cl per 25ml (< answer i'm looking for)
Now individually i can work out these items. Rum would be 0.62 in the 25ml
column... but I cannot find how to create each sum in the total box, so when
i leave two of them blank it understand to only work out the one answer.
It must look something like this? Right?
Item 50cl 70cl 1.5cl per 25ml
Rum £17.50 =s...