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

 

[GlennUK]

1. - if you want to do a simple sum that involved a cell in another sheet, sure, that's fine. What's the problem with doing that exactly?

2. Excel will accept a lot of possible short date formats ... but there are certain characters that you can't use ( like / ). What you want the tab name to be?

 

[Mon Wimp]

Hi, and thanks for the response

I have a workbook 366 worksheets for one year including a leap year.

lets say i am using range f1:f10 for a carryover figure and g1:g10 today figure for age groups on every sheet.
Each cell is to look @sheet before @f1:f10 to get the number from the carryover figure and the add to today figure. I would think that this would b a big job with formulas if i have to each individual cell

As for the dates. Well what i am trying to do is for the 366 worksheets, is have jan 1 so on to dec 31. I am already using vba to look @ a sheet that has all the dates, but it will not except dates as a sheet name.

I hope this helps. I would send u the form if it was allowed, and if i knew how.

Thanks Allan

 

[GlennUK]

You've said twice that Excel won't accept a date as a sheet name ... yes it will, as long as you don't put illegal characters in there. What dates are you trying have as sheet names? And what happens when you try that?

 

[Mon Wimp]

Ok I am Trying to explain.

eg: a1's cell formatting is date, so if i enter 1 1 then the cell formatting thinks it is the 1st January, Yes!. So this good. VBA looks @ a1 and name the sheet1 a1's value. It cannot do this. excel has put in special chars for the date an I am not putting in any special chars. I think that excel only accepts things that are text formatting only for sheet names.
If this does not help then i do not what else to but thanks for your help.
regards Allan)

 

[GTO]

In A1, is there actually a date (ie - in the formula bar, it shows a valid date which may be formatted differently than what displays in the cell) or is it text?

Presuming the sheets are in order, renaming may be as easy as:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> exa2()<br><SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> CurDate <SPAN style="color:#00007F">As</SPAN> Date<br><SPAN style="color:#00007F">Const</SPAN> InitialDate <SPAN style="color:#00007F">As</SPAN> Date = #1/1/2011#<br>    <br>    CurDate = InitialDate - 1<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wks <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        CurDate = CurDate + 1<br>        wks.Name = Format(CurDate, "mmm d")<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Does that help?

Mark

 

[Mon Wimp]

Thanks Mark, that fixed it. Obviously i am not a great explainer.

Still thanks to glen for his input.

Do you think you can help with other problem?

Allan

 

[GlennUK]

To refer to the previous sheet, you need to calculate the sheet name ( it's a date ... can you calculate the date of the sheet you want to refer to? ) ... and then reference it via the INDIRECT function.

 

[Mon Wimp]

hi glen, sorry for lateness. The date for sheet tabs is fixed now, thanks for that. My other query is with the 365 sheets, i would like the activesheet range f2 to look at sheet before it range f2 and sum those two cells together with e2 on the activesheet. This would have to work for all sheets except the first one. I hope i explained it well enough.
thanks Allan

 

[GlennUK]

To reference a cell on the previous sheet, I think you'd need something like:

=INDIRECT("'"&TEXT(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+4,3)&"/"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3))-1,"mmm d")&"'!F2")