How to format concatenated data to access data in another sheet/cell

[daverc]

I’m trying to pull data from sheet and cell (June.g20 which is 84) and have it show up in the another sheet and cell (CO_Data.g5). Sheet “CO_Data” has various equations that give me the data i need to pull the proper information from the sheet i need. Sheet “CO_Data” Cell “E8” contains “=EDATE(E6,-6)” and is formatted to give me a date of MMMM/YYYY or June/2019. Cell G5 of sheet"CO_Data" contains the following:

=CONCATENATE(TEXT(E8,“mmmm”),".g20")

the equation pulls the information from cell E8 and formats it to give only the month as a word and then add the cell number “.G20” to the end of the month, which works and displays “June.G20”

But I need it to access the cell “G20” of sheet “June” and display the number not the name of the sheet and cell number. I know I’m missing something in the formatting but cannot figure it out

[mariosv]

INDIRECT(TEXT(E8,"mmmm")&".g20") should do what you like.
& replace the use of CONCATENATE function.

INDIRECT function

Returns the reference specified by a text string. This function can also be used to return the area of a corresponding string.

[erAck]

Just to mention that, as almost anything involving the TEXT() function, this will not work if the current locale’s language does not match the sheet name’s language used for month names, i.e. passing the document around to other people working in a different locale is likely to fail, or even switching the locale on the own computer.

[mariosv]

I can’t find where I see, but I think now it’s possible to use English format code with any locale.

[erAck]

Yes, English format codes are possible in all locales since some time, but the result (here month names) can be different (so wouldn’t match the sheets’ names).

[mariosv]

Sure, perhaps he should better use months numbers NN instead months names. I don’t know the details, but usually it’s better having the whole data in the same sheet, to get all the power of the spreadsheet functions and pivot tables.

[daverc]

You Rock, as Kojak the lollipop sucking detective would say- “Who loves ya, baby?” It works! I had stumbled across this a few times before but couldn’t figure out the workings of it, you made it easy.

Thanks Dave C

[daverc]

m.a.riosv “usually it’s better having the whole data in the same sheet, to get all the power of the spreadsheet functions and pivot tables” I’ve been thinking about this but to have all of the data that is compartmentalized across the 12 different sheets would be a headache to look at

[mariosv]

I think not, if it is well organized, using filters allows to select easily what to see.