Ask Your Question
0

How to format concatenated data to access data in another sheet/cell [closed]

asked 2019-09-30 15:14:14 +0200

daverc gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by daverc
close date 2019-09-30 23:42:04.484658

1 Answer

Sort by » oldest newest most voted
1

answered 2019-09-30 15:44:45 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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.

erAck gravatar imageerAck ( 2019-09-30 16:35:01 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2019-09-30 17:43:36 +0200 )edit

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).

erAck gravatar imageerAck ( 2019-09-30 20:44:19 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2019-09-30 22:14:38 +0200 )edit

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 gravatar imagedaverc ( 2019-09-30 23:26:56 +0200 )edit

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

daverc gravatar imagedaverc ( 2019-09-30 23:32:34 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2019-10-01 22:02:22 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-09-30 15:14:14 +0200

Seen: 19 times

Last updated: Sep 30