The Data In Sheet "details" Is Listed In Columns A, B,C, Then

Nov 16, 2008

I have two sheets, one is named "details" and other is "query". The data in sheet "details" is listed in columns A, B,C, then D is blank and again data is in columns E,F.G. (in text forms)

In the Query Sheet, wish to enter in A1 the value contained in A, B or E, F of "details" sheet, the result of columns C or G may appear in Column B1 of Query sheet and also if value is equal to C or G, the result of A,B, or E,F may appear:

=IF(AI=A1& B1,E1,F1!Details, etc. etc.

View 10 Replies


ADVERTISEMENT

Automatically Listed The Other Sheet

Mar 16, 2007

I have a list of jobs names on one sheet and there are certain ones that i would like to put on a list on a second sheet.

what I would like to do is put a "1" in a cell next to the job names that I want on the other sheet and have them automatically listed.

View 10 Replies View Related

Macros For Copying Listed To One Sheet?

Aug 8, 2014

I need a code that will copy a list in the same order from different sheets and will paste special and transpose it on the summary sheet.

View 1 Replies View Related

Excel 2007 :: Sheet Name In Show Details For Multi-sheet Pivot Table?

Jan 30, 2014

Excel 2007.

I have a workbook with a sheet per day, each sheet has a variety of 'jobs' as columns and a variety of people and kit as rows. Hours are manually input each day during the month.

Simplified version... These are two separate sheets 'M060114' and 'Tu070114'.

Monday
Dig
Drive

Dave
4
5

Bill
2
7

Tuesday
Dig
Drive

Dave
2
7

Bill
8
1

I have a Pivot Table on a separate sheet. This was created using the Wizard (ALT +D +P).

Dig
Drive

Dave
6
12

Bill
10
8

So far, lovely.

But when I double-click on a number or right-click and choose 'Show Details' I don't get the sheet names in the newly created information sheet. I get the Row label, column label and a list of the entries.

So if I clicked on 10 above...

Row
Column
Value

Bill
Dig
2

Bill
Dig
8

That's fine with just a couple of entries but with about 80 columns and 250 rows spread across a month, therefore 30ish sheets, it's difficult marrying up the 'Show Details' sheet to the actual data.

So after all that, the question. Is there a way of getting the sheet name to appear in the 'Show Details' information sheet?

View 1 Replies View Related

Unique Values Should Listed In Sheet 2 And 3 Every Time When Activated

Sep 16, 2012

I have below set of value in Sheet 1 (it has category & Name), whenever I open the Sheet 2 & Sheet 3, unique values should get automatically posted in D column..

Category
Names

Pet Animal
Dog

Pet Animal
Cat

Wild Animal
Elephant

[code]....

I have two requirements on this..

1. Unique names should listed in cell D of Sheet 2

2. Unique value of both category & name should listed in Cell D of Sheet 3

View 1 Replies View Related

Splitting Address Details Into Several Columns

Oct 18, 2006

I'm in need to split 1 column (address details) into several columns (for ex. address, city, postcode, country) I can't use the "Text to Column" function as some address will split into 5 part, some split into 6/7/8. Is there any way I could do this automatically as there're a lot of entries in my spreadsheet. Example of the data need to be split attached.

View 5 Replies View Related

EMailing Work Sheet Trying To Attach File Listed In Cell

Nov 14, 2009

I am using a macro to e-mail any work sheet with an address in A1. I would also like to attach a file that will be listed in cell E1 of that worksheet. The file is different for each work sheet that is being sent but will be listed in the same cell of each work sheet.

This is the code I am using (i got it from ron de bruin) when I use the .Attachments.Add (C:est1.txt) it works but i can not seem to figure out how to get it to read the file address in the E1 cell. The code i am trying to use is .Attachments.Add = ws.Range("E1").value.

View 3 Replies View Related

How To Get Details From Master Sheet To Template Sheet

Jun 1, 2012

I have a query on array formula

Am using this formula to get the details from master sheet to template sheet

=IFERROR(INDEX('P O Detail'!$D:$D, SMALL(IF('P O Template'!$C$4='P O Detail'!$B:$B, ROW('P O Detail'!$A:$A)-ROW('P O Detail'!$A$2)+2), ROW(1:1))),"")

with Ctrl+Shift+Enter

This is working fine based on the input cell $C$4

What am looking for is ... how to add two more input data $D$4 & $E$4 so that based on these three values I should get the required output.

View 3 Replies View Related

Transfer Details From One Sheet To Another?

Jun 18, 2014

I am trying to transfer information for the 'Master CCO CCB Document List' tab into one of the other tabs ('In Progress' and 'Complete') based off of the status. If the status is Complete, Approved, Declined will transfer to the 'Complete (Approved or Declined)' Tab. If the status says 'On Hold, Pending, 1st & 2nd Session Review needed will transfer to the 'In Progress (On Hold-Pending)' Tab.

I want the master list to house all of the entries, so when the status changes it is just copied to the other sheet and not cut and pasted.

On the 'In Progress' tab, once the status changes to Complete, Approved or Declined I would like for the item to be cut and pasted into the 'Complete' tab,

I'm stumped and really not sure if this will work properly. I've tried everything and now I can''t figure out how to add the contents from the form to the master list tab.

View 1 Replies View Related

Auto Update Details In Next Sheet

Aug 22, 2014

I create daily report for dispatch documents to our various branches. Now I am looking for formula (or formula is not possible then VBA code) that will auto update document number and dispatch date in "output" sheet.

Find attached sheet with desired output.

View 5 Replies View Related

Inserting Contact Details From Another Sheet

Oct 15, 2008

I have a workbook which I use for invoicing customers. Sheet one is the inovice template. Sheet two is the contact details of all my clients (i.e. column headings are Name, Address 1, Address 2, Town/City etc.).

In the invoice sheet I would like to be able to select the name of the client from a dropdown so that it automatically fills in the address into the other cells of the invoice.

View 3 Replies View Related

Copy Of Some Cell Details To Other Sheet

Nov 27, 2008

My worksheet is attached. It asks the name whenever we will open this workbook. The same would be written autometically in open by cell with date & time.Then it ask "whether its new project or not"? If yes then then approved by cell & date-time would be blanks or if no then it will show the last approver's name, date & time. I need whenever anyone approve this in case of NOT NEW Project so the name of the approver & Date & time should be recorded in the another sheet name "record" along with the sr. nos. But the record will be blank whenever we will select "yes" in "whether its a new project". I want to make it password protected & password is "007".

View 14 Replies View Related

Extract Details From All Sheets To One Sheet

Dec 14, 2007

I have in Sheet 1, in column A, the list of employees who are existing, in column B their mobile numbers and in column C the amount of mobile usage for the month June 2007.

In Sheet 2, in Column A, I have the existing list of employees of June 2007, some new employees who have joined, in Column B their mobile nos. and in Column C, amount for July 2007.

Similarly, for August, Sept. Oct. 2007 etc. In each sheet, the old employees are copied and new ones are added alongwith their details. It so happens, some employees leave the organization in between in August, Sept. or Oct. 2007.

Now, I want to extract in column A, the list of all the employees who were existing initially in June 2007, who have joined later on and who have left in between, their corresponding mobile numbers in column B and their amount in column C and show them monthwise in one single sheet side by side. E.g....

View 9 Replies View Related

Create Sheet For Details Of Each In Table

Jul 6, 2007

I have a work sheet(sheet1) that contains 700 people's information. Each row contains information for one person. Let's call the first person A. I have to copy certain columns of person A's response to another work sheet(sheet3) in the same file. Then, copy person A's e-mail address and save the sheet as person A's e-mail. Repeat the same steps for the next respondent.

I have created the macro for person A already (including the saving part). Now the question is how to write the loop in the macro.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/5/2007 by Jean Kuo
'
'
Range("Q4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-4]"
Range("Q13").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-9]C[-14]"
Range("Q14").Select
Sheets("Sheet2").Select
Range("D4").Select
Sheets("Sheet3").Select
Range("Q14").Select
Sheets("Sheet2").Select
Range("D4").Select
ActiveCell.FormulaR1C1 = ""
Range("C4").Select
Selection.Copy
Range("D4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C17"
Range("C4").Select
Sheets("Sheet1").Select

Range("T1").Select

Sheets("Sheet3").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-10]C[-13]"
Range("Q15").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-11]C[-12]"
Range("Q16").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-12]C[-11]"
Range("Q17").Select
Sheets("Sheet2").Select
Range("F4").Select
Sheets("Sheet2").Select
Range("G5").Select
Sheets("Sheet3").Select
Range("Q17").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-13]C[-10]"
Range("Q22").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[42]"
Range("Q22").Select
Selection.Copy ...

View 3 Replies View Related

Create A Macro That Find And Calculate Based On Details Of 4 Columns On Same Spreadsheet

Jul 21, 2012

create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.

Basically, here is what I need to do:

1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.

2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.

3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.

Attached is the spreadsheet..

View 9 Replies View Related

Details Of A Person Say Age And Put The Details In Another Table

Dec 9, 2009

I was interested in getting Excel to look up details of a person say age and put the details in another table under the correct heading.

E.g. Fred Bloggs age 25

Would look like this

First Name Surname 16-24 25 - 49

Fred Bloggs 0 Tick or something

View 9 Replies View Related

Creating Formula To Change Details On A Sheet Using Userform?

Feb 21, 2014

On the attached spreadsheet, ormula on 'UserForm2'.

When I hit the 'Submit Changes' button I want a formula so that it will look up the product row in Product List (Sheet 1) that is select in the ComboBox1.

Once this row has been found I want the values in TextBox1 to be copy and pasted into Column D of that row and value of TextBox2 to be copy and pasted into Column G of that products row.

View 10 Replies View Related

Linking Data (look At Sheet 1 And Populate The Date Columns With The Guest Name From Sheet 1)

Jan 7, 2009

Sheet 1 contains a column titled "name" and 6 columns to the right of name titled "sat", "sun", "mon"........ the col titled name is not the first col in the work sheet.

In each row I enter the guest name under the name col and I enter a room number (example: 1A, 1B, 2A, etc...) in the column to the right that coresponds with the day the guest will be staying.

Name Sat Sun Mon Tue Wed Thu Fri

Mr. Smith 1A 1A 1A 1A
Mr. Jones 3B 3B 3B
Ms. Tiller 4A 4A 4A 4A 4A 4A 4A

Sheet 2 is in the same workbook and looks something like this:

Room # Sat Sun Mon Tue Wed Thu Fri
1A
1B
2A
2B
3A
3B

I want it to look at sheet 1 and populate the date columns with the guest name from sheet 1

I tried this formula =IF('Sheet1'!G:G="1A",'Sheet1!$F:$F,"Available")
It seems to work on the first row but I have problems with any rows below that. Basically it acts like there is no data in the rows below.... I think the fact that the names on sheet 1 are not alphabetical is creating part of my problem? I tried a lookup formula but it seems like it has to be the first column in the worksheet and it has to be alphabetical to work like that.

View 4 Replies View Related

Excel 2010 :: Match 1 Cell And Column In Sheet 1 To 2 Columns In Sheet 2 Return Data From A 3rd

Jul 23, 2012

I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.

My attempt is in cell D2 on sheet1.

View 5 Replies View Related

Enter Form Details And Check For Data

Sep 19, 2009

On the attached workbook i have a simple form with text boxes and a enter button and cancel button.

So far, i have only got as far as the cancel button closing the form. I want to know what i have to do to enter each of the fields in to the cells written on the form. Also when i do press the enter button, if any of the fields are not entered then a message box must appear notifying the user all the fields are not entered and then finish by taking them back to the form.

View 8 Replies View Related

Summarize Data From Details Workings By Given Single Input

Sep 6, 2013

I have an excel file having the month wise model cost breakup e.g.Prod.Qty,CKD,Local etc.My problem is that I want to make a summary in a separate sheet showing the Input i.e. Model Code.If I give that input then automatic summary should be prepare.

Please refer attach file: Costing Table.xlsx‎

I this file,having 2 sheets.One is details and other is Summary sheet.

focus the cell no.C4 in the summary sheet.When,I give the model code then the summary should be ready from the details sheet data range, month wise.

View 3 Replies View Related

Dynamic Named Range For The Product Name On The Product Details Sheet

Dec 24, 2008

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

View 5 Replies View Related

Copy Only Data In Some Columns To Another Sheet

Jun 1, 2009

I am trying to copy rows that match a date range but I only want some of the columns to be copied to a pre-defined sheet.

For example, if the row is selected as a match because it is within the desired date range, I want the data in columns 'A-F', 'I-J','N-O' to be copied to the sheet 'Report' starting in 'A3'.

View 12 Replies View Related

Find The Last Row In Sheet Containing Any Data In The Columns

Jun 26, 2009

I 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.

View 9 Replies View Related

Converting Data In Two Columns Into A Row On Separate Sheet

Oct 20, 2011

I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.

Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6

Ideally the row will look like.

Example - Sheet "Status WS"

Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date

There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.

View 4 Replies View Related

Auto Hide Columns If Data Sheet Is Empty?

Mar 6, 2014

I've created an excel workbook to automatically create a report for my work. All you have to do is enter the data into the "DATA" worksheet. Easy. Only problem is that someone can easily ruin it if they try to run it when the "DATA" sheet is empty. I figure that if I can get two columns hide when it's empty and that would solve my problem. I've tried several VBA codes, but they don't work. Also, everything except the "DATA" sheet is protected to prevent accidently changes. This is the latest code I've tried:

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "DATA!$P$5" And Target.Value = 0 Then
ActiveSheet.Unprotect ("password")

[Code].....

View 4 Replies View Related

Export 2 Columns Data From 1 To 30 Sheet To One New Excel Or In Different New Sheet1

Aug 29, 2013

How to export data like name and percentage from all the sheet (30 sheets) to a new file or in a different sheet.

E.g

Name project target ,weekly %, and monthly percent% ,present, absent are there in all sheet but i want to export only Name and monthly percentage to different excel or different sheet in same excel.

View 2 Replies View Related

Userforms - Populate Data On Specific Sheet And Columns?

Mar 25, 2014

i'm having a hard time with trying to populate data from a userform to a specific column under a specific sheet. I've attached the file if you want to look at it. The file is basically a exam type with user logging and auto score computing.

The file is made of 6 sheets, 1st sheet is the log-in page where takers are required to enter their ID number and last name. 3 sheets of dummy test items, an Admin sheet that computes the score per test item and Database which I'm trying to compile all data in one column.

What I'm trying to do is that when they enter their ID and Last Name and then answer the 3 tests, they will be recorded in the database sheet in 1 column containg their ID, name and all the test results so that checking,grading and identifying who took the test would be easy.

So far I think I've done the part for the scores but I can't seem to get the userform for emp# and last name to populate on the field marked in the database.

View 1 Replies View Related

Copy Data From One Sheet To Another Based On Multiple Columns Of Criteria

Jul 27, 2013

I am working on a project to automate the import of data and fixing the formatting.

The data contains the date in column A and time in column B. The time is in increments of 15 min for each day. The rest of the data is in columns C:F. Some of the dates don't have all of the times within the day (so instead of 96 rows of data per day, there maybe 80 rows). I have a second sheet of just dates and times that has the correct amount of rows for each day. What I need to do is have the rows data that matches in both the date and time column from sheet 1 copied over to the corresponding date and time on sheet 2. Blank data rows on sheet 2 are fine. I have found some stuff on .Find, but the stuff I found was for specific searches.

Example

Sheet 1
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 01:00 AM 9 10 11 12

Sheet 2
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 12:30 AM
7/27/13 12:45 AM
7/27/13 01:00 AM 9 10 11 12

View 1 Replies View Related

Check Data For Changes In Multiple Columns And If Changed Copy To Another Sheet

Mar 8, 2014

This is my basic setup, each piece of equipment will have more than one routine to be performed:

Equipment Type
Make
Model
Serial
Location

To Be Performed
Frequency
Last Performed
Performed By
Next Due Date
R1

[Code] ..........

I want to track changes on "Last Performed" and "Performed By" and, if changed, copy both values to a separate "log" sheet. In total, there could be up to 10 routines for each peice of equipment. That means I would need to track 20 columns for changes. I would like this check to be done on save because that ensures that the user is satisfied with their changes and keeps the log from being flooded. And lastly, it needs stored in the log sheet in a way that I can identify which piece of equipment it was for and which routine was done.

View 1 Replies View Related







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