I am very green with Excel. Can anybody tell me what 'data sheet' means and where to find it? =If('data Sheet'!$G$11=1,'data Sheet'!$Bi$197,'data Sheet'!$Bj$197)
View 5 RepliesIs there a list of error numbers and their meaning?
View 9 Replies View RelatedIn my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies View RelatedThe data from the Data sheet is copied over to the Report sheet with formula.
Basically, what I need to do is If statement:
-when there is no any data in csv file (so the Data sheet will be empty);
-the range (A7:N1000) to be hidden (on the Report Sheet)
-the Report sheet to be saved
and also...
I would like to ask, how to select from the Data sheet, the rows with Data only. I copy them over with formula instead of macro- but would like to hide all the empty rows on the report.
Option Explicit
Sub Update_Report()
'
' Macro1
Dim extract1 As String
Dim dReport As String
Dim rSheet As String
Dim dSheet As String
extract1 = "LeadSheetAll_0001.csv"
dReport = "Appointments.xls"
rSheet = "Report"
dSheet = "Data1"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
on sheet 1 in cell C39 i need to find data from sheet 2 in row B that is 534 as in C32 is 11.2 and on sheet 2, 11.2 equals 534
View 2 Replies View Relatedlook at the attached file. This might be the perfect job for a macro. I am trying to find the SKU's from sheet 1 in sheet 2 and subsequently mark the matching SKUs in sheet 2. Ideally, I would like the SKU's that could not be found to be set to Quantity "0"
View 2 Replies View RelatedI would like to find the last row in my sheet containing any data in the columns A:O and select the cells in the range of A:O to that last row. I would then like the code to name this selected range as IMPORT.
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
I need to firstly find data in the main sheet which relates to the activecell and then I want to copy and paste that data into another worksheet.
The simplest of ways I guess would be the .xldown function however this seems to copy the blank cells as well as the cells with data, which is no good. I have therefore written code as below which finds the first cell with data, copies and pastes, then loops.
How do I set the loop to look at the next cell down from the initial copied cell and if there is data in it to paste it into the cell underneath the one I have just pasted into?
Range("D3") = ActiveCell.Offset(0, 1)
SheetName = Range("D3")
ActiveCell.Offset(0, 2).Select
[Code]....
i want user using userform to search for the wanted data then the displayed data be copy to another new sheet.. and be able to print!
hope the idea is clear!
i will attach the file in case someone wants to show me the example...
I think I am closer but how do I get all of the data from A2 in the origination worksheet and then copy it to the last blank cell in column A in the destination sheet?
Code:
Sub test4()
Dim lastrow As Long
Set StartSheet = ActiveSheet
'MFG PART NUMBER
StartSheet.Range("R2").Copy
Workbooks.Open Filename:="D:\_DoxaMobiusServtrax Import Template"
[Code]...
I'm trying to create a database which can analyze data for my thesis.
The idea is the following:
I have all data saved on multiple work sheets in one workbook.
I would like to use the find function and when I search for specific data this is either summarized in a new sheet or else can be printed.
I have made up 7 worksheets that are completely identical with exception of course to the data they contain. I have a master sheet that contains some of the data from each of the other sheets that will be shared between them. Things were working very well until I discovered that some data was duplicated on a couple of the sheets; and I am in need of adding more worksheets.
The formula I am using for this will not allow me to have more than the 7 worksheets. It is an IF statement array. So now I am wondering how will I be able to add these extra sheets. I would like to use my worksheet names in a range but have evidently done something wrong and could not get that to work before, ending up with this very long 'IF'. !Can worksheet names be used to create a named range?
Also, I would like to know if there is a way to prevent duplicate data on these sheets. I am using conditional formatting on each sheet now, but that of course only works on the current sheet.
Im going to try to make this as clear as possible. I cant use my actual data because it wouldnt make any sense to anyone so Ive made up an example problem. Here goes...
Lets say in Sheet 1 I have two descending columns of data. Column A is MODEL of Vehicle (Civic for example). Column B is vehicle identification number (xxx for example). Sheet two has 4 columns of data, but only one is really required for this example. Cell A1 is the MAKE of vehicle (Honda for example). Directly below that in Cell A2 is the MODEL of the vehicle (Civic). There are then a few rows of empty space until it gets to the next vehicle MAKE and MODEL.
So in Sheet1 there is a long list of MAKE's in ColumnA and VIN's in ColumnB. Sheet2 Has a long list of MAKE's and MODEL's in ColumnA and random data in other columns.
What I want to do is assemble a Macro to start in Sheet1-A2, read the MODEL then copy the corresponding Vehicle Identification Number in B2. I then want it to go to Sheet2-A2 and start searching downward until it comes across a matching MODEL. Once it finds the match I want it to step downward 2 cells and paste the Vehicle Identification Number. Then return to Sheet1-A3, and repeat the process until EOF.
In the attached sample sheet, Col B has data that will more often than not be duplicated. Each Line will have a corresponding value in Col L.
What I need to be able to do is this:
Starting from row 2 (important) is find duplicates and then place the corresponding figures for each from Col L as a Sum into Col N.
So '45678' appears twice in Col B and the Sum of the figures in Col L for '45678' is 1, so 1 will now show in Cell N2.
'55685' appears only once in Col B so it will have the "Sum" of 12 in Cell N3.
'99954' appears 3 times in Col B and has a sum of 9 from the Col L values so that will be displayed in Cell N4 and so on.
Hammer Sample #2.xlsx‎
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies View RelatedI was trying to figure how to write a formula that would return the contents of a cell in a table.
Here is the situation....
I have a 10 row by 10 column "grid". the top and left side of the grid contains a random number from 0 to 9. The numbers do not repeat.
It would remind you of a block pool.
If I have two numbers, 0 to 9 , is there a way to return the cell that corresponds to the intersection of those numbers in my table?
It would be both a Vlookup and an Hlookup which I can't do.
I have one workbook (workbook A) with data in it (the data is never in the same place i.e. same cells) and one I want to send data to (workbook B)
In workbook A the data is displayed as follows:.....
I need help creating a formula to find data in one column that starts with different letters and counts them up.
For example one column would contain the words amoung others:
DPDE
DNPD
EPRO
EOTH
YBRC
YUND
In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.
The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.
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 RelatedI'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
I am trying to look up a numerical value in Sheet 1 Cell:F7 and find that value in Sheet 2 Column B, then offset 29 columns in the row where the value was found. The number will not have any duplicates within Sheet 2.
View 8 Replies View RelatedI have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2.
Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" .
sheet1
DATE ABC DEF
2007-04-01 £55.60 £61.13
2007-04-02 £56.33 £60.27
2007-04-03 £52.14 £62.58
2007-04-04 £51.69 £61.28
sheet2
CUSTOMER DATE RATE
DEF 2007-04-04 This should be £61.28
ABC 2007-04-02 This should be £56.33
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I have a large database of customers with their contact details and address etc.
With the database the main thing we analyse is source of the customer and sales person it was given to.
I have separate sheets of which narrow the database down by source. Such as telephone lead, website lead etc.
At the moment i am currently entering the data into the main database sheet and then have to enter again on the lead source sheet.
Is there any formula for a sheet for example website sheet in which it will look up a column in the main database (lead source column) and pull everything single row of customer details that have came from the website.
I would ideally like this to just continuously do this every time i enter data into the main database it automatically gets added to its separate sheet.
I have 2 different sheets one is my check book register and the other is a summary page. Both have different categories for example [sheet 1 "register" (gasoline = A) (food = F)] [sheet 2 "summary" (Auto = A)
I think this is how I want it but I need this to work no matter what category my debit or credit falls in. If date is "January" and category is "A" then copy "debit" to sheet 2 under January Auto. then sub total all as designated on second sheet.
If date is January and category is "m" then copy debit to sheet 2 under January mortgage, then subtotal as designated on second sheet. I have attached an example of my spreed-sheet so far as you can see I am struggling getting the amounts to go into the correct categories. Also sometimes there may be more than one of the same type of transaction in the same month they need to be able to go into the same cell and be totaled as well.
Currently i am using below formula to get the data from Sheet 'Dec,11'
I have sheets from Jan,11 till Dec,11 and I would like to give a cell reference A1 where I will enter the Sheet name and I should get the data of that sheet.
=COUNTIFS('DEC,11'!$AH$6:$AH$4457,$BE$5,'DEC,11'!$T$6:$T$4457,A6)
Actually I tried entering like this
=COUNTIFS('A1'!$AH$6:$AH$4457,$BE$5,'A1'!$T$6:$T$4457,A6)
But its not working.
I have a workbook with two tabs in it. The first tab (2004) contains a current price sheet. The second tab (2007) is an updated price list from our vendor with added part numbers so the rows are not corresponding to the 2004 tab although the columns are exactly the same. I have changed many of the descriptions (Column C) in the 2004 tab but need to use the new sheet because of the added part numbers.
I would like to find an easy way for excel to find the matching part numbers in Column A of both tabs(sheets) and return into column C in the 2007 tab(sheet) the description for each part number from the 2004 tab. For those new part numbers in the 2007 tab the description should remain (as there will not be one in the 2004 tab) but I would like for it to somehow flag me (color change) so I know which ones I need to manually change.
The columns in each tab (2004 and 2007) are exactly the same. Column A is empty, B is the part number, C is the description and D is the Page number.
Can I create a formula to make this easy? If so how? Data in tab 2004 is in Rows 3 to 2157 and the data in tab 2007 is in rows 3 to 2532.