I browsed through the forum looking to find a way to solve my problem, but could not find it. Maybe someone here can help me out.
I have lots of data, and it would be impractical to do it manually. Here is an example: ...
I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 Replies View RelatedI need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.
For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").
I'm sure this will be a very simple one for someone - it leaves me *head scratching* though! I have attached a VERY generic example and it includes what I am trying to SUM for the given data. From the example if we look at Question Z1:
"Total Cost of All types of "Lemons"" = ?? I could simply do
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
View 5 Replies View RelatedIn building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View RelatedCan I use conditional formatting to check if a field has a value or a formula?
View 4 Replies View RelatedI am having trouble developing a formula which will return a field (specific cells back to me) based on some conditions.
Basically I want a formula which will return the date (G3-T3) to me on each client. On the conditions of there usage.
So I want it to extract the value of the first number to comes across (the value of the number does not matter).
E.g. Gissle (D5) "The formula should go along and provide me with the date (value) "1/7/2013" [Cell I3) in cell [E5).
It just needs to hit the first value it comes across horizontally and provide me with the date (field) which it is located in Column E (E5-E11).
save at work because I had to manually scope out the first value and insert and copy the date (took an hour).
Sheet1 *DEFGHIJKLMNOPQRST2***Usage*************3*
Date*1/5/20131/6/20131/7/20131/8/20131/9/20131/10/20131/11/2013
1/12/20131/13/20131/14/20131/15/20131/16/20131/17/20131/18/20134
Client*************8**5Giselle*********5******6Elaine******4*********7Lidi**4*************
8Eliza****************9Ji***************410Erika****1***********11Maria********5*******12*****************
inserting a formula in PIVOT table field....
View 9 Replies View RelatedI import a database with mostly unique records which has a description field that includes, among other things: Model Name, followed by predominant color. Unfortunately, there are no seperate fields for the Model Name or Color, e.g.:
DESCRIPTION
8070 CALLING WATER RED
508001 EYEDAZZLER LILIANA CARAMEL
502034 EYEDAZZLER LILIANA RED
#402022 DIAMONDS IN THE DARK RED
402024 DIAMONDS IN THE DARK PALOMINO
Is there a way to create a summary table which would count the number of occurences of the Model Name and the number of occurences of the color?
Fortunately, there are no more than 25 Model Names, and no more than 10 colors or color/combinations, so we could compare against two lists.
I have a PivotTable which shows the number of packs produced in a month. I created a calculated field which shows the number of vehicles needed to move these packs. The number of packs per vehicle is dependent on two criteria, the Pack Description and the Store. My calculated field, Shunts, is described as:
=Packs / IF(Store ="Middlewich",IF('Pack desc' ="Tall",30,60),IF('Pack desc' ="Tall",26,52))
which represents the logic
Middlewich & Tall = Packs / 30
Middlewich & other = Packs / 60
Other & tall = Packs / 26
Other & other = Packs / 52
However, it doesn't work!
I know Calculated Fields can be difficult to work with, but this is crazy and I think I'm missing something obvious as I'm sure I've had IFs work like this before!
I've attached my example, also showing the values I'd expect to see.
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
Formula where A1 is the Start Date:
Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1
I have a dataset in the following manner
A B C D Value
1 0 2 3 D
2 1 3 4 D
6 2 3 4 A
The VALUE field is my problem. I need a formula so that the value field takes the max of the other four columns and returns a string (e.g. 'A' in the column header)
I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
in coverting these fields into a date field.
Example 91306 to 060913
I have encloed the file.
I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Example
See Attached
I'm currently having a hard time creating a formula to verify if the contents of 2 cells match.
Example from Spreadsheet -
Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG
When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".
I have a spreadsheet where I have two different sheets; a summary sheet, and a more detailed sheet that lists out items on invoices.
The summary sheet shows invoice number, amount invoiced, and total paid against that invoice.
The detailed sheet goes into more details, and shows whats on the invoice, and any reasons for them not being paid.
I want a formula on the summary sheet that picks up the invoice number from the details sheet, and then brings through the figures. The details sheet may have more than one line for an invoice number, so the formula needs to be able to add up too.
I've tried a vlookup, but it doesnt seem to work, and I dont think it will do what I want.
I'm attempting to sort a list of containers and their corresponding quantities (A24:B28 in the attached spreadsheet) by using a combination of an index and match formula (cells A31:A35) and a large formula (cells B31:B35) in order to have the container with the largest quantity (summed in cells B24:B28) appear below in cells A31:A35 and the corresponding quantities in cells B24:B28.
My current index and match formula works fine until I have the same quantities of different containers as I have replicated on the attached spreadsheet. When this is the case, the formula returns only one of the various container types. How do I edit the formula to allow for multiple containers with the same quantities?
I have a total of six columns divided in to 3 columns. Each column has a number in it. I need a formula to tell me which number from the 2nd 3 columns match the number or numbers from the 1st 3 columns.
EX.
123 367 ..... the #3 is a match. the number 3 is in the 1st position. I need a column to relay this information
In Column A I have first names, In column B I have last Names, in column C I have id letters,
Column D another list of First Names And In Column E I have another list of Last Names
So what I need to do in F2 is Look at the name Last name in E2 (Lets say its Smith) then look down the Last names In Column B when you find a match look at the First name on the same row to see if the first 3 letters are the same as the first 3 letters in D2 if they are then put the id that's in cell C into F2 if not ""
I've been trying for hours but no luck, also if you do manage to do it can you tell me how you get it to look at the first 3 letters and how I could change that to 4?
with Match function not matching text in this formula?
Using the below formula cell $L$11 (it contains "abc") will not match the respective cell contents in array E:E even though "abc" is in a cell within column E.
=IFERROR(INDEX('Paste In Data File'!G:G,MATCH($L$11,IF('Paste In Data File'!C:C=B14,'Paste In Data File'!E:E),0)),"")
The cells within column E are populated using a formula such as =IF(MID(A6,3,1)="a",MID(A6,435,9),"")
Column A is the data source and contains a text string.
After much trial and error, it seems the cause preventing the match is; $L$11 contains typed in text "abc" and the above formula for column E is pulling in 9 characters, not just 3.
It seems that if I can modify the entry for the array E:E to just look at the left 3 characters only, it may work.Problem is I do not know how to modify E:E accordingly.
I have a workbook with two sheets (MainSheet and Stats)
In the Main Sheet Column A has a name entered which is surname plus a christian name e.g Smith Thomas
This name could be anywhere in the A column from the bottom up
In the Stats sheet Column A has a surname and Column B has a christian name e.g Smith in ColA and Thomas in Col B. Smith and Thomas will always be on the same row but could be in any row on the sheet from the bottom up.
I need a formula which can compare the name in the MainSheet with concatinated name from the Stats sheet if the names are the same then change Cell H1 to the text "Match"
An example might be that on the MainSheet A16OO = "SMITH TOM" on the Stats sheet A2="SMITH" B2 = "TOM" .
The formula would be on the Stats Sheet in H2 which in this case would = "Match"
I have a Date in Cell A1 Page 1 and I need a formula to put in Cell A2 on Page 1 that if there is a "matching" Date in any cell in Row B1-B7 of Page 2 that the data in the corresponding row C1-C7 on Page 2 will be placed back on Page 1 Cell A2.
View 9 Replies View RelatedI have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.
I have tried with varying Index and matching formulas and I do believe that this is the way to go.
I would like to have a formula in one cell that finds records on another sheet that meet certain criteria, and produces a sum of the total quantities associated with that record. The attached workbook has more details as to what I am trying to do.
View 2 Replies View RelatedMy task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return