Add Cells Based On Contents Of Cells In Another Column

Feb 15, 2008

I have a spreadsheet that has columns stating both status and then further to the right in the row, currency totals. There are only two status options, Stocked and On Order. I need to add the total currency amounts based on this other column's listing and it must change if status changes.

(Put simpler:
Column A lists Stocked or On Order and Column B is the currency tied up in that row.
I need to make totals for the cells in column B based on the status listed in Column A and it must change when Column A is changed. This should result in two totals, one for Stock Status Currency and one for On Order Curreny)

View 5 Replies


ADVERTISEMENT

Copying Merged Cells (3 Cells) Based On Contents Of Any Of 3 Cells To Right

May 29, 2014

I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:

Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer

[Code].....

View 14 Replies View Related

Contents Of Cells Appear Based On Another Cell

Dec 14, 2013

I am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.

View 3 Replies View Related

Copy Row Based On Contents Of 2 Different Cells.

Apr 5, 2009

I would like to do now would be to only copy the rows that also contain the word sold in column C. I guess that I could do this in two stages, first copying the rows based on the year then copy again based on the word sold in column C. It would be nicer to make a slight modification to my code and do it all in one step.

View 2 Replies View Related

Conditional Formatting Based On Another Cells Contents?

Sep 19, 2012

I'd like to format (colour fill) C1 when H1 contains the word "Bills"

I've tried to "use a formula to determine which cells to format" but it keeps using absolute references ($H$1).

Ideally I'd like to apply the "format" to the entire column C.

I did think I'd be able to use OFFSET but it's not working.

View 1 Replies View Related

Filter Out Duplicate Based On Contents In 2 Cells On Same Row

Aug 29, 2008

I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all.

Example:

DataABC DTom9730-003Company B100 Joe10242-001Company A200 Tom9730-003Company B300 Joe10242-001Company A400 Sam9730-003Company B100 Desired ResultTom9730-003Company B400 Joe10242-001Company A600 Sam9730-003Company B100

View 9 Replies View Related

Vba Cell Control Based On The Cells Contents

Sep 11, 2009

Is it possible to place formulas in vba to control cells based on the cells contents.

A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat

What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.#

if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.

View 10 Replies View Related

Go To Range Based On Cells Contents/Text

Sep 5, 2006

I am just learning VBA. I need to move to a cell location that is named in a range. As follows:

The cell "Sheet2!A1" has a formula that results in "Sheet1!B3". I would like my VBA code to read that cell and select Sheet1!B3. When using something like:

Application.GoTo Range("Sheet2!A1")

I end up of course at Sheet!2A1. I want to end up at Sheet1!B3 or wherever the cell reads at the time the code is read and operated on.

View 9 Replies View Related

Conditional Formatting - Based On Another Cells Contents

Apr 27, 2007

I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another.

What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.

View 8 Replies View Related

Calculate Contents Of Column Where There Are Empty Cells

Jan 16, 2013

In the Wheelchair 1, 2, 3 & 4 sheets in the attached workbook, I would like to do a simple calculation of the contents of column 'H'. Instead of it adding up to just a few £'s I'm getting the result of something like -£5880.00. I think I know whay is wrong, but I don't know the solution to it. Column 'H' populates when a date is put into Column 'E' So if cells if cells in Column 'E' are empty then column 'H' doesn't do the sum.

What I think I need is a bit of formula to put into column 'H' to tell it to ignore blank spaces and count what is there.

Wheelchair Hire-Sample New (1)(1) (2).xlsm‎

View 3 Replies View Related

Copy Only Part Of Cells Contents Based On Condition?

Jul 6, 2014

I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)

In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)

What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)

There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.

View 5 Replies View Related

Hyperlinking Multiple Cells Based On Another Cell Contents

Feb 23, 2012

I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.

The setup
*I have a folder called Zircon Plant
*That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders)
*The Register is located in 03. Documentation
*The PDFs i want to hyperlink to are located in 02. PDFs

What i want to do
*i have 2000 rows of drawing names
*the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21)
*the cells i want to contain hyperlinks are V21 - V2020

so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.

(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)

If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"

based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is

' Select PDF range
ActiveSheet.Range("u21").Select
' Promt PDF Hyperlinking
ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"

I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets

View 5 Replies View Related

Hide A Rows/columns Based On A Cells Contents

Jun 30, 2006

way to automatically hide a row or column based on a specified cell.

Or even something general like: Hide all rows with cells evaluating to #n/a.

View 9 Replies View Related

Give Cell A Unique ID Based On The Contents Of 2 Other Cells

Aug 11, 2008

I am trying to give a cell a unique ID based on the contents of 2 other cells.

Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.

View 9 Replies View Related

Merging Cells Contents Based On Unique Identifiers

Feb 22, 2010

Im having some problems writing a macro,

I have a spreadsheet with 2 columns. The first column is the index number (unique identifier) of the second columns data that will repeat itself as many rows as needed. Let me try to exemplify it:

Column A (index)Column B (text)
1 AAA
1 BBB
1 CCC
2 DDD
2 EEE
3 FFF
3 GGG
3 HHH
3 III

Im trying to write a macro that will merge the text of each identifier in the first row it appears. The result Im looking for is:

Column A (index)Column B (text)
1AAA
BBB
CCC
2DDD
EEE
3FFF
GGG
HHH
III

I already know how to make the merge, but I cant get the logic to make it go through each row and merge the contents, since the number or rows for each identifier varies.

View 9 Replies View Related

VBA To Loop Column And Copy Cells Contents To Another Cell

Dec 17, 2013

I have a script that copies data to files based on many cells contents but where I am having a problem is creating series numbers for each file.

File-01.txt
File-02.txt
..
File-100.txt

In my current code I copy files to folders by date and each folder I need series of files (Lab Testing series)

In column A1:A100 I have a series of numbers 01, 02, 03 ...100

Column B contains the Files to be saved
C:LAB2012Jan1file-01.txt
C:LAB2012Jan2file-01.txt
C:LAB2012Jan3file-01.txt
C:LAB2012Jan4file-01.txt
...
..
C:LAB2012Dec31file-01.txt

So this works fine

I now need the Script to do is to loop to Column A and select Cell 2 and do the File Copy again on the Next series

C:LAB2012Jan1file-02.txt
C:LAB2012Jan2file-02.txt
C:LAB2012Jan3file-02.txt
C:LAB2012Jan4file-02.txt
...
..
C:LAB2012Dec31file-02.txt

When Complete repeat until it reaches the end of column A

Since my Cells are populated by all the data in the workbook I thought at the end of my copy script I would take the next Cells data in A and put it in Cell H8 where all the constants are for the file names.

Column B is built using

=IF($C1="","",$I$1&$G$4&$D1&""&$I$8&$H$8)
=IF($C2="","",$I$1&$G$4&$D2&""&$I$8&$H$8)

View 1 Replies View Related

Clear Contents Of Cells Based On Data Validation Selection

Apr 13, 2012

I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.

The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.

This capability should be available for each row through 100.

View 3 Replies View Related

Merge Cells Based On Used Cells In Column 5

Jan 7, 2014

I have data written in Cells B5, C5, D5 etc that changes as you add more column data

I am trying to figure out how to merge cells starting from Cell B3 based on used cells in row B5 to end of column.

View 6 Replies View Related

Add Cells In One Column Based On Specific Cells In Another Column

Jun 10, 2008

I would like to write a macro to add cells in columns C,D,E,F,G & H based on the text (employee) entered in column B. For example I need it to add all the cells for "John S" found within column "C" and then total it at the bottom with a code JSSS (John S straight shop time) or JSOS (John S overtime shop) as per my example. The attatchment is a spreadsheet I use to total billable hours for our employees.

View 3 Replies View Related

Show Contents Of Vertical Cells In Horizontal Cells

Nov 21, 2008

I am referring to the post made by NBVC at http://www.excelforum.com/excel-gene...xcel-help.html, Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down

I have a similar case but there are no empty rows between lists, what would be the formula to get the same results? (The transposed address should appear next to each first line of each block).

Earlier, I posted at http://www.excelforum.com/excel-gene...-one-cell.html and I got brilliant answers, I also would like to get benefit of the above mentioned formula but in cases when there are no empty rows between lists.

View 4 Replies View Related

Clear Contents Of All Unlocked Cells (many Are Merged Cells

May 28, 2009

I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.

View 14 Replies View Related

Having Cells Change Their Contents According To What The Contents Of Other Cells Are

Mar 25, 2008

I have a column in my .XLS whose contents I want to change depending on what the contents of the cells of a different column are. However, I only want two cells from the same row to be dependent on each other. So, for example, if $A$1 reads "1" then $B$1 should read "apple" and if $A$2 reads "1" then $B$2 should read "apple"; if $A$12 reads "3" then $B$12 should read "pavement" etc.

View 11 Replies View Related

VBA To Clear Contents Of Cells And Keep Some Cells Locked

Jan 25, 2010

I have written this code to clear the contents of certain cells, lock the content of others and protect the sheet again it works on sheet1 but not on sheet 7. This is suppose to happen when the Print button on my sheet is clicked.

View 4 Replies View Related

Combine Contents Of Two Cells Depending On Contents Of Another?

Jan 14, 2013

I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!

I have provided a link to the example file below:

[URL]

View 4 Replies View Related

Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column

Oct 2, 2008

I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

View 2 Replies View Related

Count Of Cells In Column Based On Criteria On That Line In Different Column?

Mar 10, 2014

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

View 1 Replies View Related

Count Filled Cells In One Column Based On Date In Another Column

Apr 24, 2009

I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:

AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May

If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.

View 2 Replies View Related

Combining Cells In Column Based On Matched Value In Another Column?

Apr 5, 2013

I have a problem which requires a vba routine that is currently outside of my level of expertise...

Here is an example of the data:

TESCO GARAGE 3729
59
REF 177 1092098207 BCC
59
CO-OP GROUP LTD
60

[Code]......

View 3 Replies View Related

Summing Cells In Column Based On Data In Another Column?

Feb 27, 2012

I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.

A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX

I had thought an IF formula would be the way to go.

View 6 Replies View Related

Copy Cells In One Column Based On Criteria In Another Column

Aug 17, 2006

I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.

Here is what I have:

Option Explicit

Public Sub RatingbyDept()
Dim Dept As Range

With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept

I keep getting error 92 - "For loop not initialized".

View 8 Replies View Related







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