Retrieving Data from a Column / Row to populate a cell

I currently have data supplied to me in an Excel spreadsheet that I to
transfer manually

I would like this to populate a cell reference, the problem I have is that I
cannot gather a train of thought to obtain the data from from a specific
row/column.

There is a sample of the data supplied to me below
Name            In            Out        Int
Greg                165        108        29
09 July 2003     42         34         9
14 July 2003     44         19         4
16 July 2003     40         40         8
17 July 2003     39         15         8
Andrew            125        43        9
15 July 2003     41         13         2
16 July 2003     42         13         5
17 July 2003     42         17         2
Tony                  114        99        48
01 July 2003     37         54         15
10 July 2003     33         16         20
17 July 2003     44         29         13

The data I would like to record would be as follows for a selected date
i.e. If I wanted to record data from column 4 on Date 16 July 2003

Greg        8
Andrew    5
Tony

I hope I have explained this clearly enough

Thanks in advance

Michael


0
mmc308 (4)
7/18/2003 8:25:48 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
601 Views

Similar Articles

[PageSpeed] 55

The following macro inserts a new column, calculates the name for that 
row, and turns on the AutoFilter. You could filter for a specific date, 
and copy that data.

'==========================
Sub FixData()
Dim r As Long
r = Cells(Rows.Count, 1).End(xlUp).Row

   Columns("A:A").EntireColumn.Insert Shift:=xlToRight
   Range("A1").Value = "Name"
   Range("B1").Value = "Date"
   Range("A2").Formula = "=IF(A1=""Name"",B2,IF(ISNUMBER(B2),A1,B2))"
   Range("A2").AutoFill Destination:=Range("A2:A" & r)
   Range("A2").AutoFilter
End Sub
'==========================

mmc308 wrote:
> I currently have data supplied to me in an Excel spreadsheet that I to
> transfer manually
> 
> I would like this to populate a cell reference, the problem I have is that I
> cannot gather a train of thought to obtain the data from from a specific
> row/column.
> 
> There is a sample of the data supplied to me below
> Name            In            Out        Int
> Greg                165        108        29
> 09 July 2003     42         34         9
> 14 July 2003     44         19         4
> 16 July 2003     40         40         8
> 17 July 2003     39         15         8
> Andrew            125        43        9
> 15 July 2003     41         13         2
> 16 July 2003     42         13         5
> 17 July 2003     42         17         2
> Tony                  114        99        48
> 01 July 2003     37         54         15
> 10 July 2003     33         16         20
> 17 July 2003     44         29         13
> 
> The data I would like to record would be as follows for a selected date
> i.e. If I wanted to record data from column 4 on Date 16 July 2003
> 
> Greg        8
> Andrew    5
> Tony
> 
> I hope I have explained this clearly enough
> 
> Thanks in advance
> 
> Michael
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd (439)
7/19/2003 11:15:50 AM
Reply:

Similar Artilces:

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

Editing external data via ODBC
Hi, I'm in the process of transitioning an excel/access (2003) based solution to a web-based one. Right now, we have a dozen or so separate spreadsheets and a couple of access databases that get updated several times a day. I've managed to import most of the data into a mysql db which I've tried to normalize as much as possible. This will be the backend db for the webapp. I've set up an ODBC connection from excel to the db and it works like a charm; a few query tweaks and I can reproduce the data from all the separate spreadsheets. But now, how about going the other way? ...

How to view each row as a "record" or 1-screen ??
We have a spreadsheet with about 26 columns per row. We want to display each row as a record or "screen" Is there anyway to do this without becoming a VBA programmer? Or, is there any sample code we could use as a model and tweak? thanks for any help. You can use the built-in data form: Select a cell in the list Choose Data>Form tmb wrote: > We have a spreadsheet with about 26 columns per row. > > We want to display each row as a record or "screen" > > Is there anyway to do this without becoming a VBA programmer? > > Or, is there any samp...

Missing row #2
A user has deleted a row that had an amount in it. Her rows are now number, 23, 24, 25, 27 (so row 26 is missing). Whenever she tries to total columns or rows, the number that is in that missing row is calculated in. How do I retrieve that row, or clear out the number? TIA. Kim Select rows 24 and 26, the whole rows and goto Format>Row>Unhide. -- HTH RP (remove nothere from the email address if mailing direct) "Kim" <Kim@discussions.microsoft.com> wrote in message news:37603B87-33DA-4EB0-AC54-68F919F4A8B8@microsoft.com... > A user has deleted a row that ha...

random selection of rows
I have a master list of all of our cases--there are approximately 20000 rows, each row representing a single case, and each row has 13 columns of data. I filtered the list, for example, to see all of the active cases for a case manager. Suppose that filter produces 117 cases (there are of course a different number for each case manager). I want to create a macro or use a formula to randomly select one of these rows as a way to randomly select cases for audits. Is there a way to do this? Thanks. Hi Bradley, The following code makes a random selection from the filtered ...

Cell Contains Data.
How can I tell a cell that IF a cell contains data (numbers, text, whatever) do x or y. How can I identify data?? I don't want to tell the function to look for a specific number or text, just data, just something in the cell... =IF(A1<>"","Data","No Data") "GEM" <GEM@discussions.microsoft.com> wrote in message news:07916799-F312-4E89-A926-59356B74ED89@microsoft.com... > How can I tell a cell that IF a cell contains data (numbers, text, > whatever) > do x or y. How can I identify data?? I don't want to tell the function ...

Active cell highlight
Hi, I would like to know if there was a way to have an active cell always in yellow or any color. Example: If the active cell is moved around, the active cell will be always in yellow. Thank you in advance maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm E wrote: > > Hi, > > I would like to know if there was a way to have an active cell always in > yellow or any color. > > Example: If the active cell is moved around, the active cell will be always > in yellow. > > Thank you in advance -- Dave Pet...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

Counting specific test in a column in another worksheet that fall between entered dates
Hi, I'm trying to count the amount of times a txt string appears associated with a date on a different worksheet between two separately entered dates on the sheet were the calculation is being performed. Im using this formula but it only returns the 'value if false' even-though the logical test result is true i.e Derbyshire has been entered into the sheet on the 09/09/09 : =IF(('Tests 1'!$D:$D="Derbyshire"),(COUNTIF('Tests 1'!A:A,">="&$N$22)-COUNTIF('Tests 1'!A:A,">"&$O$22)),"") Thanks for any in...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

Data Validation
I have noticed the "Ignore blank" check box in my Data Validation window (XL2003), but i cannot figure out what it does. Nor can i find an answer in the Help files. Just what does this option do? Thanks, Tonso Ignore Blank: If checked, blank entries are allowed. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Tonso" wrote: > I have noticed the "Ignore blank" check box in my Data Validation > window (XL2003), but i cannot figure out what it does. Nor can i find > an...

retrieving email from server
How do you set the timing for retrieving email from server. I would like to have Outlook retieve them several times a day, automatically ...

Import of Data into RMS 2.0?
We will be converting to RMS soon. Is there anyway we can import our old sales history(Monthly) into RMS 2.0? Hi newbie. I am not aware of a free utility that will import all data, including sales transaction history to your RMS system. Retail Realm has an Import Tool that will do this if your current system is Microsoft Point of Sale, possibly POS 2009, but it is licensed (meaning you need to pay for it). Otherwise, I believe you are limited to importing items, customers, suppliers, and UPCs as Aliases, no history. HTH..... "newbierms user" wrote: >...

Choose highest value in a row
How do I tell excel to choose the highest value in a range of cells? A B C D E Highest 1 3 2 7 5 7 help please. =max(a1:e1) will return the highest value. sohum.shah@gmail.com wrote: > > How do I tell excel to choose the highest value in a range of cells? > > A B C D E Highest > 1 3 2 7 5 7 > > help please. -- Dave Peterson =MAX(A1:E1) returns 7 in your example. Gord Dibben MS Excel MVP On 23 Oct 2006 10:47:09 -0700, sohum.shah@gmail.com wrote: >How do I tell excel to choose the highest valu...

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

Conditional formatting for rows containing text
Is it possible to conditionally format for rows containing certain text? I receive a weekly report of hours and costs from our financial department that I need to sort and enter information into a spreadsheet for actuals vs. budget. This information comes to me with column A looking similar to this: 1234 Sum of Total Hours 1234 Sum of Total Cost 1235 Sum of Total Hours 1235 Sum of Total Cost 1236 Sum of Total Hours 1236 Sum of Total Cost 1237 Sum of Total Hours 1237 Sum of Total Cost with the #'s being different ch...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

convert text to predefined number in single column
Hi all, Greeting to all of you out there! I am a newbie to this forum and now I have a question here. I have a worksheet which contains thousands of records. Within a particular column (column "N") there are single character in the cells throughout the records (eg. "M", "S"). My question is how can I convert from "M" to "1" and "S" to "2" using formula? Kindly advise. Thanks and best regards. Eric --- Message posted from http://www.ExcelForum.com/ Use a help column, =LOOKUP(N1,{"M","S"},{1,2}...

data available to MS Access
I'm not able to see the multiple entries of a sharepoint item with a mulit-line text column --I can only access the text of the last entry. With other multi-value columns (attachments, single-line text, numbers, etc), I don't have a problem getting the multiple entries. ...

How to exclude cells from a datasource?
Hi, I have an XY chart in excel97 that uses a range of cells for the data source. Every now and then I would like to exclude a XYnode from the chart. Without having to reselt the datasource. Is there a value one can enter in the cell that exludes it from the graph (compare: putting text in a cell excludes it from the SUM() function). For example point X Y a 1 1 b 2 2 c 3 3 Gives a graph with two lines connecting the three points While point X Y a 1 1 b ? ? c 3 3 Should give a graph with one line connecting point a and c. Point b is left out. Or is the...

Data not valid error message
Access02 Have a Report which appears fine. But when you go from DesignView to ReportView it throws error: ~.."the data you entered for this field is not valid"... it doesn't say which field.....I can click thru the message and it reprompts this a few times (probably once per instance of invalid) and then opens fine. it does not do this when the Report opens ; only changing from design view... but it does do it if it is multipage and you need to go to the next page....so needs to be fixed... there's alot of fields and so am wondering how to troubleshoot....hunch is t...

How do I preset data to auto copy to a certain page
I have a Macro that hides and copies visible cells to worksheet 3, but, I have a certain row that needs to wait till the next page break befor copying so that I don't have to manually adjust them. Moving them manually takes 25 minutes 2-3 times a day, if there is a way it would really save me. ...

Macro-separate different dates with two grey rows
Excel 2000: In column B, there are about 200 rows with dates. The sheet is sorted by date. The dates have a range of about 3 weeks. There may be between 1 and 35 rows with the same date. Is there a way to automatically insert 2 grey rows between the different dates? Example: I enter 21april in row 1, then 22april in row 2, and 2 grey rows separate automatically. Joe Hi Joe try the following macro Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, &...