Need Help trying to automatically organice data from new sheets

I will try to explain fully my problem as i'm not english native, hope you can help me.

I have a workbook, in it I constantly create several sheets and use the same page format with different data in all of them, which I rename different(for example: 'A1 B1', 'A1 B2', 'A2 B1', etc.)
In the end i want to create a summary of a specific cell of each sheet that updates itself as i am creating new sheets
What i think of doing is using the =concatenate formula (so i get the text for example: ='A1 B1'!$V$10, and in the next 'A1 B2'!$V$10)
in the end i only get the text, but not the data i want it to appear, so i don't know how to make it so i get the value instead of the text. (i tried copying the text and pasting it with Formating (R) and it works, but i was hoping there's a way to automatically do it.

PD: sorry if it's in the wrong section, hope someone can move it if it's in one that it shouldn't be.

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

hi,

see in attachment if it work for you. adjust Row()-7 as per your current cell.

Thanks
Rohit

Try

G4=INDIRECT(CONCATENATE($L$1,K4,$M$1,L4,"'",$N$1)) and drag down.

Formula:

Please Login or Register  to view this content.

Glad to help you please mark as it solved and please add reputation by clicking star * icon left corner on your screen.