Excel 2010 :: Using VBA For Sub-Totaling Returning Unexpected Values

May 8, 2014

This relates to this thread - [URL] .....

This is the only macro in this file

[Code] .....

It will perform the sub-totalling for the column that has the current active cell

When I select Column I

It does.....
-for each blue cell it finds it provides a total of all the white cells bellow it
-for each yellow cell it find it provides a total of all the blue cells bellow it until it reaches a yellow cell

It works backwards, so not exactly as I've just described, but that isn't the problem

Problem is -
UK Excel 2010 - results are as expected
US Excel 2010 - returns zero values for totals

We've possibly narrowed the problem down to when it looks at cell properties, more specifically -- If Cells(rowX, 1).Interior.ColorIndex = 20 Then

How to get results in blue and yellow cells when you select a cell in column K then execute the CreateTotals macro.

Attached File : Example-1p.xlsm

View 6 Replies


ADVERTISEMENT

Unexpected HLOOKUP Results (Returning MAX/MIN Values From Multiple Rows In A Named Range)

Nov 6, 2009

This is a floowup to the issue that was originally posted as "Returning MAX/MIN values from multiple rows in a named range ". I marked that post as solved since I have worked through part of the issue and since have a different one.

View 2 Replies View Related

Excel 2010 :: Searching For Files By Cell Value And Returning Values

Jun 24, 2014

I need a formula (but most likely a VBA macro) that will search through a folder for a file than get data from that file. The files are named in systematic way, but I need the entire formula to work from inputting a mold number in one cell. E.g. I input 6291 in cell A2 the vba macro searches for file “6291 mold.xlsx” and returns a range of numbers as well as pictures in specified cells. Is this possible? If so how?

The closest thing I have found is VBA macro that retrieves a list of media files in a folder, I listed the code below.

[Code] ....

[URL] ....

View 3 Replies View Related

Excel 2010 :: Returning Value From Third Column Based On Two Other Columns

Jun 24, 2012

Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.

OrderNumber
TaskName
SignOffDate

1
OED
01/05/2012

[Code] ........

What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:

OrderNumber
OED
CTN
FAD6
RFS

1
01/05/2012
17/05/2012
22/05/2012
02/06/2012

2
03/05/2012
19/05/2012
27/05/2012
02/06/2012

There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.

An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.

I'm using Excel 2010.

View 5 Replies View Related

Excel 2010 :: Searching A Folder For A Keyword And Returning The Full Path?

Jun 19, 2013

My Excel 2010 spreadsheet contains client data like the below:

A
B
C
D
E

What I'm trying to get from this is a personally addressed email with 2 attachments, one will be standard to all recipients and one will be unique and specific to that recipient. The filename of the unique attachment will contain the reference but will have some other stuff in the filename as well on either side that I will not be able to remove.

In terms of the file locations the unique ones will be in subfolders of the folder holding this workbook and the generic one will be in the same folder as the this workbook.This is some adapted code that solved a similar problem (in Excel 2007 though) on a different website.

VB:
Sub Mail_Report()
Dim OutApp As Object
Dim OutMail As Object [code]....

In that case the file started with the "reference" field but in my case it is in the middle and the formats vary depending on the provider, there will only be a maximum of 5 providers but I would like to avoid 5 different macros if at all possible so I need a search function of some sort.

View 2 Replies View Related

Totaling Up The Values From Specified Range Of Dates?

Apr 11, 2014

see the attached workbook. The "data" sheet is the raw data for individual sales orders. Column A has the SKU for the item, and there are also columns for quantity sold and the date sold.

Column A of Sheet 1 is a list of each individual SKU (no duplicates). What I'd like to do is search through the data sheet and total up the quantity of each SKU sold for several individual months. There is also a column that would display totals for the past 30 days (with "today" as day 30).

View 3 Replies View Related

Returning Values From One Excel Sheet To Another Using Cell Addresses?

Aug 16, 2014

I have two sheets: sheet1 and sheet2.

In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.

In sheet1 in column "B" are names.

I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.

Is there an Excel formula or vb script that will do this?

I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.

View 4 Replies View Related

Excel 2003 :: Returning Multiple Values From Index?

Apr 20, 2013

I am using the following formula:

=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")

to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).

There are faults in my formula, and if I'm honest I don't entirely understand it !

I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.

To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.

Using Excel 2003

View 9 Replies View Related

Excel 2007 :: Returning Values Based On Criteria?

May 17, 2014

I'm using Excel 2007

I have the below data in sheet 1

In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD

DEB/CRED
ACC1
ACC2

[code]....

View 7 Replies View Related

Excel 2003 :: Returning The Date In Column For Matching Values

Apr 4, 2014

I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.

Scenario:

Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.

Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.

View 10 Replies View Related

Excel 2007 :: Lookup One Value And Returning Multiple Values (Horizontally) With Two Sheets

Jul 31, 2014

I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:

1st sheet:

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;

[Code]....

I have 180 rows of data like this one in the first sheet

Second sheet(named sheet 1) is not used for this

Third sheet(named sheet 2):

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;

[Code]....

Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.

This formula should be in the column following "majoration".

I am using excel 2007 and windows 7.

View 3 Replies View Related

Excel 2010 :: Produce Unique List Of Key Values With Second Values Concatenated Together

Sep 15, 2013

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b

My system is Windows 8 Excel 2010.

View 7 Replies View Related

Excel 2010 :: Highlight Same Values In Different Worksheets

Jul 26, 2013

I have "Worksheet1" with Columns A and B for IP Addresses, then Column C is for a device name.

I have "Worksheet2" with a range of IP Address from F5:I260

When I enter a new device and assign it the IP addresses on "Worksheet1", I would like the IP Address to highlight in "Worksheet2"

This way I can keep track of which IP address I have used. Excel 2010

View 7 Replies View Related

Excel 2010 :: Sum Values Between Two Dates In Tables

Jan 27, 2014

Code:
tblCluster
Jan-14 Feb-14 Mar-14
Salary 10,000 20,000 30,000

Jan-14
Feb-14
Salary 30,000

I have a table above (in red), as an example, my actual table goes out 60 months.

Where the Green Value is, I need a formula that will reference the two months above it (They could be any of the 60 months), that will then sum the salary from the table between the selected two months (including these months)

I have tried,

Code:
=SUMIFS(C6:BJ6,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],">="&'Cluster Analysis'!$I$51,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],"

View 2 Replies View Related

Excel 2010 :: Replace Values In A Cell

Apr 24, 2014

In Excel 2010 I am attempting to replace values in a cell, the right two values with "00", but am having difficulties with the correct '=replace' function.

View 2 Replies View Related

Excel 2010 :: Looking Up One Value And Need To Return Multiple Values Horizontally

Mar 28, 2014

I have in column A duplicate values and in column B different responses (Sheet: Lookup). I need to look up the value in column A (Sheet: Results) and bring back all the responses in column B (horizontally).

Nittie Query.xlsx

View 5 Replies View Related

Excel 2010 :: Countif And VLookup Of Multiple Values

Jan 15, 2014

I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.

I have attached a sample workbook as a reference : Sheet1.xlsx‎

View 8 Replies View Related

Excel 2010 :: Plotting Values V1 / V2 / V3 And V4 According To Given Date And Condition

Mar 25, 2014

Excel Version : MS Excel 2010
Attachment filename : <Forum to plot the values.xls>

My requirement is i want to plot the values V1, V2, V3 and V4 from the given condition (A1 / A2 / A3 / A4) and the given date.

If the given date is not available then the formula should take the older date than it not next date. For example if the given date is 10-Mar-14 and the date is not available for the given condition then it should take the older / earlier dates like 09-Mar-14 or 08-Mar-14 like and it should not take 11-Mar-14, 12-Mar-14.

The values should be plotted in the yellow cells.

View 1 Replies View Related

Excel 2010 :: Showing Multiple Values Per Cell?

Jul 31, 2014

When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.

If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.

View 5 Replies View Related

Excel 2010 :: Pasting Space Separated Values?

May 9, 2013

I have some text out of note pad in the following format

"cat" "dog" "bird" "turtle"
"cat" "dog" "bird" "turtle"

There is several lines like this. I need to copy it out of notepad and paste it into excel where every word in quotes is in its own cell. Right now if I paste it everything goes into cell A1.

I am using excel 2010..

View 4 Replies View Related

Excel 2010 :: Crashing When Pasting Formulas As Values

Nov 8, 2013

I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.

The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.

I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.

I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.

There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.

I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.

Formula:

=IF(AND(N3>1,ROWS($1:1)<>MATCH(M3,$M$3:$M$7979,0)),"Exact Duplicate","")

is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.

View 1 Replies View Related

Excel 2010 :: Looking Up Values Across Sheets And Return Not Only Value But Also Sheet Name

Jan 14, 2014

I am looking up the largest value across various sheets (1 to 99) with the following formula:

=LARGE('1:99'!$B$1:$B$50;$C3)

That correctly returns the largest value in range B1:B50 across sheets 1 to 99.

However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.

View 3 Replies View Related

Excel 2010 :: Selecting Corresponding Values From Multiple Columns

Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..

Excel2010

Excelhelp.jpg

View 3 Replies View Related

Excel 2010 :: Finding Values Using Linear Extrapolation?

Mar 26, 2014

I am looking to find out certain values from a current set of data using linear extrapolation in Excel 2010.

I have attached the data i am using below:

Data.png

I need to find out the specific power output and heart rate values, at a set blood lactate value (i.e. When the blood lactate value is exactly 2.0mmol.L-1, what is the given power output and heart rate).

I have tried plotting the data into a line graph with a secondary axis, however because i am unable to change the horizontal axis range for the power output values, i cannot interpret the data in this way.

View 14 Replies View Related

Excel 2010 :: Formatting Chart Axis Values?

Oct 8, 2011

Why the recorded code doesn't work? I recorded the code below to format the Y-axis values of a chart with the recorder.

Code:
ActiveChart.Axes(xlValue).Select
With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Arial"

[Code].....

View 6 Replies View Related

Excel 2010 :: Hide Zero Values In Pivot Chart

Nov 29, 2011

Excel 2010 how to not chart zero values in a Pivot Chart?

View 1 Replies View Related

Excel 2010 :: Loop Through Row Of Data Testing Values

Dec 25, 2011

I am running excel 2010. I have set up a variable " testvalue" type variant

I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".

Code:
testcol = 1
testrow = myTextRows - 1 'penultimate row
Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0
testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value
Select Case testvalue

[Code] ........

View 3 Replies View Related

Excel 2010 :: Hyperlink - URL Has Two Values In Which To Input Data

Feb 24, 2012

I wish to use a formula to grab data out of cells A1 & B1 and use that data to complete a hyperlink URL in cell A3. The base URL never changes but the last two variables do. For instance:

URL Format: http://www.test.com/XXXX&page=XXX

Example data:
Cell A1 = 1234 (always 4 digits)
Cell A2 = 567 (always 3 digits)

Desired Final Result, hyperlink: [URL] .........

I cannot seem to figure it out.... my formula i have gives errors:

=HYPERLINK("http://www.test.com/"(A1)"&page="(A2), "Linked")

This is Excel 2010

View 2 Replies View Related

Excel 2010 :: Update Values From And To Closed Workbook?

Mar 7, 2012

I am having a workbook (say a.xlsm) which has value entry fields and some values are given to another workbook (say b.xlsm) which has some sort of calculations and the result is projected back to the book a. most of the time book b will be closed. I am using Excel 2010.

I opened and saw that the result which is calculated and projected from workbook b is not getting updated. I opened the workbook b and saw that the values I have entered in a has not been updated in b. note that I am opening one book at a time and I do click on update links when I am asked.

View 1 Replies View Related

Excel 2010 :: VLookup Based On Values From Two Columns?

Jun 4, 2012

I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.

Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas

Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas

View 5 Replies View Related







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