permanent cell equal to a sheet

[spilner]

hi,i hve D,E and F cell as below.i need help if someone can make macro of this.

the cell D and F's cell is permanent.only the sheet number is different.
ive hundred of sheets which mean i will also have hundreds value in E cell.
below is example.what im showing is exact like in excel file.

  D           E                F
=''!G11       1             =''!F28
=''!G11       2             =''!F28
=''!G11       3             =''!F28
=''!G11       4             =''!F28
=''!G11       5             =''!F28

after formula :

   D             E              F
='1'!G11         1          ='1'!F28
='2'!G11         2          ='2'!F28
='3'!G11         3          ='3'!F28
='4'!G11         4          ='4'!F28
='5'!G11         5          ='5'!F28

is there anyway we can can make it auto insert value in E to D and E to F's ='value in E' ?

thanks a lot

 

[spilner]

i dont really understand your question but hope the pic above explain.

F25 = 100
F26 = 100
F27 = 100
F28 = 300

thanks

 

[GlennUK]

I mean that your formula will have to look for the cell entry of "Total" and fetch the required value from the cell alongside of it. Maybe like this:

=IF(ISERROR(INDIRECT(E2&"!A1")),"",INDEX(INDIRECT(E2&"!F1:F999"),MATCH("Total",INDIRECT(E2&"!E1:E999")),0))

 

[spilner]

it work great man.
i dont understand on F1:f999.just curious cause the total will probably till F2000 or F10000 one day.
it match the word of Total?
if i dont have the word of TOTAL.so,the formula wont work?

 

[GlennUK]

Change the 999 to whatever you think will be the possible maximum. Be aware that the larger the number the slower your calculations will be.

 

[spilner]

Cool.i have a last question.

=INDIRECT("'"&E2&"'!G11")

how do i turn the result is blank like u did in the f28 formula?

it still give me #REF!.

i try =if(INDIRECT("'"&E2&"'!G11"),0) not work too.

thanks a lot glennUK.i appreciate all your hard work.thank you once again.

 

[GlennUK]

Like this:

=IF(ISERROR(INDIRECT("'"&E2&"'!A1")),"",INDIRECT("'"&E2&"'!G11"))

 

[spilner]

thanks it work great.

i try changed it to F:F and E:E it work like a cham.so its unlimited,hope thats okay.thanks a lot bro

 

[GlennUK]

If you are happy with the speed of calculation, then fine.

 

[spilner]

thank you so much Glenn.thanks for your hard work.thanks for your time.thanks for everything.

 

[SydneyGeek]

The INDIRECT formula I gave you is currently hard-wired for a particular cell and won't change when rows are inserted or deleted.
When I usually do in that situation is use a range name, as Glenn asked.
Let's assume that you want to put a SHEET-LEVEL name on each of the worksheets to summarise. You would do this:

1. On, say, Feb Results sheet select the cell that represents the total
2. Click in the Name box (to the left of the formula bar) and enter the sheet name followed by ! and the name you want to give the cell (say, TotalRevenue). Note that sheet names with spaces need to be wrapped in single quotes, so the name will be entered 'Feb Results'!TotalRevenue
3. Press Enter; the name will change to TotalRevenue but, by inserting the sheet name, you have created a name for that sheet only.
4. Repeat for the other sheets, using their sheet names as required.
5. Back in the summary sheet change the INDIRECT formula to
=INDIRECT("'"&E2&"'!TotalRevenue")

That will pick up the correct total regardless of how many rows you insert before it.

Denis