Notes Field From Matching Month

[crazyforexcel]

Hi,

Is there a formula to fill the Notes field on the Monthly Analysis sheet from the proper sheet months 1-12?

Thank you for your help

[saravnepali]

Not sure if you can get formula for this that can do it.

You can use concatenate function to join text like this

Please Login or Register  to view this content.

Or, use code below to get the value

Please Login or Register  to view this content.

[crazyforexcel]

Thank you but is there a formula to do this?

Sorry I didn't see the formula.

Thanks so much

[crazyforexcel]

I tried the formula but it isn't filling the notes field.

I don't want to combine the notes.

I just would like the particular month's notes.

For instance in January: 'January is cold'

[TMS]

Thank you but is there a formula to do this?

Probably not without a long repetitive IF formula.

[Richard Buttrey]

Hi,

You asked for a formula. You could use one like

Please Login or Register  to view this content.

extend that to cover the other sheets and set the Monthly Analysis Cell to 'Wrap Text' - if indeed that's what you want.

Incidentally I note that you are using merged cells. You should seriously consider that practice. Most of us avoid them like the plague since they cause too many problems for all sorts of reasons that needn't concern us now.

[saravnepali]

You can use CONCAT or TEXTJOIN function as mentioned above for all the sheets text

Please Login or Register  to view this content.

You can even call as a formula from Worksheet, if that what you would prefer

Please Login or Register  to view this content.

would do the job, if you have this code on the standard module

Please Login or Register  to view this content.

[crazyforexcel]

Okay I think I have not asked properly.

If I select the January sheet (cell B1) on the Monthly Analysis sheet then cell H34 will have:

January is cold


Please see the attachment

Thank you

[AliGW]

Add numbers to your References tab thus:

Excel 2016 (Windows) 32 bit

	N	O
1	Months
2	January	1
3	February	2
4	March	3
5	April	4
6	May	5
7	June	6
8	July	7
9	August	8
10	September	9
11	October	10
12	November	11
13	December	12

Sheet: References

Then use this:

=INDIRECT("'"&VLOOKUP(B1,References!$N$2:$O$13,2,0)&"'!E35")

[crazyforexcel]

Thanks Ali I get a REF error. I will be back soon thanks.

[AliGW]

Not if you do it exactly as I advised. See attached.

[crazyforexcel]

Thank you Ali I see what I was doing wrong.

It works perfectly!

[AliGW]

No problem. Happy Christmas!