IF I CHOOSE TEXT FORMULA SHOULD MOVE VALUE To ANOTHER SHEET

"if I choose salary/dividend/service in B4"
How are you going to do that?

Or do you want ALL values to appear in the table which is not actually choosing anything.

Originally Posted by JohnTopley

You will need VBA to do this.

No you won't.

Sheet1!E9 has two spaces between "1.2.2:" and service. Change this to one space as per the other descriptions.

Then

in mainform!C3
=IFERROR(VLOOKUP("1.1: "&C$2,INDIRECT($B3&"!B$4:E$1000"),4,0),"")

in mainform!D3
=IFERROR(VLOOKUP("1.2.1: "&C$2,INDIRECT($B3&"!B$4:E$1000"),4,0),"")

in mainform!E3
=IFERROR(VLOOKUP("1.2.2: "&C$2,INDIRECT($B3&"!B$4:E$1000"),4,0),"")

and copy down the columns.

The Income tax values will apear if appropriate.
If you change the selection by drop down menu the mainform sheet will reflect the change.

You've copied it incorrectly.
If you copy the three formulas into a blank spreadsheet that error doesn't occur so you must have changed something.

Did you do this as suggested above?
"Sheet1!E9 has two spaces between "1.2.2:" and service. Change this to one space as per the other descriptions."

Apologies, the formulas on the forum are different to what I entered on the spreadsheet.
They should be

in mainform!C3
=IFERROR(VLOOKUP("1.1: "&C$2,INDIRECT($B3&"!$B$4:E$1000"),4,0),"")

in mainform!D3
=IFERROR(VLOOKUP("1.2.1: "&D$2,INDIRECT($B3&"!$B$4:E$1000"),4,0),"")

in mainform!E3
=IFERROR(VLOOKUP("1.2.2: "&E$2,INDIRECT($B3&"!$B$4:E$1000"),4,0),"")

and copy down the columns

That's strange, I had this working yesterday with several figures appearing.
Now I'm only getting a figure for jan/Salary 20%.

I'll take another look at this...

Originally Posted by tsikara

Thank you for your help
The formula is working but another problem is that, for example when i choose salary it appears in all three cells.
I need it to take for example only salary value when i choose it in month sheets.

Can you post the file?

You've changed the descriptions.
Sheet1 was

1.1 Salary
1.2.1 Dividend
1.2.2 Service

It's now

1.2.1 Salary
1.2.2 Dividend
1.2.3 Service

You've reflected this change correctly in the formulas. But...
Your jan feb mar sheets have different descriptions, e.g. feb has 1.2.1 Dividend, 1.2.1 Dividend doesnt exist on Sheet1.
Same for Mar 1.2.1 Dividend
Apr has 1.2.2 Service, 1.2.2 Service doesnt exist on Sheet1

Also your formulas are incorrect.
They should be

n mainform!C3
=IFERROR(VLOOKUP("1.2.1: "&C$2,INDIRECT($B3&"!$B$4:E$1000"),4,0),"")

in mainform!D3
=IFERROR(VLOOKUP("1.2.2: "&D$2,INDIRECT($B3&"!$B$4:E$1000"),4,0),"")

in mainform!E3
=IFERROR(VLOOKUP("1.2.3: "&E$2,INDIRECT($B3&"!$B$4:E$1000"),4,0),"")

and copy down the columns

as per post #10

Our format can't recognize ( , )-this symbol
we use ( ; ) instead.

Main problem is that when i choose salary, it takes all three category value in mainforms then when i choose dividend it take rest two, and only third works as it should, because there is no category left.
So when i choose salary it should take only "salary" value and so on.

The , ; is irrelevant, just change the commas for semi colons as per your regional setting.

Here is a copy of the file with correct figures.
See if you can amend the commas to semicolons

If you have problems with that file then use this file.
I have made each formula a piece of text (inserted ' at the beginning)

Do a Find/Replace , with ;
Manually remove the the apostrophe at the beginning of C3 D3 E3 and the text will change to formulas suited to your region.
Copy them down the columns.

Your sum is wrong in Jan
You're summing E3:E7, surely this should be E4:E7 ?

I cant help you with this, the example data you originally provided was ONE entry for each month tab.
I assumed it was going to be a maximum of 4 different type, but now you have multiple entries per month tab with the same "1.2.1" description so a VLOOKUP won't work.

I don't have any more itme to spend on this as this is getting far more complicated than I envisged.
Hope you find someone to sort this out.