Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup of Cells and transferring them to make a list
Can you please tell me how to lookup cells and transfer them to a list,
eg Data in sheet 1 A B 2 100 5 50 4 75 2 250 5 95 2 60 I want to lookup the value "2" in the Column A of the data in sheet 1 and and transfer to another sheet to appear as below(keeping in mind i dont want blanks cells where the other A values were); Sheet 2 A B 2 100 2 250 2 60 Thank you in advance Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup of Cells and transferring them to make a list
Hi Dan,
The easiest way for that is to goto DataAutofilter and filter for 2 on column A then copy and paste the result to your new sheet. HTH Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup of Cells and transferring them to make a list
Thanks, I got to that part before posting but found that my data is
continually growing ad, as bad as this sounds, dont have time to refresh the filter. I will also need to use this feature with lots of data. I also need run a cumulative totals. there lay several problems. So my question is, Is there a formula that will shift the data to the new sheet and bunch the selected data while completely leaving out the unwanted data. Thanks again Dan "MartinW" wrote in message ... Hi Dan, The easiest way for that is to goto DataAutofilter and filter for 2 on column A then copy and paste the result to your new sheet. HTH Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup of Cells and transferring them to make a list
"Dan" wrote:
So my question is, Is there a formula that will shift the data to the new sheet and bunch the selected data while completely leaving out the unwanted data. One play which will return exactly what you're after .. Assume source data in Sheet1, cols A and B, data from row1 down to a max row 100 (say) (key col is col A = criteria value) In Sheet2, Let D1 house the criteria input, eg: 2 Put in A2: =IF(ROW(A1)COUNT($C:$C),"",INDEX(Sheet1!A:A,MATCH (SMALL($C:$C,ROW(A1)),$C:$C,0))) Copy A2 to B2 Put in C2: =IF(OR(Sheet1!A2="",$D$1=""),"",IF(Sheet1!A2=$D$1, ROW(),"")) (Leave C1 empty) Then just select A2:C2, copy down to C100 to cover the max expected extent of data in Sheet1 Cols A & B will return the required results, all neatly bunched at the top (Hide away the criteria col C if desired) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup of Cells and transferring them to make a list
Typo:
Assume source data in Sheet1, cols A and B, data from row1 down to a max row 100 (say) 2nd line above should read as: cols A and B, data from row2 down to a max row 100 (say) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup of Cells and transferring them to make a list
Hi again Dan,
I will also need to use this feature with lots of data. I also need run a cumulative totals. there lay several problems.< As you say 'there lay several problems'. The short answer is YES! what you are trying is very achievable, and it probably doesn't involve very complicated formulae, just some clear thinking. You say you are trying to 'shift' data from one sheet to another. Don't think of it that way, think more along the lines of the end result as in what does that new sheet need to get the result you want. Post again with more detail of your desired results and I'm sure someone will come up with what you need. HTH Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|