Using Data From Specific Tab For Formula In Different Tab?

Jan 11, 2013

I have the following formula...

=SUMPRODUCT((C:C="A CO")*(T:T="Pay Hurt")).

It shows the number of cells in column C say "A CO" AND the number of cells in column T show "Pay Hurt". I had this formula on the same tab as the data, and then a Report Tab that pulls that total number.

I only need that total number on the Report Tab but don't know how to put that formula in that tab while referencing the data from a different tab. Is there a way to have that formula in the Report Tab that references the data in the "Data" tab. My only guess would be something like

=Data!SUMPRODUCT((C:C="A CO")*(T:T="Pay Hurt"))

But this didn't work.

View 1 Replies


ADVERTISEMENT

Formula To Separate Specific Data From Data List In Sheet1 Into Sheet2 / Sheet3

Jun 30, 2014

I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.

The data ref will be column F which is the different event locations.

I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.

I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.

The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.

View 5 Replies View Related

Finding Formula To Search For Specific Data

Nov 15, 2011

I am having trouble getting a formula to work with exactly what I need. I need a formula to find the total quantity of each product. The file I'm searching from does not list the product name or description in the subtotal column. The host file is similar to below and I just want the product name and total quantity in the new worksheet. I will have a list of all products we carry in the new worksheet and I want to pull the total quantity ordered for each item, and if none were ordered return a quantity of 0. The program the host file is exported from also lists a header at the top of each page, so for example there may be a header in between the last occurrence of Product D and the Subtotal.

Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
Product B Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
Product D Description Sub description Date Ordered Quantity Ordered
Product D Description Sub description Date Ordered Quantity Ordered
Product D Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity

View 2 Replies View Related

Formula To Extract Specific Data In Cell?

Dec 2, 2011

I have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.

View 2 Replies View Related

Formula To Find The Sum Of A Set Of Data Based On Specific Criteria

May 20, 2014

automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.

View 3 Replies View Related

Formula To Search A Cell For Specific Data To Extract

Mar 13, 2009

I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell

Example:

If I have a list of vehicle descriptions all in different formats:

CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX

Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"

View 4 Replies View Related

Search And Extract Cell Data After And Before Specific Values With Formula

May 20, 2014

I need a formula to extract data after "<li>Color: " & before"</li>"

A
B

1
ORIGINAL
RESULTS

2
blah blah blah<li>Color: White</li>some informations
White

3
blah blah blah<li>Color: Red</li>some informations
Red

4
blah blah blah<li>Color: Black</li>some informations
Black

5
blah blah blah<li>Color: Red</li>some informations
Red

I have already use this formula but I get the "#VALUE!" error even if the cell contains the "<li>Color: </li>" data.

[Code] ....

Sometimes, some cells does not contain the "<li>Color: </li>" data, I don't want the "#VALUE!" error, I just want a blank cell there.

This is an example with the real values I want to extract Example.xlsx

This is the formula that works

[Code] ......

View 8 Replies View Related

Specific Formula Executed In Column Of Data Depending On Selected Value In Cell

Apr 4, 2013

I have a sheet with a country in it in A1 (validation list).

Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)

I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.

I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..

View 5 Replies View Related

Specific Cell To Run A Specific Formula

Aug 14, 2009

let say in cell A6 i have a list

SnG
Cash
MTT

I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.

So in cell A6 is the list.

in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.

But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this

=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))

View 12 Replies View Related

Macro To Extract Data From Specific Column To New Sheets Specific Column?

Jun 14, 2013

I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.

For instance, in Sheet1:

Chicken
Cow
Donkey
Pig

[Code]....

So the above table would be the result i'm aiming for.

NOTE, its not different workbooks. I'm looking for sheet to sheet macro.

I've attached a file as well if someone wants to have a go at it. There are no codes in it.

View 3 Replies View Related

Create Userform That Adds Integer Data To Existing Data In Specific Cells

Jan 29, 2013

I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.

If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.

View 1 Replies View Related

Replacing Sheet1 Data With Sheet2 Data For Specific Columns Using Macro

Mar 8, 2014

I have data in sheet 1 like below

Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394

Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai

So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.

View 1 Replies View Related

Excel 2003 :: Match Two Sets Of Data And Display Specific Data?

May 12, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)

Referral
A
B
C

[Code]....

way to do the calculation using Excel 2003

View 9 Replies View Related

Hiding Runtime Errors: Refreshes A Specific Data Range That Imports Data From A Text File

Nov 25, 2009

I have a macro that first refreshes a specific data range that imports data from a text file as such:

View 2 Replies View Related

Formula To Go To A Specific Column?

Mar 5, 2014

Is there a way to go to a specific column i excel? My data ranges from Column A to Column TP and rather than using find, or scrolling along to find the column i want, i want to know if i can put a date in A1 for example and it will go straight to that column?

I have used a macro before to filter horizontally, but thats not going to work in this case, i just want to go straight to that column labelled "05/03/2014" for example.

View 3 Replies View Related

Formula Needed That Changes According To A Specific Day

Oct 12, 2008

I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep

View 4 Replies View Related

Formula To Find Specific Value

Jun 3, 2009

Hello, i've been using the below formula to find the most consecutive "W" (Wins) without any "L" (Loses) in 2 rows, there are between the "W" blanks and and numbers

=MAX(FREQUENCY(IF($Y5:$DP5="W",COLUMN($Y5:$DP5)),IF($Y5:$DP5"",IF($Y5:$DP5"W",IF($Y5:$DP5"",IF($Y5:$DP5>0,COLUMN($Y5:$DP5)))))))

The problem is at the start or end of the count eg
A B C D
L WWW
W W L
The above returns 5 the result i am looking for is 3 as column B and C are the only ones without an L, there are alot more columns so obviously if there are larger sets of consecutive W then the result would be bigger,

View 9 Replies View Related

UDF If Formula That Contains Specific Word

Jan 15, 2010

I have a UDF which basically accomplishes a lookup. To do that I use this
Function name(number As String)
Select Case number
Case Is = "1"
name = "BOB"
.
.
.

View 9 Replies View Related

Removing Specific Data From Series Data In A Chart

Nov 1, 2011

I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?

View 2 Replies View Related

Formula To Find A Specific Word?

Aug 28, 2012

Is there a formula to find a specific word through many columns? Between A1 and L4500 i got a lot of diffrent information. I want to show if there is any cells in the row with the specific text, a formula in column M. Etc. If C100 got "topside" a notice in column M

View 5 Replies View Related

How To Freeze Specific Ranges In Formula All Together

Dec 12, 2013

I want to freeze the formulas (F4) in whole column in Microsoft Excel, Is it possible to freeze together, not to freeze one by one. I freeze it one by one but that's actually a pain when you have hundreds of formulas to freeze.

View 7 Replies View Related

Average Formula For A Specific Condition

Jul 30, 2009

I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using. On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.

So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at. I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.

View 3 Replies View Related

Get Value 0 If The Formula Can't Find The Specific Parameters ?

Oct 1, 2009

How to get the value 0 if the formula can't find the specific parameters given below. What is the extra bit I need to add to this formula below?

=INDEX(Bayswater!1:65536,MATCH("Sim Only PAYM",Bayswater!A:A,0),4)

View 5 Replies View Related

Remove Row With Specific Value (count By Formula)

Aug 20, 2013

If i want to remove the row that has value lower than 2 in a particular column (in this pic it's column L).

this column come from a =counta formula to count how many 'x' on each row.

[URL]

i paste few rows below. the column i am talking about is highlighted as 'count' My data goes really long below, thus it's not effective to use filter option.

1
1
2
2
3
3
4
4

[code].....

View 9 Replies View Related

Put Manually Formula In Specific Cell?

Jun 5, 2014

I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.

For exemple:

=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01

requested date:
2012-01-01

Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...

View 3 Replies View Related

Using Only Specific Cell Either For Entry Or Formula

Apr 25, 2007

I have a problem to solve

I have 2 worksheets named 'Products' and 'Order'

At the 'Order' i use 3 cells

Code
Pallets
Cases

When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.

What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.

1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000

2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000

because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50

View 9 Replies View Related

Formula For Copying From Specific Worksheet

Mar 4, 2009

Can a formula be created to link certain information to a specific cell.

In a workbook I have 10 worksheets that are have information in identical locations:names are English, Math, Science, VPA.......and so on.

I created another worksheet that prints out linked info from the worksheets: Eng, Math, Sci, VPA........

Current Print Worksheet called "Print" link to the "English" sheet

links in cells look like this
A1 - +English!B10
A2 - +English!B11
A3 - +English!C20.......................over 100 links

Can I have a formula in the link cell where I can type "Math" so all the cells change to links to another sheet.

So in B2 I type "Math" and the formula changes

A1 - +Math!B10
A2 - +Math!B11
A3 - +Math!C20

View 9 Replies View Related

Formula Won't Calculate On Specific Computer

Mar 19, 2007

We use a spreadsheet to log all transactions at our front desk. I've made a column that automatically calculates the time when a transaction is input into a row. The formula is as follows:

=IF(D5="","",IF(A5="",NOW(),A5))

This morning that formula stopped working on the computer at the front desk, returning a message about a circular reference. I can open that same spreadsheet on my computer as well as others in the office and it calculates fine, but of course on the one computer on which I need it to work, it won't ;P

All computers are using Excel 97. Presumably a setting has changed on the front desk computer that's affecting this calculation.

View 4 Replies View Related

Adding Specific Values In Different Columns Using One Formula?

Mar 28, 2014

How would I go about finding the "Number of Shirts Ordered" values in the top right?

View 1 Replies View Related

Formula To Check To See If Every Row In A Specific Column Is Blank

Aug 22, 2008

Does excel have a formula to check to see if every row in a specific column is blank and if it is then set the value?

View 10 Replies View Related







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