• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Divide rows individually from two spreadsheets and put result on another sheet

NARAYANK991 said:
Hi ,

It works for me. Check the uploaded file.

Narayan
Click to expand...
Yes I see. I'll re-check my sheet to see where I erred. But, I need to maintain the dynamic range anyways.
Here I think I am on to a solution using this formula below in B5 to generate the formula required to calculate the correct value.

In B5 I have formula:
="SUMPRODUCT"&"("&INDIRECT("C5")&"_data"&"/"&INDIRECT("D5")&"_data)/ROWS("&INDIRECT("C5")&"_data)"

where C5 has EURJPY and D5 has USDJPY

which produces result
SUMPRODUCT(EURJPY_data/USDJPY_data)/ROWS(EURJPY_data)

Now I'm trying to create a formula in A5 to convert this text string result in B5 to an executable formula. I tried =B5 but the result is the text string.
 
Hi ,

I still do not understand why you are avoiding the use of the entire named range within the INDIRECT function ; if you wish the named range name can be changed from EURJPY_data to anything else.

What exactly is your requirement ? Which cells will contain what ? Can you specify this ?

Narayan
 
westend9876 said:
In B5 I have formula:
="SUMPRODUCT"&"("&INDIRECT("C5")&"_data"&"/"&INDIRECT("D5")&"_data)/ROWS("&INDIRECT("C5")&"_data)"
Click to expand...
Hi, westend9876!
You're building a string there, you can't build formulas as strings, it'd be wonderful if Excel lets you, but it doesn't.
Regards!
 
SirJB7 said:
Hi, westend9876!
You're building a string there, you can't build formulas as strings, it'd be wonderful if Excel lets you, but it doesn't.
Regards!
Click to expand...
Hi. Just to update the post I found a workaround for converting strings into formulas by defining a name for the range of strings I'd like to convert and using the EVALUATE() function. Thank you for your assistance.
 
westend9876 said:
Ok Yes I see. Thanks. Works great!!!
Click to expand...
Hi. Here is the formula for the dynamic range EURJPY_data you named for me:
EURJPY!$B$2:INDEX(EURJPY!$B:$B,COUNTA(EURJPY!$B:$B))

However, every time the data in column B is deleted and re-populated with the new data the range loses the cell reference and returns:

=EURJPY!#REF!:INDEX(EURJPY!#REF!,COUNTA(EURJPY!#REF!))

Is there anyway to prevent this, maybe by manually placing the range(s) in another cell(s) and have the formula in the named range retrieve the columns from those cells?

Also, I used Indirect function in the named ranges successfully like this:
=INDIRECT("'EURJPY'!"&"$B$2"):INDEX(INDIRECT("EURJPY!"&"$B"&":$B"),COUNTA(INDIRECT("EURJPY!"&"$B"&":$B")))

But, my next calculation in the sheet based on the result of the named range uses SUMPRODUCT which does not work with Indirect. So Indirect will not be an option.

Thanks.
 
westend9876 said:
Hi. Here is the formula for the dynamic range EURJPY_data you named for me:
EURJPY!$B$2:INDEX(EURJPY!$B:$B,COUNTA(EURJPY!$B:$B))

However, every time the data in column B is deleted and re-populated with the new data the range loses the cell reference and returns:

=EURJPY!#REF!:INDEX(EURJPY!#REF!,COUNTA(EURJPY!#REF!))

Is there anyway to prevent this, maybe by manually placing the range(s) in another cell(s) and have the formula in the named range retrieve the columns from those cells?

Also, I used Indirect function in the named ranges successfully like this:
=INDIRECT("'EURJPY'!"&"$B$2"):INDEX(INDIRECT("EURJPY!"&"$B"&":$B"),COUNTA(INDIRECT("EURJPY!"&"$B"&":$B")))

But, my next calculation in the sheet based on the result of the named range uses SUMPRODUCT which does not work with Indirect. So Indirect will not be an option.

Thanks.
Click to expand...
Or is there an alternative to the next formula below that you provided to calculate the result of the named range result that can work with Indirect function:

=SUMPRODUCT((('Example-2.xlsx'!EURJPY_data/USDJPY_data)-$A$1)^2)

An alternative to this formula will allow me to keep the Indirect formulas in the dynamic named range and not worry about breaking link to cells.
 
Hi ,

Regarding your first question , you must be deleting the column ; I have tried deleting rows , copying and pasting data , and I don't lose the cell references. Can you describe the exact steps that you are following due to which you get the errors ?

As far as your second question is concerned , I am not able to grasp the problem ; what are the named range definitions , and what are the formulae using them ? Can you give more information , or even better upload a workbook with these , after clearing it of data ?

Narayan
 
NARAYANK991 said:
Hi ,

Regarding your first question , you must be deleting the column ; I have tried deleting rows , copying and pasting data , and I don't lose the cell references. Can you describe the exact steps that you are following due to which you get the errors ?

As far as your second question is concerned , I am not able to grasp the problem ; what are the named range definitions , and what are the formulae using them ? Can you give more information , or even better upload a workbook with these , after clearing it of data ?

Narayan
Click to expand...
I included workbook file name Example-2.1.

Yes the data in the column is deleted everyday and updated with the appended data. So, that's why I changed the dynamic range names from EURJPY!$B$2:INDEX(EURJPY!$B:$B,COUNTA(EURJPY!$B:$B)) to =INDIRECT("'EURJPY'!"&"$B$2"):INDEX(INDIRECT("EURJPY!"&"$B"&":$B"),COUNTA(INDIRECT("EURJPY!"&"$B"&":$B"))) so that the column references would not be deleted.

Now the problem is that my next calculation in A2 uses the result derived in A1. But the SUMPRODUCT formula in A2 does not work with results from Indirect function derived in A1.

So I need either another way to avoid the dynamic ranges from changing reference columns to #REF or another formula in A2 that does not use SUMPRODUCT.

Thanks.
 

Attachments

You generated the correct result. But every time I try to copy that formula into another cell, Excel stops working before I can even do Ctrl+SHT+Enter. See the screenshot attached.

The problem is using Sum or Sumproduct when the dynamic range name formula is based on Indirect Function.

How did you get the formula into cell A2 without getting the error I got on the screenshot?
 

Attachments

  • screenshot _Example_2.1.jpg
    screenshot _Example_2.1.jpg
    372.3 KB · Views: 1
Hi ,

I think the blame should rest on Excel !

When I first tried the SUMPRODUCT version of the formula , Excel crashed ; I tried it out several times , and each time Excel crashed.

When I changed to the SUM version ( array entered ) , and tried it out several times , copying the formula to other cells , Excel did not crash.

I am using Excel 2007 ; this may have something to do with it.

See this file where I have copied the formula to several other cells.

Narayan
 

Attachments

I see. How did you enter the equal sign without it crashing? I tried first to copy the formula in the address bar without the equal sign like this:
SUM(((EURJPY_data/USDJPY_data)-$A$1)^2)

Then I enter the equal sign and Excel crashes immediately before I can enter the array. I'm using 2007 also. Yikes! Excel has some pesky particularities.
 
I tried again and I see it looks like Excel does not like when I copy the formula. I must type it out completely in the address bar then enter array.
 
You must log in or register to reply here.
Back
Top