Formula between sheets

  • Thread starter Thread starter Dragonomega
  • Start date Start date
D

Dragonomega

k im have serious trouble finding a way to do this please help and i will
explain as best i can
i have a spreadsheet set up wit numerus pages and would like to set a
formula that can be copied and pasted where the formula looks at the
"previous sheet" at the same cell example onsheet 2 i wuld like to have cell
e48 equal to cell e49 on sheet 1 and repeat that for every sheet 3 to 2 4 to
3 yeah i can do each one individually but i have a lot of sheets and about 40
or so cells that need this formula dont reallywant to do all that typing
and on a somewhat seperate note i am also in need of a formula or macro (i
needa little guidance on maco i have not written one before and do not even
know where to put one in at) that will push a row of ifo over tothe "next
sheet depending on how many sheets i tell it to do so if itson sheet 2 and
say 3 days it will copy the info to the next few sheets so it will show on
sheet 2, 3, and 4 and then stop please assist!!!!! thanks in advance
 
First enter this User Defined FUnction:

Function prev_sheet()
prev_sheet = ""
s = Application.Caller.Parent.Name
i = 1
For Each sh In Sheets
If sh.Name = s Then
If i = 1 Then Exit Function
prev_sheet = Sheets(i - 1).Name
Exit Function
End If
i = i + 1
Next
End Function

The function will return the name of the provious sheet. So if you have
sheets named:

cat
dog
fish

entering =prev_sheet() in a cell in the fish tab will return the value dog.

So instead of using something like:

=dog!E38

we can use:

=INDIRECT(prev_sheet() & "!E38")
 
Thank you so much it works great as far as i can tell any chance of help on
my second question?
 
Back
Top