Look up formula

[Chel]

Is it possible to write a formula that #1 has to look in a column for a
specific word and then if it is there then #2 goes to a previous tab and
grabs the information that is the last thing typed in that column?

Hope this makes sense.

Many thanks

 

[Shane Devenshire]

Hi,

How does it know which "previous tab" to pick? How does it know which
column to pick?

If we say Yes, is that good enough?

If you want a suggested formula you would need to show us samples of the
data in the tab you are entering the formula in and other details. For
example, is it looking for the word by itself in a cell or is the word part
of a sentence? Does it make any difference what the word is, or on what row
it finds it? Is this search case sensitive or not?

The general formulas for the last item in a column are:

=INDEX(C3:C19,MATCH(TRUE,C3:C19<>""))
=LOOKUP(9^9,C3:C21)
=LOOKUP(2,1/(B:B<>""),B:B)
=LOOKUP(REPT("z",255),B2:B21)
=LOOKUP(9^9,IF(C3:C15>0,C3:C15))

Of course which one you choose depends on your data.

 

[Chel]

Hi there

The worksheets are weekly – eq ‘Mar 8-15’ & ‘Mar 15-21’

Here is an example:

Worksheet ‘Mar 8-15’

A B C
1 Date Location Comment
2 8 - Down
3 9 - Down
4 10 Manitoba Working
5 11 - Working

Worksheet ‘Mar 15-21’

A B C
1 Date Location Comment
2 15 - Down
3 16 - Working
4 17
5 18

In worksheet Mar 15-21, ---- B2, I would like to put a formula that looks at
C2 if it says ‘working’ then got to worksheet Mar 8-15 and look at the range
B2:B5 and choose that last location in that range and insert it.
My new worksheet would have in B3 the word Manitoba. Hope this is a bit
clearer. In the meantime I will play around with a few of those formulas and
see if I can get it to work.
Thanks for your time with this!