When printing mutiple selected sheets in Excel, though the total no of
pages in footer changes, but the individual page no. does not increse
say 1,2,3,4 etc., it remains for example 1/4, 2/4, 1/4, 1/4 where
there are three sheets , first one containing two pages and the other
two one each. When I print individual sheets footer shows 1/2, 2/2,
1/1, 1/1.

Maybe this will help. With all sheets selected, use:
File Page setup Page
Where it says "First page number" put the word "Auto" (without the quotes).

(I have Excel 2003.)