Spreadsheet Reference

  • Thread starter Thread starter Raja
  • Start date Start date
R

Raja

Hello

I have 30 different spreadsheets named 01, 02, 03, 04…30 an Excel
workbook. All the sheets have references to the previous sheet. Is
there any way I can refer the cell values of the previous sheet using a
general formula other than individually linking each cell to the values
previous sheet?

Any information in this regard will be of great help to me as that will
cut a ton of my work.

My thanks in advance to any one who is trying to help me in this
regard.

Raja
 
If all the spreadsheets look exactly the same you should be able to use Find/
Replace to change, say a reference from 01 to 02. However you need to be very
careful. For your find input make sure you include the ! because the risk is
it will replace items you don't want replaced. Also, I would highly suggest
you highlight only the area you want to perform the changes as this is a high
risk operation. If you are uncomfortable in using Find Replace then look at
http://www.auditexcel.co.za/othertools.html .
 
Ninja said:
try looking up using the 'indirect' function.
Click to expand...

Thanks for the replies Ninja and Adrian.

I hope I did not communicate the problem right. Here is what I nee
again.

I have 30 different spreadsheets in a workbook. Worksheet names (Aug01
Aug02, Aug 03….Aug30). All the sheets are similar. I need to refer th
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)
 
Adrian said:
If all the spreadsheets look exactly the same you should be able to use
Find/
Replace to change, say a reference from 01 to 02. However you need to
be very
careful. For your find input make sure you include the ! because the
risk is
it will replace items you don't want replaced. Also, I would highly
suggest
you highlight only the area you want to perform the changes as this is
a high
risk operation. If you are uncomfortable in using Find Replace then
look at
http://www.auditexcel.co.za/othertools.html .

Thanks for the replies Ninja and Adrian.

I hope I did not communicate the problem right. Here is what I need
again.

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)
.
.
.
.
.
D5 of sheet Aug02 to D4 of sheet Aug01, ('Aug01'!D5)


All the sheets are chain linked like this till the first sheet (Aug01).
I could do this manually, how ever any revisions to the format, I have
to redo all the links again. I am looking for a formula instead of
“('Aug29'!D5)” to be able to link the previous sheet.

Hope I am clear now. Please let me know if there is some thing that
helps me with this.
Click to expand...
 
Hi Raja,

as Ninja already said: Use the INDIRECT function.

You may compose a cell name by CONCATENATE, e.g. If Cell A1
contains the numeric value 5,

CONCATENATE("Aug",TEXT(A1-1,"00");"!D5) yields "Aug04!D5".

So,

INDIRECT(CONCATENATE("Aug",TEXT(A1-1,"00");"!D5))

yields the vaule of cell Aug04!D5.

There is one problem left: How to find the name of the current
worksheet?

With the above approach, you need to have a cell (I used A1)
on each worksheet that contains the number of the current
worksheet.

It would be more comfortable to use something like
RIGHT(INFO("worksheet"),2), instead.

Unfortunately, the INFO function does not support something
like "worksheet"...
 
Mangesh said:
check for a solution in the worksheet.functions group.
Click to expand...

That is... where? Are you talking about Visual Basic?
Although I really now quite a number of programming languages,
I do not know how to make use of this in EXCEL.

All I can say is: There is no EXCEL function that yields
the name of the current worksheet. At least I can't find
one.
 
xirx said:
That is... where? Are you talking about Visual Basic?
Click to expand...

No. In the excel.Worksheetfunctions group

All I can say is: There is no EXCEL function that yields
the name of the current worksheet. At least I can't find
one.
Click to expand...

You can find, use:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Mangesh
 
=INDIRECT("'"&(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))-2))&((RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),2))-1)&"'!D5")

paste this into your spreadsheet. This will point at cell D5 - change
it at the very end of the formula if you dont want this. It will only
work if the final 2 digits in your spreadsheet are numbers, which yours
appear to be ( 05 as opposed to just 5)

Cheers
 
the formula will also only work if the spreadsheet is saved (in case
you try it on a new one).
 
Back
Top