vba to xtract data from another sheet

[Mon Wimp]

Hi again, thanks for to all that have responded to earlier Questions. I however have another. Can i somebody help with 2 questions:
1. i would like to get a value from sheet 1 in the same range as sheet 2 and then sum that value in sheet 2 to the cell directly on its left in sheet 2.

2. how does one get excel to accept a short date for tab names.

thanks as always.
Allan

 

[Mon Wimp]

thanks for the response. Dates were the other problem, which you fixed and that works fine. the other part of my first post was this problem. how to sum the earlier said cells. this has nothing to with dates. I am sorry that i keep confusing you. this not my intention. Can this be done in vba rather than a formula.
Allan

 

[GlennUK]

Maybe something like:

Function getprevsheet(mycell As Range)
    Application.Volatile
    getprevsheet = Worksheets(mycell.Parent.Index - 1).Range(mycell.Address).Value
End Function

... returns the value of the referred-to cell in the previous sheet.

 

[Mon Wimp]

so can i use this as in a worksheet module, so that when data changes in the worksheet it changes as well.
Allan

 

[GlennUK]

Put that into a module ( Insert / Module ). Cells are calculated individually, and so multiple calls will be made to the function.

 

[Mon Wimp]

ok ,not sure what i am doing wrong
Function getprevsheet(mycell As Range)
Application.Volatile
getprevsheet = Worksheets(mycell.Parent.Index - 1).Range(mycell.Address).Value
End Function
both module and private sub are not working for me. my range is q2. I get a compile error and a expected end sub which i thought only happens if you do not have an end sub. it changes end sub to end function. oops
Allan

 

[GlennUK]

Clear out whatever it is you've been experimenting with .... if you are getting "Expected End Sub" error, then you have rubbish sitting in there somewhere. Then put the code into a normal ( not Worksheet, not Workbook ) module.

 

[Mon Wimp]

I will revisit this tomorrow.
I can not get it to work. A normal module has to be called: Yes. As a function it does not work for me. Give the module a name, and for me it still does not work. I appreciate your patience though, for me i would of shot a cat or something by now. will see tomorrow.
Thanks Allan

 

[Mon Wimp]

Hi glenn, if you are there.
Ok i have put into a sheet module and i have renamed mycell to the ranged cell to enter the data in. But what i do not get is how it runs. what am i doing wrong.
Allan