Convert Absolute Values To Negative Values Based On Criteria

Aug 14, 2008

is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"

Below is a small sample of data, real data is around 100 rows

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.

I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.

for example

I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.

I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.

I want to compare 2 columns based on their positive and negative values..

For example,

A B C ID1 91 -7 ID2 -7 74 ID3 -4 -5 ID4 90 4 ID5 -55 34 ID6 33 3

I want to know 1) which id's have both negative values in both columns B & C, 2) which id's have both positive values in both columns B & C, and which id's have opposite signs in both columns B & C.

I was wondering how to make the following: get a list where each Number from the "source file.xls" is assigned to a Group like in "final list.xls" through "conversion table.xls".

In the "source file.xls" we have apart from Number, the Series and Category columns. I need to compare both Series and Category to the same from "conversion table.xls" and in the third workbook output the matching Number from "source file.xls" and Group from "conversion table.xls" according to matching algorithm.

I am attaching the files: source file.xls conversion table.xls final list.xls

All three should be different workbooks and the first two will need to be closed. Also very important is the source file can have many names so it would be great to implement an open dialog box to load the data from "source file.xls". All data is in text format....

I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.

I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.

Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.

Using the attached example, how do I get Excel to display in cell F8 the correct SUM of cells F4 & F5 without having to manually enter the SUM in column F. I need F8 to the be the SUM of the amounts that are displayed.

Is there an easier way to sort a column full of numbers by their absolute value without having to create an additional column, use formulas to get the absolute, then sort that column and then delete it.

I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.

I have this data source file that comes from a back office program, and basically what i want it to do is to turn the number negative in column C when the header of that column in column A is WITHDR and leave it positive when it is CONTR. What i was thinking was that it could go to column A from column C and go up each cell until the cell is not a date and multiply the column C number by -1 depending the column A header equals WITHDR and do that for every cell in column C, but I dont know how to proceed, im new at VBA.

I have copied over a row of $, ($) amounts from one worksheet to another. I am trying to make the amounts I copy over past in absolute values. Is there a way to do this? Below is the current code:

'copying over the Amount in Local Currency ST Reclass Sheets("Recon_ST").Select Range("S9").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select Selection.Copy Sheets("ST Journal entries").Select Range("J2").Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False

I tried to make use of the indirect function but don't think this is the right type of formula to be used.

Essentially in column A..i have linked cell references

e.g = D2767 However, for the whole of the column I'd like to change the refs to =$D$2767, ALL in column A in one go.

I know that if you place the cursor in one cell immediately after the = sign and press F4 it automatically adds $ SIGNS TO THE CELL, which I find useful for V-lookups, but I obviously cannot do it (fill it) all the way down the column i'm using excel v7.

In addition or related how would I write an IF formula to read eg.

= IF (cell ref begins with C, then link and add PRECEDING AND FOLLOWING $ signs to all refs)?

Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.

I need to be able to find a value in another sheet based on 2 criteria

The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).

The formula I use is as follow: =LOOKUP(BA2,Budgets!B:B,Budgets!I:I)

and this is what I use to get the column: =+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here

and I use this too: =MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too

If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....

What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..

I have tried index and match and quite frankly, I never made it work, always getting an error.

I've got an interesting problem which I haven't been able to find an answer for.

A B PR Yes PR Yes PR Yes DN Yes DN No DN No

I need to count how many unique values in column a have every row in column b the same value. So counting for yes values, PR would equal one but DN would equal zero.

I used this formula to find out how many unique files had yes values, now I need to find out how many files only have yes next to them.

I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.

I am trying to calculate the average of the absolute values of a range of visible cells. I use filters to extract the data rows I need from a large spreadsheet. Then I need to calculate statistics on the resulting data, i.e. the visible cells. The line I am using to calculate this is:

However, what I get back is simply the value of the first visible cell in the range, i.e. the average of the absolute value of one number is that number. how to make this work

Have a query that pulls general ledger data. Named Range SALES defined for Sales Account numbers. What is the best way to always have SALES (which pulls as negative number) converted to absolute values?

I have in my mind that it would be some sort of worksheet change event . . . but I don't really grasp the byval/byref TARGET concept by itself, let alone whether it can work on a named range.

I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45

The columns that are named with times are times that indicate a break time. The column named title is the actual shift time, eg 08:00 - 17:00.

I need a formula that would look at my source data, and populate a sheet in the following layout

agent_id, agent_name, title, start_time, end_time

The title be one of the following: Shift 08:00 - 17:00 Tea Break 10:00 - 10:15 Lunch Break 12:00 - 12:30 Tea Break 14:15 - 14:30

If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.

I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.

I have a worksheet consist of some 17,000 records - from ColA:ColDZ. In Datasheet, I have all values. From this, based on some values in ColA all records should be copied into another sheet(s).

I have attached a sample workbook in which I have explained the requirement.

Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............

I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.

I'm facing a big problem and I can't found a solution for days.

I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

Let me Put to you an example for you been able to understand my problem.

Example considering Range "C2:C20" exluding blank cells

(1) - To count unique values =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

(2) - To count unique values based on criteria in range "B2:B20" =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))

(3) - To sum unique values on "E2:E20" based on same criteria =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))

They all work fine. But I need to insert a merge at Row 10 for better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's. I just don't know what to do guys ....

I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.

The smaller table, Drift List, is to be used as a reference to fill up the bigger table. Here is how Drift List looks like: