Shortening the code...?

[excellenthelp]

Hi - I am trying to figure a way to shorten the code and have it run horizontally across the spreadsheet.

The code would check if there is any data typed in a cell on the farthest right of the spreadsheet to the left in this order (from column EQ to column A). If the cell (row 7) is marked 'X' and then run the code, otherwise, dismiss the code from running. Leave B4="" in the code.

It would be nice to be able to run the code from Budget!A$7 to Budget!EQ$7 if possible without making the code any longer... The code below seems to have many duplication due to having to go through each column...

Please Login or Register  to view this content.

Any help would be greatly appreciated!

[Bernie Deitrick]

Yes, there are many, many ways to shorten 'the code' - which is actually a formula.

But your are using values in multiple cells to determine the value returned by the formula, so it would help if you posted a workbook with your formula so that we can ensure that what we propose gives you what you actually want. And I'm not sure what you mean by "have it run horizontally across the spreadsheet" - copy the formula across the sheet, or refer to an entire row or....?

[excellenthelp]

Okay, Bernie. I have attached a sample.

The main focus is on the DENOMACION part of the spreadsheet (upper right corner) and the tab "Denomacion" where the formula lies in and would need some tidying up.

Thanks!

SampleWB.xlsx

[newdoverman]

One idea for you at this point
Create a table of the months and the code that goes with them and assign the range of months and codes the name Month then use the name in a VLOOKUP.

	A	B
1	Abril	AO
2	Agosto	CK
3	Diciembre	EG
4	Enero	E
5	Febrero	Q
6	Julio	BY
7	Junio	BM
8	Marzo	AC
9	Mayo	BA
10	Noviembre	DU
11	Octubre	DI
12	Septiembre	CW

In Denomacion!B2 enter

Please Login or Register  to view this content.

This will replace the much longer formula currently in use.

[Bernie Deitrick]

In cell C4 of your sheet Denomacion, try using this:

=OFFSET(Budget!$A9,0,MATCH($B$1,Budget!$1:$1,FALSE)+COUNTIF(OFFSET(Budget!$A9,0,MATCH($B$1,Budget!$1:$1,FALSE)+2,1,7),"<>"&"")+1)

But note that you need to enter 0 into your Budget sheet instead of blanks when the value is 0 so that those can be counted.

[excellenthelp]

Newdoverman, Thank you for your code. However, it is not what I was looking for. My char(code) is already short enough for that specific code.

Bernie,
I have put it in Cell C4 of my sheet Denomacion and it did not work. I do not understand why we are referring to A9 on sheet Budget. help...?

[excellenthelp]

Also, I noticed that my code works only for January, but not Feburary. That is what I am trying to figure out how to write a code that will run all the way through all the months. But without writing a too long code.

[Bernie Deitrick]

You need to correct two habits that you have - leaving values blank when they should be 0, and entering spaces to remove values instead of deleting them.

Here is your workbook with those two issues corrected, with my formulas shown returning the same values as yours.

SampleWB V2.xlsx

Note that when you enter values for March, my formulas will continue to work.

[excellenthelp]

Bernie,

Try that with Feburary. When I mark x in cell S7, the denomacion will not update with either of our codes.

I am trying to have the code run through every month wherever the X is marked in row 7 and if there is value of 1 or greater in row 6.

[Bernie Deitrick]

Your original formula has nothing to do with x in a cell. It appeared to pick up the last entry within the month blocks of 7 columns, which is what my formula will do, for the month that is shown in cell B1.

[excellenthelp]

Yes, it has to do with an X in a cell. If I mark an X in cell J7 on sheet Budget, the denomacion will show $884 on the upper right corner of the sheet Budget same with cell I7 and it will show $930.

However, if I am to mark an X in cell S7 on sheet Budget, the denomacion will not update.

[Bernie Deitrick]

I think it would better if you described what you have, and how you want to extract data. Is it from the column furthest to the right with an 'x' in row 7?

In C4, array-enter (enter using Ctrl-Shift-Enter)

=IF(B4="","",INDEX(Budget!9:9,MAX(IF(Budget!$7:$7="x",COLUMN(Budget!$7:$7)))))

[excellenthelp]

Yes.

The data would be like this:

if there is an 'X' in row 7, go to the farthest right 'X' in the row.

The working formula for Ahorro in the denomacion corner on sheet Budget for column J on the same sheet would be this: =IF(J7>0,J9,"").

I want the formula to be able to calculate for all the cells in same row for every month in row 9. This will be then copied and pasted from row 9 to 38 and row 42 and 43.

Hope this is more clear now. =)

[Bernie Deitrick]

This works off the x furthest to the right.

Sample WB works off x in row 7.xlsx

[excellenthelp]

After running a test with the sample WB you attached. It does not work well.

Thank you for your effort Bernie. If I find a solution, I will update this thread. =)

[excellenthelp]

By coming back to this thread, I realized that the workbook did actually work. I just had to fix the sum forumla on the denomacion sheet. However, I would like to include the entire row 7 to track the farthest right "x".

The "x" would move column by column from column F to column EO.

What change to the formula should I make in order to make it include the rest of row 7?

I will work on this while I wait for a response.

Thanks!

[excellenthelp]

I see that array formulas are creating issues when I update the Budget sheet because I would then have to go to the denomacion sheet to press CTRL+SHIFT+ENTER to make the array formula run.

-___-

I want the entire workbook to automatically update when a value is added/altered.

[Bernie Deitrick]

My example already found the x furthest to the right in row 7.