Cell Value and TREND

[pulmonaryKid]

Hi everyone, I'm a new poster though I've lurked intermittently in the past.

I'm using the following :

="=Sheet1!B"&ROUND(TREND({9,29},{16,20},ROW()),0)

I'm attempting to display the value of Sheet1!B9 in Sheet2!B16

And copy/paste the formula so that the value for Sheet2!B20 will show the value of Sheet1!B29 and so forth...

Unfortunately, rather than showing the value, the cell is showing text...

So Sheet2!B16 cell shows the text "=Sheet1!B9"... Which means it's not showing the actual formula, but simply showing text...

Any ideas would be great!

Thanks in advance,

pulmonaryKid

 

[pgc01]

Hi

You are just building a string with the address and that's what excel shows you.

To convert a string to a reference, wrap it in Indirect

=Indirect(your formula)

 

[pulmonaryKid]

Thanks for the help... Unfortunately when I include "Indirect" I get a REF error.

The formula ends up looking like this :

=INDIRECT("=PatientInfo!B"&ROUND(TREND({44,64},{24,28},ROW()),0))

and the end result after execution but before error is

=INDIRECT("=PatientInfo!B44")

But then I have a REF error...

Any ideas?

Thanks again...

 

[pgc01]

Hi

The "=" sign should not be in the string.

Try:

=INDIRECT("PatientInfo!B"&ROUND(TREND({44,64},{24,28},ROW()),0))

that will result in

=INDIRECT("PatientInfo!B44")

 

[pulmonaryKid]

YOU ARE AMAZING. You literally just saved me 10+ hours of work. Thanks again!

 

[pgc01]

I'm glad it helped. Cheers!