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

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

- Column List Sorting Locking 2 Columns Together
- Pivot table with added columns for evaluating results
- How do I add different ranges in a column together?
- Freeze first row & column together?
- How do I merge to Excel Columns together?
- Can I lock 2 columns together so that the 2 cells in a row are joined?
- How to group 2 columns together
- Keeping two columns together when sorting with a function?
- Adding two columns together
- Adding three columns together
- Adding numbers in one columns together
- Macro concatenating columns together, exceptions with duplicates, delete duplicates
- Index row, return dollar amount adding amount together
- Adding up Columns with "X"'s
- Adjust Formula Range Reference As Columns/Rows Added
- Automatically Adjusting To Added Columns
- Cell Formulas Change Appropriately if Columns/Rows are Added or Removed
- Automatically update formula when a new column is added
- Adding column with description based on preset codes
- How ensure cell formulas reference appropriate cells after adding columns
- Adding columns to table
- Update formulae when adding columns to another worksheet
- Vlookup - effect of adding columns
- Adding variable number of columns

Thank you

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?

need to total. Rather than adidng one cell at a time. How do I add multiple

ranges within the column together?

Thank you

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

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!

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

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

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!

How would i do this quickly?

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

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

Lauren

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

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!

Is this possible?

Thanks!!

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 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.

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.

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!!!

Won't adding a column in the middle of the table screw up these references?

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)

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

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.

No luck finding an answer? You could always try Google.