combining functions

[keinia]

hello all, looking to combine indirect and sum functions. i'm pulling data from multiple sheets and will need that total sheet to be able to chance with the drop down list. normally all that would need would be the indirect function. but the part that's stumping me is the sum function that's needed as well. right now my cells look like this =SUM(Vendor2014!D258:Vendor2014!D260) but as you can see that limits it to only the "2014" page as i have pages extending up to 2020 i need those totals to adjust as well. i thought at first the formula would look like this =sum(indirect("'&B3&"'!D258:"'&B3&"'D260)) then tried =sum(indirect("'&B3&"'!D258":Vendor2014!D260)) im sure this is an easy one but i cant figure this out. anyone have an idea?

Thanks

[JeteMc]

Try this:

Please Login or Register  to view this content.

Let me know if you have any questions.

[keinia]

Thanks but no that will give the the sum total of all the sheets yes but i need the sums to coincide with the drop down box thank you but this wont work.

[FDibbins]

For a start, that formula does not seem right...
=SUM(Vendor2014!D258:Vendor2014!D260)
That (I would think) would look something like =SUM(Vendor2014!D258:D260)
Try this...
=SUM(indirect("'Vendor"&B3&"'!D258:D260"))
with the year being in B3

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

[JeteMc]

Borrowing from this thread: http://www.excelforum.com/excel-gene...le-sheets.html
I modified to (hopefully) work with your criteria:

Please Login or Register  to view this content.

The formula assumes that the names of the first and last sheets to be accessed are in C1 and C2.
Let me know if you have any questions.

[jewelsharma]

for one sheet:

Please Login or Register  to view this content.

for 2 sheets:

Please Login or Register  to view this content.

for 3 sheets:

Please Login or Register  to view this content.

keep adding as more INDIRECTS as needed.

[keinia]

this worked perfectly thank you! now to fix the hundreds of cells lol

[FDibbins]

this worked perfectly thank you! now to fix the hundreds of cells lol

Which 1 worked for you? (just curious)

[keinia]

this worked perfectly! thank you so much!

[keinia]

keeps throwing it on the bottom lol yours did FDibbins, along with the formula change, for some reason it was working with the original string i was using before but yours is cleaner and the indirect worked like a charm, just had to modify for the full sheet name but worked perfectly

[FDibbins]

Thanks, Im just happy you got it workling

[keinia]

thanks again!

[jewelsharma]

Happy to learn that it worked for you. Please mark the thread as SOLVED and do consider appreciating FDibbins and JeteMC for their effort and contributions.