I have huge set of data listing almost 20 columns and 400 rows..i am trying to apply a formula for one of them..
Condition: Onshore or Offshore
Data to be retrieved: list of locations at onshore and offshore
Result expected: If a particular cell is selected as onshore then allow user to select only Onsite locations same for offshore
I am trying to use COUNTIF function to retrieve data to a "Report" tab on my workbook.I need it to be count if as I have 2 criteria whether or not the value in the cell should be counted toward the sum (one is text basically saying "Y" and other the date). Now the formula worked fine when use hard data such as this:
=COUNTIFS('Inspection Report'!$B$3:$B$1048576,"*" & "06/05/2014" & "*",'Inspection Report'!E3:E1048576,"Y")
OUTCOME: 47 (which is correct)
See how I used the date there, I typed it myself. However the end user should not be able to have access to the formula, so I used a cell with drop down list and the date is stored there. So following the logic, my new formula should be this:
=COUNTIFS('Inspection Report'!$B$3:$B$1048576,"*" & C2 & "*",'Inspection Report'!E3:E1048576,"Y")
OUTCOME: 0 (which is incorrect)
how the date is stored. By following my logic this should work as the cell only holds a string value and should be used like this in to calculate the result. if I need to use only the value of the cell in a formula like this should there be any conversion so excel understands what I am trying to do?
In column A I have client names and columns B to P I have numerical data and in column Q I have a formula which gives me a percent, i am trying to think of a way/ a function that will return for me the client names when the value in Column Q is >=100%. It seems like it would be some type of reverse Vlookup.
View 4 Replies View RelatedIn a sheet from A:E, the headings on the first row are:
ID, Name, Gender, and Age.
I enter the data in sheet “DB” and I want to retrieve the data in sheet “Report”. I want to lookup the data range for each ID listed in Column A and retrieve all data from the matching row in a report form/look.
I have tried to use the vLookup formula, but when defining the range of the column (A2:A), it would return “0”. When defining only the range that contains data, it displays the correct information....
I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.
Issue: All I would like to do is for a NESTED IF Function to be able to say this:
1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square
2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.
3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.
If 'Column A' - 4" Square, Then 'Column B' - A
If 'Column A' - 5" Square, Then 'Column B' - B
& So on...
But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:
User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A
User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B
& So on...
Currently to do the above function, I have utilised the following Excel NESTED IF Function:
[Code] .....
& The following happens:
1. User inputs data into 'Column A'
2. The NESTED IF Function then provides an automatic response.
3. However it relies on the data being inputted without the ' " (Quotation Symbol)'
4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"
Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.
Example - IF Function.xlsx
I have Column A sample below:
Column A
Row 1 WG32127
Row 2 WFH3040 / 3920
Row 3 WFBF3709/3919
Row 4 WG32156/3915, 3911, 3920
Row 5 WG32148129/3915, 2922
I'm trying to use the Left Function to get only the Alpha-Numeric codes only. I'm having the following problems.
1) The alpha-numeric codes are not a standard number of characters (they can vary anywhere from 4 to 10).
2) In some cases the alpha-numeric codes are alone in the column
3) In other cases they are with other numbers with back slashes separating them
4) In the cases where there are back slashes there are spaces, commas and other numbers as well.
Is there a way to use the Left Function to retrieve only Alpha-numeric codes only.
In the attached spreadsheet I need a formula to extract the value from column C that matches the date in Column E where the date in column E is equal to or falls between the dates in Columns A and B.file now attached.
View 4 Replies View Relateda way to select which ones to print based on the given condition. I have a store list, and on this list I provided the store number and their authorized vendors. I have a weekly spreadsheet that has all our vendors cost/retail changes. On this spreadsheet, the vendor numbers are on column A. I was hoping for something, like maybe provide the store number to print, and it will print only the vendors that that particular store carry... We have a total of 106 stores, so automating this process would definitely save us a lot of time.
View 9 Replies View Relatedhow to apply this function to my sheet given below. In this budget sheet, I want to create a graph which would show all the items percentage (item by item) with the values given in columns C & H only, leaving out the lines which are empty or bear 0 values.
View 2 Replies View RelatedHow to open a fresh workbook and have information automatically being pasted in, based on a checkbox trigger. I don't know if I have explained well or not but essentially it is this:
W5 is the checkbox with a tick and a cross. When the cell is ticked I want to specicy cells on this record to be brought over to another tab.
I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2
On the 2nd tab I have the shift schedule, which tell which shift is working on particular days
Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am
Feb
1
Shift A
Shift C
[Code] ........
Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.
I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time
I have a spreadsheet with ~35k rows of data. This isn't too easy to explain I have the following data (example).
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc.
Jo Bloggs 12345 A1 1 1 0
Jo Bloggs 12345 A2 1 1 0
Mark Jones 89101 A1 1 1
Mark Jones 89101 A2 0 1
For each user with data level A1 I wish to add an X if the same user has a corresponding data level of A2 so the result should be,
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc.
Jo Bloggs 12345 A1 X X 0
Jo Bloggs 12345 A2 1 1 0
Mark Jones 89101 A1 1 X
Mark Jones 89101 A2 0 1
I would really love a formula or macro to be in existance that would save me from trawling through all this data and going blind.
I have written a code for equal distribution of Budget Data based on condition.
The user will enter annual budget number in Column K (highlighted in Green) and based on condition in Column N (highlighted in Yellow), the data will be divided by 12 and it need to be equally distributed from Colum P to Column AA. The condition for distribution is 'Y' (Yes)/ 'N' (No).
I have also attached the sample file.Budget_Input Form.xlsm
Attach is a sample of my project. I have to loop through B9:B80 in sheet("Raw Data") and capture the value of each cell. EG:
* if Value captured is 120A --> check with sheet("SP14F") from the row which contains the same value.
* i have to check that the aperture, outer and inner values of Layer 120A and so on..of the Raw Data sheet is the same as that of in the SP14F sheet.
* if the values are not the same, change the font to red
i understand that this task requires looping and arrays.
I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.
View 3 Replies View RelatedI have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
I have a spreadsheet using an Input Form (user cannot get to the spreadsheet to enter data - they must use the form). This is working well.
What I need to do now is allow the user to retrieve data that is already entered. Example: Spreadsheet has 100 unique records (rows). In each is ID, Name, State, Dues, and about 20 other columns of information.
I need to allow the user to key the ID # and then up pops all the other information for just the ID provided. I will also need the ability for them to then change that information if needed.
So if they key 13WIL they will get Wilson, TX, $50, etc. And then they can make changes to any field needing a change. Add a phone, change and address, etc.
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
I have created a userform with multipage, has two page that add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes
[Code].....
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies View RelatedThe formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.
View 3 Replies View RelatedI need to match and retrieve data.
If cell A25 matches B3:B19, B25 should pull the Name and C25 the Cost.
I have attached a spreadsheet.
This code should retrieve data from sql server to excel sheet
filtering according to the "where" statement
its a modified recorded macro.
In the attached file for ODM AND ACCOUNT NAME ACCOUNT PAGE TWO PAGE C1 CELLS MEVCUT.BEN to the 2009/38 VALUE ODM PAGE AVAILABLE ON A TIME I ENTER: THE ENTIRE ROW A SUTUNUNDAKI of the SAME VALUE FILE ACCOUNT INFORMATION In the LISTELEMEK would HEATING
View 4 Replies View RelatedI still do not have my dummy files ready but wanted to start the discussion on Retrieving Specific Data form Txt file.
Later I will post sample files.
For now, I will appreciate any ideas on how can I refer to specific place in the txt file and get the relevant information to excel spreadsheet.
The criteria for searching the text file must be:
1.Look for specific ABC
2.right below must be a date: 01/20/2010
3.When those two criteria are met then go down and find the first occurrence of the word: “Test”
4.When you find the word, on the right of this word must find numbers
5.Get those numbers to excel
I have 4 columns of data:
Employee; Invoice #, Invoice Total ($), Month of Invoice
I am trying to create a simple report that will display Invoices, By Employee, for a given month. I have tried using the following VLOOKUP statements within an IF statement:
=IF(VLOOKUP(H2,S1DATA,4,FALSE)="OCT",VLOOKUP(H2,S1DATA,2,FALSE),0)
"S1DATA" is the table with the data I described above. I am trying to retrieve data for the month of October. When I use the above statement I get the information that I want, but when I copy the formula down (matching the employee name in H2, H3, H4... etc I continuously get the same invoice information.
I have a webpage with a form, where user can enter their login info and a location number.
- Based on these information I would like the location number to be fed into a Macro, when the user was authorized. The Macro then extract required information and give it back as txt-file ( this Macro is already written).
- The result txt-file need to be convert to an excel file and be availabe on web for downloading.
- -> All of this process must be automatically done. <--
My question is, which language (VBA, PHP, Perl,...) should I use to do the work and how can I do it?
I am updating one sheet within a closed workbook with information from one sheet in another closed workbook. I have been using a connection string similiar to this: "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:ExcelData2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""....
My question is, how do I make a worksheet that is in a closed workbook active. And to be more specific I need to change the name of the file I am saving to and I was hoping to do something like "activeworksheet.saveas=". If you happen to know another way that relates to the way I am connecting and updating information( SQL statements), could you let me know.
