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

 

[SydneyGeek]

Try ...

In D2,
=INDIRECT("'"&E2&"'!G11")

Fill down

In F2,
=INDIRECT("'"&E2&"'!F28")

Fill down

 

[spilner]

work like a cham.thank you so much man.i owe u.thanks once again

 

[SydneyGeek]

You're welcome.

Denis

 

[spilner]

If i havent create a new sheet yet. So the number on D wont be key in. If i put the formula it turns REF. how do i make it turn to blank? So when i enter the sheet number on D. Then i refresh the formula. Then it recalculate the cell. Thanks

 

[spilner]

sydney,

example in sheet 2.i insert a new line.the formula still take from F28.can u write when i insert a new line it will change to F29 cause the f28 is the total.
the total in f28 will be in f29 or so when i insert multiple lines.
can u fix it?

thanks sydney

 

[spilner]

can someone help to fix this formula? thanks

 

[GlennUK]

sydney,

example in sheet 2.i insert a new line.the formula still take from F28.can u write when i insert a new line it will change to F29 cause the f28 is the total.
the total in f28 will be in f29 or so when i insert multiple lines.
can u fix it?

thanks sydney

You have shifted the goalposts a fair bit with that little snippet of information. It may be that you will need a completely different approach. Is the cell F28 identifiable by some text or label, for example, the word TOTAL in a corresponding cell on the same row?

 

[spilner]

the F28 is the total of sales.

like the pic above.if i insert a new line in F27.
the F28 will change to F29 right?

this formula =INDIRECT("'"&E2&"'!F28") .it will show the value 100 if i insert a new line in F27 as the formula is exact to F28 only.i want the formula to follow the total in F28.
so,everytime i insert a new line.the formula will still show the 300.
sorry for my bad english.thanks for concern.

 

[GlennUK]

And I ask again ... Is the cell F28 identifiable by some text or label, for example, the word TOTAL in a corresponding cell on the same row?