If specific text result,... corresponing cell info in another colu

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 06 Mar 2007 21:31 GMT

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($A413>0,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"")

=IF(A514>0,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

Reply to this Message

Max - 07 Mar 2007 02:01 GMT

One play which gets you there, Steve ..

Assume in sheet: dataprior,
you have your formula in H2 down:
=IF($A2>0,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".

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.
[quoted text clipped - 11 lines]
>
> Steve

Reply to this Message

Steve - 07 Mar 2007 18:58 GMT

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,

> One play which gets you there, Steve ..
>
[quoted text clipped - 30 lines]
> >
> > Steve

Reply to this Message

Max - 08 Mar 2007 13:40 GMT

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($A410>0,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
---

> Perfect. Thanks so much. That's exactly what I was looking for. One small
> problem though.
[quoted text clipped - 6 lines]
>
> Thanks,

Reply to this Message

Steve - 08 Mar 2007 20:00 GMT

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

> It should work ok, but you may be having problems adapting the
> formulas to suit your actual layout over there
[quoted text clipped - 35 lines]
> >
> > Thanks,

Reply to this Message

Max - 08 Mar 2007 21:44 GMT

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,SMALL(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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Well, this is getting kinda spooky.
> The weird thing is that just like yesterday, it is working for the last 2
[quoted text clipped - 31 lines]
>
> Steve

Reply to this Message

Steve - 09 Mar 2007 15:41 GMT

Perfect !! It works great.

Thanks so much for all your help and patience.

Steve

> Steve,
>
[quoted text clipped - 46 lines]
> >
> > Steve

Reply to this Message

Max - 09 Mar 2007 22:18 GMT

Shared delights, Steve !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Perfect !! It works great.
> Thanks so much for all your help and patience.
> Steve

Reply to this Message