CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formulaspreadsheet
Connor Takata
Connor Takata

Reputation: 3

Using a cell's content within a formula pointing to another sheet

I'm putting together a budgeting workbook where I'll have a "Dashboard" sheet and subsequent expense sheets for each month - "June Expenses", "July Expenses" etc.

In the dashboard sheet, I have a row for each month and columns for each category. I made a formula for the month of June using SUMIF's to add up all the expenses for each individual category. I'm wanting to create a formula where I can drag down then over when I have next month's expenses.

*An example formula I have for expenses in a category is:

=SUMIF('June transactions'!$F:$F, F1, 'June transactions'!$C:$C)

In this case F1 is "Food & Drink".*

I'm curious if there is a way to use a reference to a cell that contains "June" rather than having to type in 'June transactions'!$F:$F. That way when I create a new row for July, I can drag down and over to populate the row and it's categories.

I know I could just create the first formula, manually type 'July transactions' and then drag across...but the more you know, right?

Thanks in advance!

I've tried using INDIRECTS but I continue running into referencing errors and all the quotation marks are starting to give me a headache :\

=SUMIF("'"&INDIRECT("A2")&"' transactions'!$F:$F", F1, 'June transactions'!$C:$C)

Upvotes: 0

Views: 60

Answers (2)

Martín
Martín

Reputation: 10177

In Google Sheets you don't need the single quotation marks inside INDIRECT, it just recognize that everything before the exclamation will be the name of the sheet:

=SUMIF(INDIRECT(A2&"_transactions!$F:$F"),F1,INDIRECT(A2&"_transactions!$C:$C"))

Upvotes: 1

lisboakotor
lisboakotor

Reputation: 521

If you're using Excel then this will work:

=SUMIF(INDIRECT("'"&A2&" transactions'!$F:$F"),F1,INDIRECT("'"&A2&" transactions'!$C:$C"))

If the quotations are confusing, you can change the sheet names to be one word so that the quotations are not needed (e.g. June_transactions) and then simplify the formula:

=SUMIF(INDIRECT(A2&"_transactions!$F:$F"),F1,INDIRECT(A2&"_transactions!$C:$C"))

Upvotes: 1

Related Questions