Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another colu
I have the below on 2 separate data sheets, indicating if an employee is no
longer here or if a new employee. These are in column H of the data sheets. =IF($A4130,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"") =IF(A5140,IF(ISERROR(MATCH($B514,PriorDataID,0)), "New Employee",""),"") If a no longer here result is displayed in dataprior!H413, can I get the cell value of A413 to display in another sheet, such as in cell B1 for the first incident , the next in B2, ? The no longer here, and new employee results are very random, and will be in various rows. Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another colu
One play which gets you there, Steve ..
Assume in sheet: dataprior, you have your formula in H2 down: =IF($A20,IF(ISERROR(MATCH($B2,dataID,0)),"No Longer Here",""),"") Then in a new sheet, Put in A1: =IF(dataprior!H2="","",IF(dataprior!H2="No Longer Here",ROW(),"")) [Here, we could also have used the simpler: =IF(dataprior!H2="","",ROW()) in A1 since the only indications evaluated in col H in dataprior would be either "" or "No Longer Here".] Put in B1: =IF(ROW()COUNT(A:A),"",INDEX(dataprior!A:A,SMALL( A:A,ROW())+1)) Select A1:B1, copy down to cover the max expected extent of col H in dataprior. Hide away col A. You'd get the results you seek in col B, all neatly bunched at the top. Do likewise to extract it for "New Employee". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve" wrote: I have the below on 2 separate data sheets, indicating if an employee is no longer here or if a new employee. These are in column H of the data sheets. =IF($A4130,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"") =IF(A5140,IF(ISERROR(MATCH($B514,PriorDataID,0)), "New Employee",""),"") If a no longer here result is displayed in dataprior!H413, can I get the cell value of A413 to display in another sheet, such as in cell B1 for the first incident , the next in B2, ? The no longer here, and new employee results are very random, and will be in various rows. Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another
Perfect. Thanks so much. That's exactly what I was looking for. One small
problem though. The A column references of dataprior! that I wanted bunched up at the top in the B column of the new sheet are correctly showing the the data corresponding to row 679 and row 744, but not row 413 and row 645. All 4 of those rows should qualify for the bunching ( having that no longer here text) .. I checked the dragged formulas all the way down, and they appear to be ok. Any thoughts ? Thanks, "Max" wrote: One play which gets you there, Steve .. Assume in sheet: dataprior, you have your formula in H2 down: =IF($A20,IF(ISERROR(MATCH($B2,dataID,0)),"No Longer Here",""),"") Then in a new sheet, Put in A1: =IF(dataprior!H2="","",IF(dataprior!H2="No Longer Here",ROW(),"")) [Here, we could also have used the simpler: =IF(dataprior!H2="","",ROW()) in A1 since the only indications evaluated in col H in dataprior would be either "" or "No Longer Here".] Put in B1: =IF(ROW()COUNT(A:A),"",INDEX(dataprior!A:A,SMALL( A:A,ROW())+1)) Select A1:B1, copy down to cover the max expected extent of col H in dataprior. Hide away col A. You'd get the results you seek in col B, all neatly bunched at the top. Do likewise to extract it for "New Employee". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve" wrote: I have the below on 2 separate data sheets, indicating if an employee is no longer here or if a new employee. These are in column H of the data sheets. =IF($A4130,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"") =IF(A5140,IF(ISERROR(MATCH($B514,PriorDataID,0)), "New Employee",""),"") If a no longer here result is displayed in dataprior!H413, can I get the cell value of A413 to display in another sheet, such as in cell B1 for the first incident , the next in B2, ? The no longer here, and new employee results are very random, and will be in various rows. Thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another
It should work ok, but you may be having problems adapting the
formulas to suit your actual layout over there Here's a revised set-up which assumes your data and formulae in "dataprior" are within rows 410 to 1000 (say) So in sheet: dataprior, your formulas are within H410:H1000, ie you have in H410: =IF($A4100,IF(ISERROR(MATCH($B410,dataid,0)),"No Longer Here",""),"") with H410 copied down to H1000 In a new sheet, Put in A1: =IF(dataprior!H410="","",IF(dataprior!H410="No Longer Here",ROW(),"")) Put in B1: =IF(ROW(A1)COUNT(A:A),"",INDEX(dataprior!$A$410:$ A $1000,SMALL(A:A,ROW()))) Select A1:B1, copy down to B691. Col B should return the required results. Adapt the above to suit your actual ranges. Let me know here how it went for you -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Mar 8, 2:58 am, Steve wrote: Perfect. Thanks so much. That's exactly what I was looking for. One small problem though. The A column references of dataprior! that I wanted bunched up at the top in the B column of the new sheet are correctly showing the the data corresponding to row 679 and row 744, but not row 413 and row 645. All 4 of those rows should qualify for the bunching ( having that no longer here text) . I checked the dragged formulas all the way down, and they appear to be ok. Any thoughts ? Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another
Well, this is getting kinda spooky.
The weird thing is that just like yesterday, it is working for the last 2 ( in rows 679 and 744) but not the first 2 (in rows 413 and 645). Because of the same problems as yesterday, I thought maybe something was wrong with the original formatted data on the dataprior sheet, so I formatted those cells all the same, but no change in the result. My data is in rows 3:1000 on the data prior and data sheet. Here are the formulas I'm using on the check sheet: In A3, dragged to A1000: =IF('Data Prior'!H3="","",IF('Data Prior'!H3="No Longer Here",ROW(),"")) In B3, dragged to B1000: =IF(ROW(A3)COUNT(A:A),"",INDEX('Data Prior'!$A$3:$A$1000,SMALL(A:A,ROW()))) Also, this might be the weird part. The references for the data sheet (new employees) is doing the same thing. Giving the correct results for the last 2 (in rows 595 and 608), but not in rows 549 and 514. I entered these formulas on the check sheet in columns C & D, with these dragged to 1000: In C3, =IF(Data!H3="","",IF(Data!H3="New Employee",ROW(),"")) In D3, =IF(ROW(C3)COUNT(C:C),"",INDEX(Data!$A$3:$A$1000, SMALL(C:C,ROW()))) I'm perplexed as to why it works great with the upper range data, but not the lower range. Also, it's just a cooincidence that both data and data prior have 4 incidents. Each could be different on different weeks of data download. Thanks, Steve "Max" wrote: It should work ok, but you may be having problems adapting the formulas to suit your actual layout over there Here's a revised set-up which assumes your data and formulae in "dataprior" are within rows 410 to 1000 (say) So in sheet: dataprior, your formulas are within H410:H1000, ie you have in H410: =IF($A4100,IF(ISERROR(MATCH($B410,dataid,0)),"No Longer Here",""),"") with H410 copied down to H1000 In a new sheet, Put in A1: =IF(dataprior!H410="","",IF(dataprior!H410="No Longer Here",ROW(),"")) Put in B1: =IF(ROW(A1)COUNT(A:A),"",INDEX(dataprior!$A$410:$ A $1000,SMALL(A:A,ROW()))) Select A1:B1, copy down to B691. Col B should return the required results. Adapt the above to suit your actual ranges. Let me know here how it went for you -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Mar 8, 2:58 am, Steve wrote: Perfect. Thanks so much. That's exactly what I was looking for. One small problem though. The A column references of dataprior! that I wanted bunched up at the top in the B column of the new sheet are correctly showing the the data corresponding to row 679 and row 744, but not row 413 and row 645. All 4 of those rows should qualify for the bunching ( having that no longer here text) . I checked the dragged formulas all the way down, and they appear to be ok. Any thoughts ? Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another
Steve,
Think we can revert to using the simpler entire col refs now that you've exposed your layout In your checksheet, Put in A3: =IF(dataprior!H3="","",IF(dataprior!H3="No Longer Here",ROW(),"")) (Ensure that A1:A2 are left blank) Put in B3: =IF(ROW(A1)COUNT(A:A),"",INDEX(dataprior!A:A,SMAL L(A:A,ROW(A1)))) Select A3:B3, fill down as far as required to cover the extent of source data. It should work fine now. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve" wrote in message ... Well, this is getting kinda spooky. The weird thing is that just like yesterday, it is working for the last 2 ( in rows 679 and 744) but not the first 2 (in rows 413 and 645). Because of the same problems as yesterday, I thought maybe something was wrong with the original formatted data on the dataprior sheet, so I formatted those cells all the same, but no change in the result. My data is in rows 3:1000 on the data prior and data sheet. Here are the formulas I'm using on the check sheet: In A3, dragged to A1000: =IF('Data Prior'!H3="","",IF('Data Prior'!H3="No Longer Here",ROW(),"")) In B3, dragged to B1000: =IF(ROW(A3)COUNT(A:A),"",INDEX('Data Prior'!$A$3:$A$1000,SMALL(A:A,ROW()))) Also, this might be the weird part. The references for the data sheet (new employees) is doing the same thing. Giving the correct results for the last 2 (in rows 595 and 608), but not in rows 549 and 514. I entered these formulas on the check sheet in columns C & D, with these dragged to 1000: In C3, =IF(Data!H3="","",IF(Data!H3="New Employee",ROW(),"")) In D3, =IF(ROW(C3)COUNT(C:C),"",INDEX(Data!$A$3:$A$1000, SMALL(C:C,ROW()))) I'm perplexed as to why it works great with the upper range data, but not the lower range. Also, it's just a cooincidence that both data and data prior have 4 incidents. Each could be different on different weeks of data download. Thanks, Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another
Perfect !! It works great.
Thanks so much for all your help and patience. Steve "Max" wrote: Steve, Think we can revert to using the simpler entire col refs now that you've exposed your layout In your checksheet, Put in A3: =IF(dataprior!H3="","",IF(dataprior!H3="No Longer Here",ROW(),"")) (Ensure that A1:A2 are left blank) Put in B3: =IF(ROW(A1)COUNT(A:A),"",INDEX(dataprior!A:A,SMAL L(A:A,ROW(A1)))) Select A3:B3, fill down as far as required to cover the extent of source data. It should work fine now. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve" wrote in message ... Well, this is getting kinda spooky. The weird thing is that just like yesterday, it is working for the last 2 ( in rows 679 and 744) but not the first 2 (in rows 413 and 645). Because of the same problems as yesterday, I thought maybe something was wrong with the original formatted data on the dataprior sheet, so I formatted those cells all the same, but no change in the result. My data is in rows 3:1000 on the data prior and data sheet. Here are the formulas I'm using on the check sheet: In A3, dragged to A1000: =IF('Data Prior'!H3="","",IF('Data Prior'!H3="No Longer Here",ROW(),"")) In B3, dragged to B1000: =IF(ROW(A3)COUNT(A:A),"",INDEX('Data Prior'!$A$3:$A$1000,SMALL(A:A,ROW()))) Also, this might be the weird part. The references for the data sheet (new employees) is doing the same thing. Giving the correct results for the last 2 (in rows 595 and 608), but not in rows 549 and 514. I entered these formulas on the check sheet in columns C & D, with these dragged to 1000: In C3, =IF(Data!H3="","",IF(Data!H3="New Employee",ROW(),"")) In D3, =IF(ROW(C3)COUNT(C:C),"",INDEX(Data!$A$3:$A$1000, SMALL(C:C,ROW()))) I'm perplexed as to why it works great with the upper range data, but not the lower range. Also, it's just a cooincidence that both data and data prior have 4 incidents. Each could be different on different weeks of data download. Thanks, Steve |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If specific text result,... corresponing cell info in another
Shared delights, Steve !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Mar 9, 11:41 pm, Steve wrote: Perfect !! It works great. Thanks so much for all your help and patience. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Excel 2003 :Summing specific cells of a particular color in a colu | Excel Discussion (Misc queries) | |||
sum of 1 column if info in another = specific text | Excel Discussion (Misc queries) | |||
how to get left or right cell info after getting the result for ma | Excel Worksheet Functions | |||
Want info in a specific cell to reflect in another worksheet.. | New Users to Excel |