Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cell Reference help required
Hello All, I have a problem with Cell reference between spreadsheets. Help would be highly appreciated. Here is what I need. I have 30 different spreadsheets in a workbook. Worksheet names (Aug01, Aug02, Aug 03….Aug30). All the sheets are similar. I need to refer the totals of the previous worksheet in the current one. Example: D5 of sheet Aug30 to D4 of sheet Aug29, ('Aug29'!D5) D5 of sheet Aug29 to D4 of sheet Aug28, ('Aug28'!D5) |
#2
|
|||
|
|||
Run this VBA code
Sub RunThis() ReDim ShtNames(0 To Worksheets.Count - 1) i = 0 For Each sht In Worksheets ShtNames(i) = sht.Name i = i + 1 Next For j = 1 To UBound(ShtNames) Worksheets(ShtNames(j)).Range("D5") = "=" & ShtNames(j - 1) & "!D4" Next End Sub This will do the following: In Aug2 sheet cell D5, it will put =Aug1!D4 and so on. Mangesh "Raja" wrote in message ... Hello All, I have a problem with Cell reference between spreadsheets. Help would be highly appreciated. Here is what I need. I have 30 different spreadsheets in a workbook. Worksheet names (Aug01, Aug02, Aug 03..Aug30). All the sheets are similar. I need to refer the totals of the previous worksheet in the current one. Example: D5 of sheet Aug30 to D4 of sheet Aug29, ('Aug29'!D5) D5 of sheet Aug29 to D4 of sheet Aug28, ('Aug28'!D5) |
#3
|
|||
|
|||
Dear Mangesh, Thanks a lot for the code. I am a dummie ..so please explain me on where and how to run the code. I am using MS office 2000 Raja Mangesh Yadav Wrote: Run this VBA code Sub RunThis() ReDim ShtNames(0 To Worksheets.Count - 1) i = 0 For Each sht In Worksheets ShtNames(i) = sht.Name i = i + 1 Next For j = 1 To UBound(ShtNames) Worksheets(ShtNames(j)).Range("D5") = "=" & ShtNames(j - 1) & "!D4" Next End Sub This will do the following: In Aug2 sheet cell D5, it will put =Aug1!D4 and so on. Mangesh -- Raja ------------------------------------------------------------------------ Raja's Profile: http://www.excelforum.com/member.php...o&userid=25901 View this thread: http://www.excelforum.com/showthread...hreadid=393232 |
#4
|
|||
|
|||
Nice simple solution :-)
=IF(RIGHT(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255),2)<" 01",INDIRECT("'"&LEFT(MID(CELL("Filename",A1),FIND ("]",CELL("Filename",A1))+ 1,255),LEN(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255))-2)& TEXT(VALUE(RIGHT(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255 ),2))-1,"00")&"'!D5"),"") -- HTH RP (remove nothere from the email address if mailing direct) "Raja" wrote in message ... Hello All, I have a problem with Cell reference between spreadsheets. Help would be highly appreciated. Here is what I need. I have 30 different spreadsheets in a workbook. Worksheet names (Aug01, Aug02, Aug 03..Aug30). All the sheets are similar. I need to refer the totals of the previous worksheet in the current one. Example: D5 of sheet Aug30 to D4 of sheet Aug29, ('Aug29'!D5) D5 of sheet Aug29 to D4 of sheet Aug28, ('Aug28'!D5) |
#5
|
|||
|
|||
Hi, I think Bob's formula can be shortened to like this, =IF(RIGHT(x,2)="01","",INDIRECT("Aug"&TEXT(--RIGHT(x,2)-1,"00")&"!D5")) where x, =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=393232 |
#6
|
|||
|
|||
If you have not used Bob's formula as yet, then to use my code, simply press
Alt F11 in excel. This opens the VBE. Go to Insert, and insert a new module. Place the code here. And click on the right-pointed triangle to run it. Mangesh "Raja" wrote in message ... Dear Mangesh, Thanks a lot for the code. I am a dummie ..so please explain me on where and how to run the code. I am using MS office 2000 Raja Mangesh Yadav Wrote: Run this VBA code Sub RunThis() ReDim ShtNames(0 To Worksheets.Count - 1) i = 0 For Each sht In Worksheets ShtNames(i) = sht.Name i = i + 1 Next For j = 1 To UBound(ShtNames) Worksheets(ShtNames(j)).Range("D5") = "=" & ShtNames(j - 1) & "!D4" Next End Sub This will do the following: In Aug2 sheet cell D5, it will put =Aug1!D4 and so on. Mangesh -- Raja ------------------------------------------------------------------------ Raja's Profile: http://www.excelforum.com/member.php...o&userid=25901 View this thread: http://www.excelforum.com/showthread...hreadid=393232 |
#7
|
|||
|
|||
Yeah but assumes Aug. Mine will work with Aug, Sep, etc, which I am sure is
what the OP would want. Put that back in and it is no shorter. -- HTH RP (remove nothere from the email address if mailing direct) "Krishnakumar" wrote in message news:Krishnakumar.1taimi_1123243557.8972@excelforu m-nospam.com... Hi, I think Bob's formula can be shortened to like this, =IF(RIGHT(x,2)="01","",INDIRECT("Aug"&TEXT(--RIGHT(x,2)-1,"00")&"!D5")) where x, =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=393232 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Reference help required | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) | |||
reference cell value from fixed column with variable row | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |