Get pie chart to use current month's data

Jo4x4 · Aug 9, 2018

Hi everybody,

I have a sheet with Jan to Dec in column a, and then data in eleven columns c-m

Below this, I have two pie charts, one for the current month, and one for the year to date.

How do I get the current month to update and use the current month's data?

Thanks in advance

Jo
Win 7 & excel 2007

jmacleary · Aug 9, 2018

Hi there. You could create a new row (I assumed row 14) labelled 'Current' in column A14 and put this formula in C14 and drag across to column M. Then use that range for your pie chart:
=VLOOKUP(TEXT(TODAY(),"mmm"),$A$1:$M$12,COLUMN(C$14),FALSE)

Regards
John

Jo4x4 · Aug 10, 2018

Thanks John, works a charm!!

ExcelWusgood · Apr 10, 2022

Hi both,

Could either of you perhaps dumb this down to make an Excel-novice understand?

Im in a similar situation as listed above. But im not confident how to use the formular written by @jmacleary. (also my excel is in a different language, but I have the translations down). If i write the exact formular listed, I get an error.

Any help is very appreciated!

Br

BSALV · Apr 10, 2022

Is your data the same ?
In column A the names of the month (3 characters ! Jan , Feb, ...) ?
what is your language ?
Otherwise show your (non sensitive) data with the XL2BB-tool.

ExcelWusgood · Apr 10, 2022

My tab has months listed in row 2 (from B2:M2) with full characters, so im using "mmmm", and data in column A, which means Im also using HLOOKUP.

I can't seem to press any cells without getting an error. Am I supposed to implicitly fill something into the formular written above?

Thanks for your response!

Jo4x4 · Apr 10, 2022

ExcelWusgood said:
Hi both,

Could either of you perhaps dumb this down to make an Excel-novice understand?

Im in a similar situation as listed above. But im not confident how to use the formular written by @jmacleary. (also my excel is in a different language, but I have the translations down). If i write the exact formular listed, I get an error.

Any help is very appreciated!

Br

Hi, I know very little about how this works.

BSALV · Apr 10, 2022

Map1

A

B

C

D

E

F

G

H

I

J

K

L

M

2

3

value

4

5

6

this month

55

7

this month

55

Blad2

Cell Formulas
Range	Formula
B2:M2	B2	=TEXT(DATE(1,COLUMN()-1,1),"mmm")
B3:M3	B3	=RANDBETWEEN(1,100)
B6	B6	=INDEX($A$3:$M$3,MONTH(TODAY())+1)
B7	B7	=HLOOKUP(TEXT(TODAY(),"mmm"),$A$2:$M$3,2,0)

ExcelWusgood · Apr 13, 2022

Thank you for your answer @BSALV! Unfortunately i couldn't get it working and I've done some research the last few days without luck resolving my issue..

Here's the sheet im working in. What im seeking, is to produce a pie chart consisting of the current months costs, while updating automatically when a new month occurs.

Forskudsopgørelse og budget.xlsx