Merged Cells: Format The Cell So That If There Isn't Two Lines Of Type In The Description It Remains Aligned With The Quantity And Price

Apr 22, 2006

I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price

View 2 Replies


ADVERTISEMENT

Lookup Description For Chosen Value In Table With Merged Cells

Sep 11, 2007

Is there a way to capture ALL the description in Column D whenever I change the code? This mean if I change the code to 546CC0004, the description will change
Currently I copy manual from this file to another file. I Have more than 100 code with different Description. a Sample is enclosed.

View 4 Replies View Related

Calculate Price Based On Quantity

Apr 21, 2009

I`m relatively inexperienced with Excel, searched for a few days and have not been able to figure out how to calcuate this value. I have multiple worksheets to work out financials. I have a separate worksheet with the pricing breakdown based on volume. 2 columns

0 - 99,999 0.05
100,000 - 499,999 0.04
500,000 - 999,999 0.03
1,000,000+ 0.03

On a separate worksheet I want to add I6+I7, depending on the value multiply it by the value in the second column. So if the total is 520,000 then multiply it by 0.03.

View 5 Replies View Related

Cost Price Vs Quantity Discount

Apr 7, 2008

I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +

View 9 Replies View Related

Quantity*price Returns An Error

Jul 15, 2008

I am using MS Query to join 3 worksheets together.

I need to calculate one field but it keeps returning an error.
the 2 fields are part of the same table calle "Trades"

The fields are : Quantity and USD Price

USD Price is calculated initially in the worksheet using the current exchange rate.
I can display each fields separately (no problem) but when I try to multply the 2: I get a syntax error.

Things i have tried:
I tried to multiply Quantity by 2 and it works. when I try to multiply USD Price by 2 I get the syntax error.

I also tried to copy the values (not the formula) into a new column called USD Price2 and:
- Quantity*USD Price2 : syntax error
- USD Price2*2: Syntax error

View 9 Replies View Related

Sum Quantity And Price Across Sheet Based On Items

Jun 18, 2013

I have an issue when I use the sumproduct formula, it only sum the quantity and price orderly which is not matched

I have attache the file for example : Sale monthly.xlsx

View 9 Replies View Related

Sum Up The Totals Of 'quantity X Unit Price' For Each Product

Nov 23, 2009

i am currently working on cost analysis of large projects.

I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product.

I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.

View 9 Replies View Related

Get And Average Price For Total Quantity Of Jacks?

Nov 23, 2013

I am trying to get and average price for a total quantity of jacks

Material Price row G cells 4 - 56
Material Labor Price row I cells 4 - 56
Jack Quantity row L cells 4 - 9

I had this =SUM(G4:56)+SUM(I4:56)/SUM(L4:9) just wont work?

View 5 Replies View Related

Lookup Price Per Unit Based On Quantity Sold

Nov 22, 2007

I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.

Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.

View 7 Replies View Related

Calculate Grand Total From Multiple Rows Of Quantity And Price / Unit

Jul 18, 2014

For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.

I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.

I looked and messed around with For loops, but I can't make it work.

View 1 Replies View Related

Merged Cells Lose Format

Mar 10, 2007

I am using the following code to copy a ws to a new wb. The source ws has a range of cells merged (necessary), but in the new wb these cells become "un-merged." Horizontal and vertical formatting remain intact.

I've run a macro to capture the code to "re-format" the range of cells, but am not sure how exactly where/how to apply this to the new wb.

What needs to be added to this?

Sub CopyTag()
Dim SaveMeAs As String
SaveMeAs = Sheets("NCP Tag").Range("E6").Text
Sheets("NCP Tag").Select
Sheets("NCP Tag").Copy
ActiveSheet.Cells.Copy
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook. SaveAs Filename:="C:Test" & "NCP" & SaveMeAs
End Sub

View 6 Replies View Related

Auto Format Some Merged Cells Within A Sheet Based On The Information In Them

Feb 22, 2010

I am trying to auto format some merged cells within a sheet based on the information in them. how to reference the information within theses cells?

View 5 Replies View Related

Format Individual Cells To Type In CAPS

Jul 29, 2009

how I can format individual cells to put text in caps. I have found how to format the whole worksheet, but I only want certain cells to do it.

View 9 Replies View Related

Extending Vlookup: Price List To Cover Up To 10,000 Lines

Feb 9, 2009

However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work

unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines

View 5 Replies View Related

Cells Aren't Properly Aligned

Aug 5, 2008

I have some columns of data that won't get perfectly aligned. The numbers in all the cells are right aligned but the numbers in some of the cells are a few pixels to the left compared to some other cells that appear to be properly right aligned.

I have attached a screenshot if my explanation isn't clear.

View 3 Replies View Related

Alignment Of Adjacent Cells After One Column Is Aligned?

Dec 31, 2013

Example of Cell Movment.xlsx

The following attachment should explain what I am trying to accomplish.

View 2 Replies View Related

Format Specific Lines Within A Cell

Jul 7, 2009

I am trying to write a macro that given a selected range of cells goes in each cell and bolds the first 2 lines within the cell. An example of this would be if the cell contained:.............

View 2 Replies View Related

Determine A Cell's Format And Type

Nov 9, 2008

How do I determine a cell's type (e.g., numeric or string or formula) and if numeric, the format (e.g., currency, general, scientific) including the number of decimal places.

Is there a function that returns the object's properties? Must I use isnumeric, isformula, and so on or is there one function that returns this information?

View 3 Replies View Related

Vba Give Format To A Cell- Specify A Type For It

Jul 27, 2007

Does anyone have any clue of a VBA function that gets a cell and Changes the type of it´s contense?

I have been using the Cstr function as in the next example but I dont get the result I want

hojaRES.Cells(i, 6) = CStr(hojaRES.Cells(i, 6))
If IsNumeric(hojaRES.Cells(i, 6)) Then
MsgBox "nothin changes :("
Exit For
End If
hojaRES.Cells(i, 6) = "0" & "34" & hojaRES.Cells(i, 6)

My problem is that i want to add 034 prefix to a telephone number, and as it treats the data as numeric; number 0 (before34) is deprecated

View 3 Replies View Related

Cell To Generate The Sum Of The QUANTITY Of Items From Multiple Cells

Mar 21, 2014

I'd like to generate a total sum based on the quantity from each item. So columns H3 - N3 should generate a total in P3 (skipped O) by the quantity entered in each cell. In addition I'd like the value of D3 to change the pricing in the formula. D3 has a drop down with 3 products, so selecting ANY of those products should cancel out the previous formula and replace it with another.

This is the sum without a value in D3
=SUM(H3*10.00)+(I3*8.00)+(J3*6.00)+(K3*18.00)+(L3*10.00)+(M3*8.00)+(N3*8.00)
IF D3 has a package selected from the drop down, I'd like this sum to generate instead.
=SUM(H3*8.00)+(I3*6.00)+(J3*4.00)+(K3*15.00)+(L3*8.00)+(M3*6.00)+(N3*6.00)

[code]....

View 3 Replies View Related

Format Cell To Show % As The Number Type-in

May 1, 2014

(3) examples when I type a number to a cell:
If I type a number "1", I want that cell to show 1.000% but not 100.000%.
If I type a number ".2", I want that cell to show 0.200% but not 20.000%.
If I type a number "25.5", I want that cell to show 0.255% but not 2550.000%.

I play around with the below custom format cells but they do not work.
_0.01*#.000%
_0.01*#,.000%
0.01*#,.000%

I could use two cells one for number, other cell has a % mark. But I rather to format a single cell if it is possible.

View 4 Replies View Related

Type A Number In A Blank Cell With The General Format

Aug 10, 2009

My excel recently the General number format so that when the general number fomat is used and I type a number in a blank cell with the general format, the number is always divided by 10.

E.g., I enter "102", the number is automatically improperly converted to 1.02.
However, if I enter "=102", the number is properly entered as 102.

How can I reset the General format to the original setting without this divide by 100 problem?

View 3 Replies View Related

Concatenate: The Cell Remains Empty

Apr 26, 2006

After I run this macro, the cell remains empty. Any thoughts.

'Vary1 is some cells name
'Vary2 is some cells name
Dim AdjustFactorAddress As String

Set adjustmentfactor = Worksheets("Sheet 2").Cells(4,5)
AdjustFactorAddress = adjustmentfactor.Address()
Selection.Offset(1,2).Formula = "= ROUND(Vary1*Vary2*R[2]C[4]*'Sheets 2!'" & AdjustmentFactorAddress & ", 2)"

View 4 Replies View Related

Unmerge Merged Cell And Put Value In All Cells

Nov 6, 2008

Is there any quick and easy way to unmerge merged cells and put the merged cell value in all the merged cells. For example, if I have A6:A15 merged together and its value is "FirstMerge" then I would like to unmerge all A6:A15 and then put "FirstMerge" in all the cell A6,A7,A8 etc. etc. upt oA15. I have lot of these merged cells in a sheet and I get this every month and makes any data processing very difficult. A macro or VBA code or any other solution will be fine.

View 4 Replies View Related

Split Un-merged Cell Into Two Cells?

Nov 8, 2013

I have many columns in my current spreadsheet but I have two columns that I want to split in to two project names.

Currently I have one column with: Project A/Project B And the second column with the total cost of Project A AND Project B combined

The thing is that I can easily just create a new line but that would mean all the data in the other Cells remain the same and the only two that will be different are the above two columns. I will be maintaining alot of rows so I was hoping I do not have to create two rows for every new item then to merge both rows in reach column every time I have to put in a new Item.

I am hoping to have for example the below

Name Comments Project Cost
-------------------------------------------------
Item 1 This item relates Project A $150
to upgrade of Project B $120
SAS system
----------------------------------------------------

Currently I have it as

Name Comments Project Cost
-------------------------------------------------
Item 1 This item relates Project A/B $270
to upgrade of
SAS system
---------------------------------------------------

View 2 Replies View Related

Discount Prices Based On Order Quantity And Package Quantity

Nov 23, 2009

I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:

Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6

Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................

View 5 Replies View Related

Merged And Unmerged Cells Based On Value In A Cell

Apr 6, 2013

I try to unmerge cells A1:A5 based on a value "unmerged" in cell G1.

Also if I select "merged" in cell G1 (data validation), then cells A1 to E1 will merge to a single cell A1.

View 2 Replies View Related

Retrieve The Number Of Merged Cells In A Cell

Aug 1, 2007

I would like to test how many cells have been merged in a given cell. I know about the MergeCells property which returns true if the cell is merged. But how do i retrieve the number of merged cells in that cell (supposing it is merged, of course).

View 3 Replies View Related

Excel 2010 :: Inserting Image To Cell / Merged Cells As Background Fixed To Cell Size?

Jan 18, 2014

Excel 2010. I need to place picture into one cell or one big merged cell, as a background fill. picture must resize to size of cell. must be fixed in, not in front. i still need write into that cell, so it needs to be really background.

View 2 Replies View Related

Count Cell Values In A Column And Merged Cells

Apr 10, 2012

I have 10 columns and 18 rows. Every 2nd row might contain a number. I need to sum those numbers, however, sometimes group of cells in a row can be merged and i value of a cell is then 0.

example:

A1 to D1 merged; value 6
A3 to B3 merged; value 3
B5 to E5 merged; value 2

=sum(A1,A3, A5) would return 9 - correct
=sum(B1,B3, B5) would return 2 - should be 11?
=sum(C1, C3, C5) would return 0 - should be 8?
=sum(D1,D3, D5) would return 0 - should be 8?
=sum(E1, E3, E5) would return 0 - should be 2?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved