Formula that correlates cell text to a sheet in workbook

Hello All,

I'm working on a formula that will auto populate total payroll for different departments. I will use this to compare the current month to the previous month for each department within a single workbook. Both the previous month and current months payroll spreadsheets will be included in the workbook. I could write the code as, =sumifs(previous!D:D,previous!H:H,"=Management",previous!G:G,">600000") and rename each sheet to current and previous. I would rather not have to rename the sheets for ease of use, since there will be 12 of them per year and seeing each months named sheet will be less confusing for anyone else looking at the work.

At the top of each chart on my analysis spreadsheet I name the columns of data Jan and Feb, for example, which is the same name as the sheet containing the payroll for each month. I would like the formula to recognize what I type in that specific cell and correlate it to the matching sheet to pull the info.

In other words if I type Jan in a given cell, I want the macro to do =sumifs(Jan!D:D,Jan!H:H,"=Management",Jan!G:G,">600000"), where the sheet I import into the analysis workbook will be named Jan.

Thoughts?

Thanks in advance

A side question, I condensed this code as the forum will not allow me to post a 'less than symbol'. Any thoughts why? I get a popup from securi firewall which our IT guy says is not our firewall.

I would like the formula to recognize what I type in that specific cell and correlate it to the matching sheet to pull the info.

Sound like a job for superm....oh wait, wrong 1 LOL

You can do this with INDIRECT, something like this...
=sumifs(indirect("'"&A1&"D:D!","'"&A1&"!H:H","Management","'"&A1&"!G:G",">600000")

Although, using INDIRECT on full-column references could slow your file down, so consider restricting the range to 2-3 times what you think you would need (or just that table, if it wont grow at all)

Full-column references are like D:D, that is over 1 million rows, rather than $D$1:$D$160 000

Try it this way then, I just typed that 1 here, looks like I was a bit inaccurate

=sumifs(indirect("'"&A1&"!D:D",indirect("'"&A1&"!H:H"),"Management",indirect("'"&A1&"!G:G"),">600000")

Happy to help and thanks for the feedback

Sure, you just need to reference the cell containing that, instead of hard-coding it.

=ROUND(SUMIFS(INDIRECT("'"&D$10&"'!D:D"),INDIRECT("'"&D$10&"'!P:P"),$E10,INDIRECT("'"&D$10&"'!L:L"),">610000",INDIRECT("'"&D$10&"'!L:L")," < 620000"),0)

where E10 would contain the dept name

maybe upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

FD, what I meant was that I initially tried that suggestion and it didn't work. It did when I tried it again, I must have initially deleted something I wasn't supposed to.

I ran in to one final problem with the formula. Just needs to display an area where the department is blank. I tried =ROUND(SUMIFS(INDIRECT("'"&D$10&"'!D:D"),INDIRECT("'"&D$10&"'!P:P"),$E10,INDIRECT("'"&D$10&"'!L:L"),">610000",INDIRECT("'"&D$10&"'!L:L")," < 620000"),0)

where E10 is a blank cell but it just returns 0, and there are for sure blanks in the file. The total should be $145

Thanks


disregard. "" works wonders when used properly.

::facepalm::