Values does not correspond to month change

[jianyeo]

Hi guys, I urgently need help here.

When i change the month using the combo box, I need the value that is associated to the month to change as well. But as you can see from the attachment below, when i change the month, the value doesn't change.

I do not know how to go about doing it, can anyone please help? Really appreciate your help. Thanks.

[NBVC]

Which value needs to change and changed to what?

[arthurbr]

Hi and welcome to the board,
not everyone has 2007 or a file converter, so in the future attach xls files instead of xlsx - Thx

[jianyeo]

Hi,

Sorry about it I'm new to this forum.

I need to change the value of salary etc. eg, for jan month salary is 80, so if i choose the combo box and select other month, I would expect the corresponding cells to change its value as well.. But i dunno how to do it.

Hope this clarifies. Thanks.

[NBVC]

How about:

=INDEX($AB$10:$AC$21,MATCH(D$5,$AC$10:$AC$21,0),1)

copied across...

but you will have to change the values in AB10:AB21 to be hardcoded... right now they will cause circular references since you have formulas that refer back to the same cells you are changing.

[jianyeo]

Sorry, but where do i copy across?

I cant seem to get it working...

Just one favour, are you able resend me the xls file with the corrected formula? Thanks!!

[NBVC]

You paste the fomula in D7 and copy it across to O7.

See attached...

Note: I also changed formula in D6:O6 to obtain month a different way without relying on tables....

Note2: I change the values in AB10:AB25 to hard-code them...

[jianyeo]

Hi NBVC,

Thanks for your help!. But i'm afraid that's not really my intention. I got your point but currently those salary fields etc are to be input, so it will tabulate the amount for the month... and when i change the month under the combo, the amount will change accordingly...

able to help me with that?

also one thing i dont understand (Note: I also changed formula in D6:O6 to obtain month a different way without relying on tables....).... able to elaborate more on this?

ur help is very much appreciated!

[NBVC]

I don't follow...

What exact fields are you trying to change and based on what fields in your tables?

I am basically doing a lookup of the month in the same column against the table in AB10:AC25? Is that not right? What is then?

[jianyeo]

ok i try to simplify it. take the total income (net) section for eg, i would need user to input the amount for the field specified below:
Salary/Wages
Salary/Wages (Spouse)
Bonus
Other
Other

Then it'll auto sum up the amount under each month,eg feb has 160 dollars. But the problem lies that if i select the combo box to another month say oct, then feb amount is no longer 160 dollars, it gets "overwritten"... so what i'm hoping to have is that once i get the amount for each month, no matter what month i choose in the combo box, the correct amount still correspond to the month.

hope im clear in my explanation =)

[NBVC]

So you want to type numbers into say D7:D11 for all the months in row 5 and then be able to switch the months around, via the drop down and have those typed numbers automatically move along with the change?

Well, if that is true, then you will need some VBA or you will have to recreate a similar "input only" table on the side and then have this table refer to that one, via lookup formulas..

[jianyeo]

yeps that's what i wish to do.

recreate a similar "input only" table??

can i do it without any macros or vba?? sorry but are u able to provide some help for me??

[NBVC]

Ok,

I created a table in AH:AU that you would put your inputs in... you can put this table on another sheet to make it easier...

Then I used formula in D7:

=HLOOKUP(D$5,$AJ$1:$AU$8,ROWS($A$1:$A2),0)

and copied it across and down the table.

[jianyeo]

Thanks a lot!!!..

I'll do it this way, really a great workaround!... definitely ease my woes a lot...

btw dude, would you have msn contact to exchange or such? Let me know if it's possible...

[NBVC]

You can PM me through the forum, but Excel-related questions should be put in new threads in the forum for all to see and learn.

In the meantime, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save