Multiple Separate If Forumlas In One Cell

May 5, 2009

I have multiple collumns with monetary value that are intended to automatically be deducted once the previous collumn is depelted.

I need to also have the values stop at 0 and not go into the negatives.

Here is what I have now

=IF(K12<=0,SUM(I11*5%)+I11-M12, IF(AND(I12<=0,K12<=0 )=TRUE, 0,SUM(I11*5%)+I11-M12))

Basically I have collumn 1 and 2

collumn 1 collumn 2

100,000 50,000

I need to have collumn 1 start to be deducted once collumn 2 reaches zero. I also need both collumns to stop at zero and not go negative.

View 12 Replies


ADVERTISEMENT

2 Forumlas In 1 Cell

May 3, 2009

I'm trying to put a sort of "IF(cell)=PAID,SUM(cell),nothing" But every time I do it, it comes up with some error about puting a ' before it if I don't want it to be a formula, I'm trying to figure out how much I ow, and how much I've spent.

View 5 Replies View Related

Separate Multiple Lines In Cell To Multiple Rows?

Jul 10, 2014

I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).

So my data is as follows - (I have attached teh sample file)

"Pari bom - GP and CFO 02547895452 pari@gmail.com

papa bom - GP and CFO 23365789541 papa@gmail.com

mama bom - CFO 2587412589 pari@gmail.com

nana bom - CFO 2587412589 nana@gmail.com

masi bom - CFO 2587412589 masi@gmail.com

"

I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.

View 4 Replies View Related

Separate Data From One Cell To Multiple Cells On Different Sheet

Sep 10, 2012

I have a sheet labled Parsed and a sheet labled Prices. The Data on the Parsed sheet is in W2:W1000. the data on the Parsed sheet, can have anywhere from one value to up to ten values in it. (again all sparated with a space.)
the data is separated by a space between each number value. I am trying to find a way to take the values from Parsed!W6 for example and separate the data into the prices sheet in to different columns. Some cells may be blank.

Example:

PARSED SHEET.

W
X

1
Equipment

[Code] .......

View 2 Replies View Related

Look Up Data From Multiple Columns And Insert In One Cell Using Comma To Separate?

Jan 31, 2014

I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:

The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.

A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3

November is in A3

If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2

View 14 Replies View Related

Using Value In Single Cell To Determine Multiple Data Validations From Separate Work

Apr 10, 2014

I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.

Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer).
I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.

I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.

E.g.
IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc
IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...

I have managed to do this with the following formula, but only if the data is stored in the same sheet.
Cell B1
=IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1))
Cell B2
=IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))

[NOTE]
=meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored.
=magicArmour2 represents the range of cells where the Magic Body Armour is stored.

The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)

View 5 Replies View Related

For A Column Of Cells Separate Text In Single Cell Across Multiple Columns

Nov 29, 2013

New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:

(NOTE: does not contain actual names or info) Book1.xlsx

This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?

While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.

View 6 Replies View Related

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

Sep 23, 2013

Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.

View 5 Replies View Related

Shade A Cell Red If Two Separate Conditions Apply (in Separate Cells)

Dec 3, 2013

I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.

View 11 Replies View Related

Copy Multiple Columns In Multiple Workbooks Into Separate Worksheet?

Feb 27, 2014

The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".

I would rather not have to copy this by column for each of the 4 workbooks

[Code].....

View 6 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Selecting Multiple Separate Ranges

Dec 23, 2006

I'm trying to get multiple seperate ranges selected but Excel interprets it as criteria even when I use Insert> Function. The problem probably comes from the use of commas in formulae. Example:

I want A1:E1 and A3:E3 and A5:E5 to be the range involved which leads me to construct:

= COUNTIF(A1:E1,A3:E3,A5:E5...

However, Excel interprets the 2nd and 3rd values after the commas to be criteria or something else.

View 3 Replies View Related

Saving Multiple Sheets As Separate PDFs?

Jan 9, 2014

I have an Excel file with multiple sheets (over 100). I want to save them each as an individual PDF, with different (but similar format) names.

As an example, I want each PDF to be be called "Date Name" (i.e. "1-8-2014 Sarah", and next one will be "1-8-2014 Beth", etc.)

Is there a way to do this all at once, instead of my having to manually save each one? Is there a way for me to set it so that the title lists the date and then, for example, whatever is listed in A1 (which will be the name)?

One other question is that because I get this report from someone else who generates it, the way the file is formatted on my computer is that the print area is set at too small, so if I convert immediately to PDF, everything gets messed up. I have to manually make the print area bigger so that the PDF version encompasses everything on one page. Right now I've been manually adjusting the print area page by page - any way to do this all at once?

View 1 Replies View Related

Combine Multiple Files In One Under Separate Worksheets?

Jun 28, 2014

I have multiple files in a folder.

The files are named:
File1.xls
File2.xls
File3.xls

....and so on; the number of files varies. I am trying to write a macro, to combine all *.xls files in one workbook having each file as a separate worksheet.

View 2 Replies View Related

Matching Multiple Criteria On Separate Tab To A Matrix

Apr 25, 2013

Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.

View 5 Replies View Related

List Multiple Cells In Separate Worksheet

Mar 19, 2014

Is there a way to display multiple cells in a separate worksheet that are a part of multiple worksheets? Please see below.

Worksheet 1 has list of email addresses in column A
Worksheet 2 has list of email addresses in column A

How to have Worksheet 3 display email addresses in column A that were on Worksheet 1 and Worksheet 2? Considering all duplicates are removed from each worksheet.

Worksheet 1 (column A)
red
blue
green
yellow

[Code]....

Need to have Worksheet 3 display as: (column A)
red
blue
green

View 1 Replies View Related

Select Multiple Rows As Separate Selections

Jul 27, 2006

Is there a way (with a macro?) to select all rows within a certain range (e.g. from row 3 to the end of the sheet) SEPARATELY, just as selecting them manually with ctrl+select?

Like in my example sheet;
I would use the AutoFilter on row 2 to show only the items with prices equal to 30. Now I want to select the entire rows (name, amount & price) of these two items, so that when I turn of the AutoFilter the two rows would still remain selected.

View 3 Replies View Related

Merge Multiple XLS Files Into One XLS File In Separate Sheets?

Oct 12, 2010

merging the multiple *.xls files into one single *.xls file but each *.xls file com in separate worksheet.

Say i have 30 xls files in datewise i.e., 01.10.10, 02.10.10, 03.10.10 so on....

I want to merge all the above 30 xls files in single file master workbook - in that master workbook file the above 30 xls should come in separate work sheets.

View 9 Replies View Related

Separate Space$ Delimited String Into Multiple Cells

Oct 13, 2009

I am trying to split a string into separate cells. I have managed to generate the formula for the description and first dlr value in the string but I am have trouble figuring out how to build functions for the rest of the string.

Example attached.

View 3 Replies View Related

Pull Data From Multiple Excel Into Separate Sheets?

Mar 26, 2014

As part of report consolidation I need a Macro that can read through all the files in a particular location(Say a folder path) Pull the data for each unique products into separate sheet

In the example below : All the rows with "Chocolates to sheet 1" , Drink to Sheet 2 and so on Since the products in column 3 can vary the unique sheets need to be created at run time based on the source data

Workbook1
WEST
100
Chocolates

[Code]....

View 2 Replies View Related

Show Multiple Instances Of Field On One Row, Instead Of Separate Records

Mar 30, 2007

Attached are two files. I want to make the "ORIGINAL" file look like the "DESIRED_OUTPUT" file using VBA.

View 3 Replies View Related

Populating Multiple Items With Validate Function In Separate Columns

Mar 28, 2014

UREC Bar Event Sheet.xlsx

I am trying to correspond prices with products from the validate function in multiple columns. I want to select the product and have the price for the product populate in the 'cost' column. I have the list of products with corresponding prices on sheet 2 named 'products'. I have attempted to write a formula in cell G11 on the Event Sheet, but was unsuccessful. Please direct my path.

View 3 Replies View Related

Indexing Multiple Columns To Return A Result From A Separate Column

Oct 22, 2009

Sheet2:
col A = contains the style#
col B = contains the color of the style
col C = contains the size of the style
col D = contains the qty of the style,color, size

Sheet1:

I would like to do the following:

A1 = input the style #
B1 = input the color of that style
C1 = input the size of that style

then D1 should automatically contain the qty of the mentioned style, color, and size.

View 4 Replies View Related

Copy Selected Data From Multiple Rows Into Separate Column In One New Row

Dec 1, 2013

ID Name QTY Price

1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100

I need macro to loop through all data and generate this table in another worksheet

1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100

Each data in a row in the new table will occupy a different cell.

View 3 Replies View Related

Excel 2003 :: Deleting Rows For Multiple Files In Separate Workbook

Sep 10, 2012

I'm new to this forum and to VBA

First-off, I'm using Excel 2003 SP3.

The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg

G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc

The files contain the following data:

DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc

I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.

My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.

I have a separate xls file with criteria data in two columns:

FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc

The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.

the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!

Option Explicit

Sub CopyPaste()

'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).

'The data from the Results sheet is then copied to the Master sheet.

Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String

[Code]...

View 9 Replies View Related

Print Macro For Multiple Ranges And Graphs On One Worksheet To Separate Pages

Mar 26, 2014

There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.

Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?

Sub Load_Data_Report()
'
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
'
' Keyboard Shortcut: Ctrl+j
'

[Code] ........

View 1 Replies View Related

Returning Data From Separate Worksheet By Matching Multiple Criteria - Vlookup / If Combo?

Jun 8, 2014

I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.

The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.

View 2 Replies View Related

Separate Letters And Numbers In A String Into Separate Columns

Nov 20, 2012

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A

View 5 Replies View Related

Separate Numeric / Text Combination Into Two Separate Columns

Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC

The result would be:

302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC

View 6 Replies View Related

How To Separate Text From Numbers Into Two Separate Cells

Feb 13, 2014

I'm trying to separate text from numbers into two separate cells...

Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.

Input: Output 1: Output 2:

Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234

Ideally, I would like to do this with a formula...

View 6 Replies View Related







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