Passing a formula with a variable

[Beneindias]

Hi guys.

I will try to explain my problem.

I have an excel file with 2 worksheets.

In worksheet 1, I have a table with a column that has a function that goes to worksheet 2, and finds a value in a table.

In this table, I have column with various numbers, but there are two rows with a formula, for example 14*2*(100 - R) where "R" is a value that I have in worksheet 1, in cell $BB9.

Can I have that "R" value pointing to my $BB9 cell and doing the calculations? Or do I need to "hard code" this formula?

So, to simplify:
Worksheet1!A2 -> =worksheet2!D1 [can be D2, D3, D4, it depends]

Worksheet2!D1 = 14*2*(100 - worksheet1!BB9) [it can be BB10, BB11, depends on the table row]

Can anibody help me, please?

Thanks

 

[James006]

Hi,
If my understanding is correct, you need Indirect ...
you can test following:

=14*2*(100-INDIRECT("sheet1!BB"&ROW()+8))

 

[Beneindias]

Hi,
If my understanding is correct, you need Indirect ...
you can test following:

=14*2*(100-INDIRECT("sheet1!BB"&ROW()+8))

I think that it's no exactly what I wanted.

This is part os my table in worksheet 1:

vencimentos v2.4.3 - 2 semestre 2023.xlsm
	BB	BC	BD	BE	BF
9	800,00 €	11,00%	88,00 €	14,50%	14,5%*2,3*(1093,31 - R)
10	880,00 €	11,00%	96,80 €	14,50%	14,5%*2,3*(1093,31 - R)
Salarios

Cell Formulas
Range	Formula
BB9:BB10	BB9	=[@[Total Vencimento Base]]+[@[Total Retroativos]]+[@[Total Horas Extra]]+[@[Total Prémios]]
BC9:BC10	BC9	=IF(Retencoes!$C$6="","Colocar taxa na página de retenções",Retencoes!$C$6)
BD9:BD10	BD9	=[@[Total Valores Sujeitos a Desconto]]*[@[Taxa Seg. Social]]
BE9:BE10	BE9	=IFERROR(INDEX(Tabela1,MATCH(Salarios!$BB9,Tabela1[Remuneração],0),3),IFERROR(INDEX(Tabela1,MATCH(Salarios!$BB9,Tabela1[Remuneração],1)+1,3),0))
BF9:BF10	BF9	=IF(IFERROR(INDEX(Tabela1,MATCH(Salarios!$BB9,Tabela1[Remuneração],0),1)=2,INDEX(Tabela1,MATCH(Salarios!$BB9,Tabela1[Remuneração],1)+1,1)=2),Retencoes!$E$17,"Não")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9:BT10	Expression	=$C9=VERDADEIRO	text	YES

This is my table in worksheet 2:

vencimentos v2.4.3 - 2 semestre 2023.xlsm
	B	C	D	E
14	Remuneração Mensal
15	Escalão	Remuneração	Taxa	Parcela a abater (€)
16	1	762,00 €	0,00%	0,00 €
17	2	886,57 €	14,50%	14,5%*2,3*(1093,31 - R)
18	3	932,14 €	21,00%	21%*1,3*(1350,22 - R)
19	4	999,14 €	21,00%	114,14 €
20	5	1 106,93 €	26,50%	169,09 €
21	6	1 600,36 €	28,50%	191,23 €
22	7	1 961,36 €	35,00%	295,26 €
23	8	2 529,05 €	37,00%	334,48 €
24	9	3 694,46 €	38,72%	377,86 €
25	10	5 469,90 €	40,05%	427,18 €
26	11	6 420,55 €	42,72%	573,22 €
27	12	20 064,21 €	44,95%	716,08 €
28	13	1 000 000,00 €	47,17%	1 162,51 €
Retencoes

As you can check, In worksheet 1, column BF, I have a formula that finds the right value in the table that I have in worksheet 2.
The problem is that in line 2 and 3, instead of only a value, we have a formula. in this formula, the value R is the value that I have in my worksheet's 1 column BB.
So, I was looking to have this R dynamic, when it's used in table of worksheet1.

the value R will vary depending on the row in table of worksheet 1.