lookup sheet name

[callen12]

I know this is simple but cant get my head around it to work

I have a spreadsheet tab names Master, List , and 1-31.

List sheet has name range (Subtotal) listing sheet names and value in cell F30. 3 column is running sum. (H:J)

On each Tab (1-31) i want the running sum for the day from List sheet.

I know i can do this individually but cant figure out the indirect/vlookup formula so when i rebuild from my master after changes i dont have to modifiy each sheet/formula.

Convaluted but thats how my brain is working right now.lol.

Thanks for any help.

 

[jasonb75]

If I read that right,

=vlookup(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),Subtotal,3,0)

 

[Aladin Akyurek]

What is your current look up formula?

 

[callen12]

=VLOOKUP(V2,Subtotal,3,FALSE)

v2 contains sheetname

 

[jasonb75]

I didn't allow for the sheetnames being numeric

=vlookup(--MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),Subtotal,3,0)

However, this will show as an error on the 'master' sheet.

 

[Aladin Akyurek]

=VLOOKUP(V2,Subtotal,3,FALSE)

v2 contains sheetname

a) Is there a formula that yields the sheet name of the current sheet or is the sheet name just typed in?

b) What kind information is associated with a sheet name in the 3rd column of Subtotal?

 

[callen12]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) = sheetname in Cell A1

 

[Aladin Akyurek]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) = sheetname in Cell A1

What do you want to do with it? How is this related to the VLOOKUP you posted?

By the way, the following doesn't need any hard-coded constant like 255 or 31...

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

 

[callen12]

When pasted into the sheets the above formula

=vlookup(--MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),Subtotal,3,0)

gives an #N/A

Which was the same error i was getting with the original formula?

 

[Aladin Akyurek]

When pasted into the sheets the above formula

=vlookup(--MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),Subtotal,3,0)

gives an #N/A

Which was the same error i was getting with the original formula?

Better...

Either:

=VLOOKUP(A1,Subtotal,3,0)

Or:

=VLOOKUP(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),Subtotal,3,0)

Does this help?

Note. FALSE and 0 have the same effect in a function like VLOOKUP or MATCH.