Using a word word variable in a formula

1
votes

I am compiling a sales analysis sheet and I am each time I need to make a change I have to go through dozens of cells.

If I have the sheet laid out like the following:

Months - > Across the top

Revenue Channel Names -> down the side

Can I stipulate the name of the month and the name of the channel in the formula based on the cell instead of doing it manually?

What I currently have:

=SUMIF(APRIL!$E:$E,"CHANNEL_NAME*",APRIL!$K:$K)

What I want:

SUMIF([C$1]!$E:$E, "[$A2]*", [C$1]!$K:$K)

Where the cell C1 is the name of the month (C changing from column to column) Where A2 is the name of the revenue channel (Cell number changing depending on the channel)

1
excelexcel-formula
look into INDIRECT function for [C$1]!$E:$E and [C$1]!$K:$K. For "[$A2]*" simply use A2 & "*" – Dmitry Pavliv
possible duplicate of Using value from an existing cell inside a formula – SeanC
The Indirect I don't think would apply since I am using it inside of the formula and it is referencing a cell on another sheet in the workbook. Your other comment of the use of the & worked like a charm. What I am attempting to accomplish is to reference month name using a static cell to help reference the sheet name - Where static cell would be the name of the month. January!E:E (referencing the Jan Sheet) - > "C1"!E:E – Andrew

1 Answers

0
votes

INDIRECT will definitely work for you here. I've used it for a similar purpose many times in my casino analysis.

Try:

=SUMIF(INDIRECT('" & C$1 & "'!$E:$E"), $A2 & "*", INDIRECT('" & C$1 & "'!$K:$K"))

Cheers,

Z