I have two sheets, sheet1 and sheet2, Sheet1 contain all data and it has
three columns Name1, Name2 and Amount.

Sheet2 have two columns Name and Amount, so i need a sumif funtion to pull
the amount column from sheet1 to sheet2 as per the names which ever i type in
sheet2 name column

Example:
Sheet1:
Name1 Name2 Amount
a p 10
b q 20
a r 30

Sheet2:
Name Amount
a 40 [=SUMIF(Sheet1!A1:A4,Sheet2!A2,Sheet1!C1:C4)]

suppose if i replace the sheet2 Name column from 'a' to 'p' the above sumif
function need to change its range automatically from Sheet1!A1:A4 to
Sheet1!B1:B4
and the amount column should show the answer as 10.