Referencing a single cell in a named range across several sheets in a formula

[MackieW]

Hello,

I am a beginner to excel who is learning everything from bottom up for a project!

I need help building a formula that will sum a specific cell in a named range across several sheets. This cell will be in a constant column, but will be changing row locations depending on where it is pasted.

To be specific:

I am keeping an inventory of items in a stock room. I've named the range based on the name of the product. Eg, "AppleJuice" The named range contains several columns of information about this product, but the one I'm concerned with is in Column J (Total Bottles Used).

I will be updating the inventory through daily sheets and need to sum up how many bottles are being used over the course of several sheets. That sum is then being subtracted from the Total Inventory of Bottles.

The current formula I have is =SUM(First:Last!J4)

This is working for now because it sums up all of this cells information from the first to the last sheet I am using. The problem is, is it means I HAVE to paste the named range in the 4th row.

Is there anyway to sum this up, perhaps using an INDEX formula so that when the row changes, I can still sum up a specific column of in a Named Range?

Thanks,
Mac

 

[dspel79082]

Mac, could you show a sample of how the items are spaced on the sheet?

 

[MackieW]

Well there are 10 columns explaining information about the products. Looks like this in one row.

Juice/ Producer/ Price/ Date/ Bottles Used (daily count)/ Bottles Used Total/ Formula Column (that finds bottles left over) Bottles Left Over. I am concerned with adding up the Bottles Used (daily count) across several sheets that will continually be added. However, the spacing on each sheet could. Apple Juice may be used in the first row, or the fourth, so when tallying up... I've named it in a Named Group as apple juice! Is there any way to always tally up the Apple Juice's "bottle used (daily count) cells across several sheets. They will be continuously in the same column, but I'd like to have the flexibility to move the row location.

 

[dspel79082]

Mackie, I believe this will work for you, just add another sumif to the formula for each sheet, and change the cells and columns to fit your sheets

in my example column A is where apple juice is located and column G is where the total used is located
you can use any rows you wish

=SUM(SUMIF(Sheet4!A:A,"apple juice",Sheet4!G:G),SUMIF(Sheet5!A:A,"apple juice",Sheet5!G:G))

 

[MackieW]

Hi Dspel,

This is definitely a step in the right direction. Unfortunately however, it's not working in my excel.

Is there a way to adjust the formula so it SUMS from Sheet 1 to Sheet 7.... as It will need to sum all of the totals?

 

[dspel79082]

Hey Mackie, I found an answer for this but I have to log off and may not be back til late tomorrow, so here is the link. It probably explains better than I can anyway. Hope this does it for you.


Debbie S

http://www.xl-central.com/sum-single-criteria-multiple-sheets.html