Monetary Denominations with Array Formulas

[excellenthelp]

I have developed a monthly budget sheet that works well in Excel. It lets me break down a paycheck to the denominations of each item the paycheck will cover.

Example: Focus on January (Enero) on the Budget tab. If one earns $1,000 on 1/1/17, the info will be filled out as shown in the range G3:G5. Then I want to see the denominations for that paycheck, I will mark an "X" in G6. The Denomination section (A4:B11) will then show the data (right now it is blank).

When one gets the paycheck for 1/15/17, the same process will be used. However, the denomination will not show for the next paycheck unless the X is marked in H7.

For some reason, the array formulas on the Denominacion tab would work for all months in Excel, but not for all months in Google Sheets.

The code is (starting in cell B4 and down in Column B on Denominacion tab):

=IF(ISBLANK(INDIRECT("Budget!"&$B$2&A4)),"",INDIRECT("Budget!"&IF(RIGHT($B$2,1)="Z",CHAR(CODE(LEFT($B$2,1))+1),LEFT($B$2,1))&CHAR(65+MOD(CODE(RIGHT($B$2,1))+1-65,26))&A4))

Maybe the rest of the formulas on the same tab needs some fixing too?

Here is the Googlesheet: https://docs.google.com/spreadsheets...it?usp=sharing

[avk]

Look attach file.
Addition : In sheet name "Denomacion" in cell "C44" formula is :

Please Login or Register  to view this content.

In above formula shown "#REF!,0," highlighted i change the range.

Please Login or Register  to view this content.

Also inseart "x" in cell "H7"

[FDibbins]

Look attach file.

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[excellenthelp]

AVK,
Thank you, but I was seeking to do this on Google Sheets instead of Excel. The exact same Google Sheet is a copy of the Excel workbook. I am trying to migrate the workbook from Excel to Googlesheets. I suspect the Array formula is acting up on the Google Sheets whereas the same array formula is performing just fine in Excel.

[avk]

Dear Excellenthelp : A doc saves every 3-4 minutes. A spreadsheet saves every time you change a cell and then leave it (Enter, Tab or arrow). It does save automatically, on Google's servers.
Check link posted in your first post.

[excellenthelp]

AVK,

I re-opened the GoogleSheets, the denomination section on the Budget tab is not showing anything

Nor does the Denomination tab show anything neither. The formulas are not still working on the Denomination Tab.

[excellenthelp]

I have now found the culprit! It is at the end of the formula in Cell B4 on Denomination Tab. I had removed

Please Login or Register  to view this content.

from the formula below:

Please Login or Register  to view this content.

So now, the working formula is:

Please Login or Register  to view this content.

Making this as SOVLED