Link To Table Instead Of Range

[tracer773]

Hello - I am hoping this is an easy fix. The short question is, why are my formulas working when the cells are linked to a normal range of cells, but if those linked to cells become a table, the formulas stop working?

The long question:
My software exports data as a CSV. I have written formulas that work fine when I cut and paste the exported data into the excel sheet. However, manually cutting and pasting is time consuming. So I was trying to create a connection so that whenever the CSV file gets updated, my reporting document would auto update. I have been going through the import process through the data tab. This imports the data into my excel report as a table. When I then link my formulas to the data in the table, the formulas stop working.

I tried to link the cells directly to the CSV file, but every time I closed the excel file, the connection to the CSV file would be lost. Through my research, it appears that to correctly link to a CSV file, I must go through the import process.

 

[SpillerBD]

the import process..? Which one?
With the introduction of PowerQuery, Excel has two built in methods and both can create connections to a source file.

The formulas work for a range but not a Table? What formulas or functions are you having trouble with? Aren't your formulas working in a Table manner and if not, why not?

 

[sandy666]

Try PowerQuery aka Get&Transform: Connect to an Excel or CSV file (Power Query)

 

[tracer773]

I click on Data and then From Text/CSV.

My formulas are fairly simple.

Formula 1 = Looks at Column A to see if they include a certain word and if true, returns a 'Yes.' These formulas are still working.

Formula 2 = If formula 1 equals Yes, then it multiplies the value in column B by 2 and returns that value. This is the formula that is not working.

 

[sandy666]

maybe values in B column are as text not as number

btw. if you want use PQ : Data - New Query - From File - From CSV. It's up to you.

 

[tracer773]

That was it, which I suspected and tried a few things to fix.

For some reason during the import process, it was converting it to text. In the import editor, I had to select those columns and transform them into numbers.

 

[sandy666]

CSV is a text file, doesn't matter if it will be open in Notepad or Excel values usually are as text