Non-volatile alternative to INDIRECT

[elliotencore]

Hi All,

I have the current formula in place:

Please Login or Register  to view this content.

It works fine, however it is making my spreadsheet incredibly slow as it is volatile.

I've read somewhere about using INDEX as an altertnative, but no idea where to start with that, and if it would even apply to this?

My range "sheetcalc" contains 1st, 2nd, 3rd,... 31st as that is what my sheets are named (each day of the month)

My original formula to do this was 31 SUMIF's, I may resort back to this, but any help would be appreciated to try and condense that formula into something similar to above, that doesn't re-calculate every time I make a change to the workbook.

[Marcol]

From your post I can't see why you are using INDIRECT() in the first place, best you post a sample of your workbook so that we can see the whole picture.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[elliotencore]

Apologies. I cannot attach a sample file right now as it is for work, however I will try to put something simple together to upload.

Basically I have 31 sheets to sum together, for each day of the month. I was looking for a solution, as I know the SUM formula can sum multiple sheets, i.e.

Please Login or Register  to view this content.

However this can't be done for a SUMIF, so I used INDIRECT to achieve it within the SUMIF, but due to it's volatility it is incredibly slow and the workbook is taking too long to load.

My original solution as I said, was 31 SUMIF's...

Please Login or Register  to view this content.

The second half of my formula is looking at Cell E6, which contains the letter of the Column I wish to SUM, so that I can drag the formula across.

[oeldere]

Your allowed to use an VBA solution (macro)?

In that case can use a macro to summurize the data on a summurize sheet.

After that is a peace of cake using pivot tables.

[elliotencore]

VBA is fine I'm using it in some of my projects but still relatively new to it all

[oeldere]

Then please post an excel file, with 2 sheets of data, without confidentional information.