Dear Ian,

Below is a sample of only one sheet, and there is one sheet for every book
of the Old Greek (LXX, Septuagint) Bible plus the New Testament. It is
arranged as follows (though in this post we can't see the Greek font):

Please note: The first row is blank (I inserted some information here).

The first column (A) is the book (in this example Exodus), the second column
(B) is the Greek dictionary form, the third column (C) is the parsing, the
fourth column is the Greek word declined (D), the blank fifth column (E) is
where I want to put the English translation of the word in D column, and the
last column (F) contains the number of where the word ranks in the book (and
used for resorting). I realize this could be done in Access, but I want to
do this in Excel for several reasons I won't bore you with the details.

A B C D E F
Exo 01:01 ou-toj rdnnp tau/ta 1
Exo 01:01 o` dnnp ta. 2
Exo 01:01 o;noma nnnpc ovno,mata 3
Exo 01:01 o` dgmp tw/n 4
Exo 01:01 ui`o,j ngmpc ui`w/n 5
Exo 01:01 ivsrah,l ngmsp israhl 6
Exo 01:01 o` dgmp tw/n 7
Exo 01:01 eivsporeu,omai vpxpgmp eivspeporeume,nwn 8
Exo 01:01 eivj pa eivj 9
Exo 01:01 ai;guptoj nafsp ai;gupton 10
Exo 01:01 a[ma pd a[ma 11
Exo 01:01 ivakw,b ndmsp iakwb 12
Exo 01:01 o` ddms tw/| 13
Exo 01:01 path,r ndmsc patri. 14

As you can see the dictionary word for the (o`) as several forms. This is
want to do: as I translate from the first word in the book I want to search
for the same word in B (ou-toj = this) and the same word in D (tau/ta =
these) and translate this B and D combination in column E (these). Then I go
down to the second row and do the same. And this should be a global search
and replace of all the books (sheets) of the Greek Bible.

"IanRoy" wrote:

Hi, Rebecca;

I don't know how to write for a six-year-old, but Word tells me the
following is at Flesch-Kincaid Grade level 6.4, rather harder than what you
asked for, and for that I apologize.

Assumption 1:
You asked for global search and replace, so I made the assumption that:
there is one word you want to look for in column B of all sheets,
another word you want to look for in column D of all sheets,
and one word you want to insert in column E of all sheets.
If this is correct, give me the words and the row number of the top row they
appear, and I will write you a formula that you can copy and paste into your
worksheets.

Assumption 2:
Assumption 1 rather conflicts with "(usually different)" in your first post.
If you mean to change these words from time to time, you will want to enter
these words into some three cells on one or more of your worksheets. Let me
know which cell contains the word for column B, which for column D, and which
for column E, and I will write the formula for that. Also, let me know if
these words differ by sheet.

To explain the formula:
To let Excel know that your cell entry is a formula, you start it with an
equal sign.
The Excel function "AND" returns the value "TRUE" if all of its arguments
are true.
So, if we use the arguments B2="Fred," and D2="Jane" with the AND function
so:
=AND(B2="Fred",D2="Jane") and those cells contain those words, this formula
will return the value: TRUE. (The quotes around Fred and Jane seem to be
needed to tell Excel to look for text.) That is not enough yet. We want to
return a word besides TRUE. We can do that by nesting the AND function and
its arguments inside an IF function. IF returns one value if its argument is
true, and another if its argument is false. So, if we want to return say
"friends" in cell E2, if both Fred and Jane are present, we would write into
cell E2:
=(IF(AND(B2="Fred",D2="Jane"),"friends","")
This way, if both Fred and Jane are present E2 will show: friends. If only
one of them or neither are present, "" tells Excel to show empty text.

If these words will not always be the same, then we can put them in other
cells and then refer to those cells in the formula. We could put Fred in cell
F1, Jane in cell F2, and friends in cell F3, for example. Then write the
formula in E2:
=(IF(AND(B2=$F$1,D2=$F$2),$F$3,"")
The dollar signs tell Excel to refer to those cells regardless of where on
the sheet this formula is copied. The other cell references will adjust, so
that if you copy or fill this formula down to E3, the formula in E3 will read:
=(IF(AND(B3=$F$1,D3=$F$2),$F$3,"")
Unlike text, you won't need to enclose cell references with quotes.
Then if you want to use another set of three words, or any of them, you can
change the entries in cells F1, F2, and F3, and the formulae will update
column E.

If that explanation is adequate, I wll be overjoyed. But if not, come back
with questions, or the information to let me write a formula that you can
copy and paste.

Regards, and welcome to Excel.
Ian.


"Rebecca" wrote:

Dear Ian,

I apologize for being a little slow here, but as I said I am very new to
Excel, so I could not follow your instructions. Could you please explain one
more time , but this time like you are explaining something to a six-year
old. I haven't learned yet how to use formulas.