Adding Columns Together

Hi,

I know the basics of excel but not much more than that. I need to create a spreadsheet that will calculate the cost of items i wish to sell.

I need It to add together the item cost, gift cost and give a total. Then add the total to the postage cost and add VAT. So that I can then see the total amount that the item is costing me to work out my sale price.

Im sure this is a very basic thing to do but not to someone who doesnt know alot about excel!

You help is much appreciated.
Joanne


This should be a simple question for those who have the knowledge. I am making a 2 column excel page, the first column will have an authors name and the second one will have the book name. I need to lock these two columns together so that author name and book name always stay together (side by side) on the sort command. I need to be able to sort by author or book title and I realize that it gives you the choice to expand the selection, but I can't trust that the others (kids) will realize the importance of doing so. This is going to be a very large list with hyperlinks and I can't afford to chance whether someone else will select the correct command. So a long story short. I want to build a list that can be sorted by author name or book name and be sure that the correct author will always be beside the correct book, but that are able to be independantly sorted. How can I do this.

Thank you

I have added an extra column to a pivot table to analyze the results. The
pivot table is set up with a page filter for separate users. Each user can
select their data to review without having to look at the entire data set
which is 240 pages. I have used if statements so that the formula rin the
added column runs only if there is data. However if the user selects page
preview they still think their report is 240 pages long because of the
formulas in the added column. Is there any way to avoid this?

I have a large column of numbers and need to select random numbers that I
need to total. Rather than adidng one cell at a time. How do I add multiple
ranges within the column together?

Thank you

Can one freeze the first row and the first column together in an excel sheet??

Also can someone freeze a random row or column in a sheet?

Hi,

This is my first post and I glad to be here as I have been looking for a forum like this for sometime now.

Well anyway, here goes.

I have made a excel worksheet that automatically imports a file, sort the columns and deletes unnecessary columns that is not needed.

The only problem that I have when I import a txt file into excel is that it has a main category and a sub category column. Well I would like to convert them two columns to a single column where the information from the first column would be split from the second column using a " > ".

I know how to do this using a formula in Excel, but after I have merged the two columns together to make one, I then want to delete the other two columns. But when I do that, my merged columns information then disappears, which I don't want.

Is there anyway of changing the merged columns information so that the information contained within it changes from a formula to actual text just as if you typed it in your self.

I hope you can help!

Thanks!

Hello,

I want to use Excel to compile a list of my DVDs.

This requires just two columns. The first column will be used to number the
DVD. The second column will be used for the actual name of the DVD.

However, I will want to sort the list in two ways: alphabetically and
numerically.

So the question is: Can I lock these two columns together so that when I
want to see my list alphabetically, the first column that has the number of
the DVD will automatically adjust itself to stay with the DVD.

I hope this makes sense and thank you for your attention and any advice you
can give me.

David

I have 2 columns with numbers in each. I need to group the 2 columns together as the numbers correspond. It looks a little like this:
5 232
7 305
5 232
141 303
7 305
141 303
What is the simplest way to group the columns to look like this?:
5 232
5 232
7 305
7 305
141 303
141 303

I have an inventory spreadsheet with data like this:

Description Count
Hammer 1
Anvil 5
Pliers 2

I want to create two new sheets.
One with the data in order by the Count
and one with the data in order by the Description.
Like this:

Anvil 5
Pliers 2
Hammer 1

and the other:

Anvil 5
Hammer 1
Pliers 2

I think I know how I can get the Count values into the
correct order by using functions Large and Row/Rows.
And I can do the same thing with the description.
But how do I keep the two columns together?
I think I need to have the Description cell and the
Count cell managed as a pair. But I only want to
evaluate one of the columns at a time.

Well, thanks for any advice!

Hello, I have two separate columns, I want to add the numbers in each row together, to form a new column of numbers.

How would i do this quickly?

Explain to me in easy terms, i know very little about excel. thanks.

Howdy,

As the title states I am a little bit of an excel noob, at least with anything more than adding and subtracting data.

I think it is easier if I just explain in words what I am trying to do.

Okay so I have three columns:

Column 1 Column 2 Column 3

100 50 50
150 50 100
100 20 80

At the moment column 1 subtracts from column 2 and the answer is in column 3.

The way I did this is =a=*1-*2 formula for every cell in column 3.

Great it works.

However what i would like to do is that whatever the answer is in column 3 to be added to the next number in column 1. it is for a day by day budget. below is the same numbers with different headings:

Allowance Spent Remaining Balance

Day 1 100 50 50
Day 2 150 50 100
Day 3 100 20 80

So I want the answer in (remaining balance column) to add to the next day's (allowance column)

So essentially the table would now look like:

Allowance Spent Remaining Balance

Day 1 100 50 50
Day 2 200 50 150
Day 3 250 20 230

Can anyone help with the formula to make this happen with maybe a little instruction to help an out.

You will be spreading the karma to a good person

:-)

Thanks in advance

This is probably very simple but I have a bunch of numbers in column B that I need to add all together...how do I do this? Thank you in advance!

Lauren

Hi all
I have a code that concatenates together information from bunch of columns. I need concatenate all this information together as there is a another report where I need to do match and index ( or simple vlookup) and have a nice overview to see where we are with a deliveries. Concatenation results should go to column X. This is what I have so far:
Sub prepare_manifest()
Dim a, i As Long: Application.ScreenUpdating = False
With Sheets("before"): a = .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp).Offset(, 22)): End With
For i = 1 To UBound(a)
    a(i, 1) = "PO " & a(i, 8) & "; Item " & a(i, 19) & "; Part " & a(i, 4)
& "; Latest delivery date and time " & Format(a(i, 14), "dd/mm/yyyy") & ", " &
a(i, 10) & "; Manifests " & a(i, 1) & "; Delivery notes " & a(i, 2) & "; Total
quantity x" & a(i, 5):
Next: Sheets("before").Cells(Rows.Count, "Y").End(xlUp).Offset(1, -1).Resize(UBound(a), 1) = a
    Columns(14).NumberFormat = "dd/mm/yyyy"
    Columns(23).NumberFormat = "0"
End Sub

This code combines some data together in a way that after I do a vlookup to another report I can read and understand whats going on. So the combined information would look like this example:
PO 1800786639; Item 10; Part L57952013200; Latest delivery date and time 04/01/2011, 11.30AM TRANSPORT; Manifests 10072; Delivery notes 81630937; Total quantity x1

Now there are some rows that as per column W are duplicates. I need to combine these lines together into a one row, delete all duplicates and only keep the bottom line. This is where I struggle.

For the first 5 parameters (PO, Item, Part, Last delivery date and time) I would like the macro to keep the values of the bottom duplicate. With Manifest ID I would like the macro to combine together all Manifest ID-s that are unique. With Delivery notes it should combine all the delivery notes together. Theoretically (and in practically) there will always be only unique delivery notes ( haven't seen singe duplicate delivery note so far). With Total quantity it should sum up the duplicate lines i.e there are are three rows with qyantites 4, 2, 3 so the total sum would be x9.

Example line where rows have been added together:
PO 1800824763; Item 10; Part KNA12A3M30701A04; Latest delivery date and time 20/02/2011, 11.30AM TRANSPORT; Manifests 11232, 11250; Delivery notes 81649755, 81649754, 81649753, 81649750, 81649749, 81649746, 81649744; Total quantity x7

I have attached a xls where there are sheets "before" and "after" along with some decent comments. All the duplicate lines as per column W have been highlighted in red. After you have you run the macro, then sheet "before" should look identical with sheet "after".

Any help would be greatly appreciated.
Cheers
Rain

Hi all,
I am creating a spreadsheet to track expenses. I am assigning a unique number in a column for the different types of expenses. Each expense will have it's own "Total Amount" cell. So in the total cell I am looking to search the column for that particular number, and return the dollar amounts, while adding the amounts together for the total. This is what I have so far. Will anyone help?


I know this is way off. Will someone help? Thanks!

Hello All. This problem is probably best explained by looking at the attached sample. I have outlined several projects, along with several possible steps & the time it takes to do each step. If a step is needed in a certain project, I have put an "X" in that step's column. I would like Excel to automatically total the time taken to do the project by searching for the cells in that row that contains an "X", going up to the Time row to get the time taken to do each step, and adding those together.

Is this possible?

Thanks!!

Hi,

I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings.

So for example:
=(SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998

becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998

How do I stop this from happening?- it has me baffled!
Cheers
Sam

VBA – How To Automatically Adjust To Expanding Columns

Hello,

Need some magic with this...: D

Looking at the “Error” sheet, the "Hours" column total incorrectly. My code is really crude. Worse still, it doesn't automatically adjust for the 3 "Highlighted" names added to the table.

I don't know how to modify my code to sum the rows for the "Hours" column when the range expands horizontally.

What I am trying to achieve is shown on the "Expected" sheet. How could I make this work? The formulas take care of increasing rows. it is the columns that are driving me around the bend!!

Thanks in advance of any responses... Vokomana

P.S. In case you don’t want to run the application here is my code:


	VB:
	
 AllHours() 
    Application.ScreenUpdating = False 
     
     '//Clear any remnants of old table
    Rows("1:5").Select 
    Selection.ClearContents 
    Range("B1").Select 
     
     '//Department Names
    Range("B1") = "Dept" 
    Range("B1").Font.Bold = True 
    Range("B2") = "Clinic" 
    Range("B3") = "Lab" 
    Range("B4") = "Physio" 
    Range("B5") = "Total" 
    Range("B5").Font.Bold = True 
     
     '//Names
    Range("B8").Select 
    Range(Selection, Selection.End(xlToRight)).Copy 
    Range("C1").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
     '//Set Totals For 1st Column
    Range("C2").Formula = "=SUMIF($A:$A,""Clinic"",B:B)" 
    Range("C3").Formula = "=SUMIF($A:$A,""Lab"",B:B)" 
    Range("C4").Formula = "=SUMIF($A:$A,""Physio"",B:B)" 
    Range("C5").Formula = "=SUMIF($A:$A,""*"",B:B)" 
     
     '//Copy & Paste Formulas Across Columns
    Range("C1").Select 
    Range(Selection, Selection.End(xlDown)).Copy 
    Range(Selection, Selection.End(xlToRight)).Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
     '//Fix Names - Wiped out by Copy & Paste above
    Range("B8").Select 
    Range(Selection, Selection.End(xlToRight)).Copy 
    Range("C1").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
     '//Colour Code Bottom Row
    Range("B5").Select 
    Range(Selection, Selection.End(xlToRight)).Interior.ColorIndex = 37 
     
     '//Set Up "Hours" Column
    Range("B1").Select 
    ActiveCell.Offset(0, 1).Select 
    Do While Not IsEmpty(ActiveCell) 
        ActiveCell.Offset(0, 1).Select 
    Loop 
    ActiveCell = "Hours" 
    ActiveCell.Font.Bold = True 
     
     '//Sum "Hours" Column
    ActiveCell.Offset(1, 0).Select 
    Do While Not IsEmpty(ActiveCell) 
        ActiveCell.Offset(1, 0).Select 
    Loop 
    ActiveCell.Formula = "=SUM(C2:E2)" 
    Range("F2").Select 
    Selection.AutoFill Destination:=Range("F2:F5") 
     
     '//Clean Up and Colour Code "Hours" Column
    Columns("F:F").EntireColumn.AutoFit 
    Range("F1").HorizontalAlignment = xlCenter 
    Range("F1:F5").Interior.ColorIndex = 37 
     
    ‘Borders 
     
    Range("B1").Select 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I'm sure this is an easy one but I don't know how to do it.

Can anyone tell me how to make sure the formulas in my cells automatically adjust if I add or remove a column/row. Right now my cell formulas in the columns after the added column adjust fine...but the cells with formulas before the added column don't adjust accordingly even if they are pulling from columns that have been moved up due to a column being added so I end up having to go backwards and manually adjusting those formulas.

Thanks in advance for your help!

I would like to know if there is a way to automatically (through a Macro perhaps) to edit a formula so that it includes a new, added column. This is what I have:

I have the data in Columns G, H and I, each column representing a new date. In Columns L and M, I have formulas that use the data from G, H, and I. Now I want to add a new column (a new date) and I want that the formulas in L and M automatically pick up the data in the new column.

Since the formulas in L and M are complicated, see below, it would be time consuming to do this manually.

Column M formula:

=IF(K7="Y";SUM(IF($G$4-S7>=$D$3;G7+$G$6*E7);IF($I$4-S7>=$D$3;I7+$I$6*E7);IF($J$4-S7>=$D$3;J7+$J$6*E7));IF(K7="N";SUM(IF($G$4-R7-S7>=$D$3;G7+$G$6*E7);IF($I$4-R7-S7>=$D$3;I7+$I$6*E7);IF($J$4-R7-S7>=$D$3;J7+$J$6*E7))))

Here is a screen shot of the layout of the worksheet:
untitled.jpg

Thanks for the help.

I am looking for some help with a VBA Code. It needs to accomplish the following.

First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it.
In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.

Next I need labels added to the tops of the rows
Next I need the description columns in C to be copied to column I
Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data.

Attached is sample data.

Greetings.

Here is my dilemma. I have a workbook with multiple worksheets containing various formulas that reference the very first page of the workbook. This first worksheet contains the raw data, and subsequent sheets contain charts, tables, etc. that reference the raw data.

A variety of macros are run in order to create this entire document, including formatting the raw data. One of these macros creates buffer columns (i.e., inserting two blank columns), between various sets of data.

When the macros are run, the cell references in the worksheets (i.e., those worksheets that reference the raw data page) now reference the wrong cells. That is, if a cell originally referenced =RawData!E1, after the macro which added a blank column between D and E, it would now reference =RawData!F1.

I want the cells to continue to reference =RawData!E1 after the macro. Can anyone help? I've attached a test worksheet containing a macro that creates two blank columns for testing. On the test, cells E1 and F1 on the STM sheet should read "1" and "1" after the macro, instead of "10" and "10."

I am using Excel 2010 with Windows 7.

Thanks!!!

I need to add 1 or 2 columns to a table... but on another sheet I reference that table with quite a few vlookups that return data based on the column number to the right of the lookup column....
Won't adding a column in the middle of the table screw up these references?

Hi there,

I have a worksheet (Actions) with a number of columns (lets say C and D). these columns use a vlookup formula of the type:
VLOOKUP(B5,Risks!$B$8:$CZ$501,18,FALSE) and
VLOOKUP(B5,Risks!$B$8:$CZ$501,19,FALSE)

where column B contains a manually entered unique reference to data held on the worksheet (Risks)

so far all works, Actions column C contains the data from the 18th column in the range B8:CZ501 on Risks worksheet, and column D the data from the 19th column.

Question:

I now need to add some coumns in between columns B and CZ on the worksheet Risks, but would like the column references in the formulae to update. Excel updates references on the Risks worksheet but not on the Actions worksheet. IE, by adding one column to the Risks worksheet I would need the formula in Actions to go from:

VLOOKUP(B5,Risks!$B$8:$CZ$501,19,FALSE)

TO

VLOOKUP(B5,Risks!$B$8:$CZ$501,20,FALSE)

Hi,
If in a formula =vlookup(a1,D:F,3,false) someone later adds a column between
D & F, the result will be incorrect. Is there anyway to make the formula
resillient to columns being added to or subtracted from.

Thanks for looking.

Paul

Hi

Using Excel 2003 I want to create a formula which will add a varing number
of columns together depending on the entry in a particular cell, for a
budgeting spreadsheet.

For example if I have a year to date total in the 13th column and I was to
type January in cell A1 I would want the value in the first column to be
displayed in the year to date total (column 13). If I put June in A1 I want
the sum of the first 6 columns to be displayed in the YTD total etc for an
entire year.

I was able to use a nested IF statement last year as there were only 3
months remaining when this job was required. However, my understanding is
that I can't have more than 7 nested IFs in versions prior to Excel 2007 so I
need an alternative approach.

Thanks for any assistance given.