Hi all,
A bit of a complicated one that I may just be approaching wrong. Let's use stocks as an example.
Let's say I want to pull High and Low stock price from the internet each day and have it automatically added to a table. If it changes throughout the day, I want the table to update, but then once the day is over, I want to keep whatever numbers we ended with, and on the next line, start the same process the next day. Later, I want to be able to do calculations or analysis with the numbers I've ended up with (MIN, MAX, differences, etc)
The way I've thought to do this is the following:
1) Set up a tab where I get the data from the internet. Use a Query, connected to Google, that strips the high and low price and puts it in the sheet.
2) On another tab, make my columns be High and Low price, then make my rows be the dates. Up in the corner, I put =TODAY() to have the current date on hand
3) For each cell, set up an IF function that checks if that row's date is the same as today's date. If it is, make that cell equal to the number on the chart I got from the Query. If not....and this is where I get stuck
I want the cell to update as long as the date is today's date, but once it isn't anymore, leave whatever number we ended up with. I've tried using a circular reference (and ticking the "allow" box in Options), but that's causing a different problem. Let's assume B3 is where I want my data to end up, A3 is where the date is, 'Live Data' is the tab with my Query, and A1 is where my =TODAY() function is. When I use the following:
=IF(A3=A1, 'Live Data'!D27, B3)
I get the correct numbers, updating automatically, that stop updating after the day is over. So that's good. The issue is that in trying to perform certain calculations, they don't register as their displayed value. If I do a MAX function of a range of these cells, for instance, I get the result of 0. Other calculations DO work, though; adding B3+B4 gives me the correct arithmetic result, and if I do a few cells of addition, then try to find the MAX of THOSE cells, it works. If I input the function "=B3" into D3, and "=B4" into D4, I CAN'T successfully do a MAX on D3 and D4. It gives me 0 again.
It's like it's only able to render the value as an actual value under very specific circumstances. And I have no idea why.
So A) how can I make it recognize a number as a number, and B) is there a better way to do what I'm describing a need to do?
I also don't know if this is accounted for already. Surely I can't be the first person to want a cell that waits until a certain condition is met, then stores a value and holds it even if the condition stops being met. So maybe I just don't know the function that does that.
A bit of a complicated one that I may just be approaching wrong. Let's use stocks as an example.
Let's say I want to pull High and Low stock price from the internet each day and have it automatically added to a table. If it changes throughout the day, I want the table to update, but then once the day is over, I want to keep whatever numbers we ended with, and on the next line, start the same process the next day. Later, I want to be able to do calculations or analysis with the numbers I've ended up with (MIN, MAX, differences, etc)
The way I've thought to do this is the following:
1) Set up a tab where I get the data from the internet. Use a Query, connected to Google, that strips the high and low price and puts it in the sheet.
2) On another tab, make my columns be High and Low price, then make my rows be the dates. Up in the corner, I put =TODAY() to have the current date on hand
3) For each cell, set up an IF function that checks if that row's date is the same as today's date. If it is, make that cell equal to the number on the chart I got from the Query. If not....and this is where I get stuck
I want the cell to update as long as the date is today's date, but once it isn't anymore, leave whatever number we ended up with. I've tried using a circular reference (and ticking the "allow" box in Options), but that's causing a different problem. Let's assume B3 is where I want my data to end up, A3 is where the date is, 'Live Data' is the tab with my Query, and A1 is where my =TODAY() function is. When I use the following:
=IF(A3=A1, 'Live Data'!D27, B3)
I get the correct numbers, updating automatically, that stop updating after the day is over. So that's good. The issue is that in trying to perform certain calculations, they don't register as their displayed value. If I do a MAX function of a range of these cells, for instance, I get the result of 0. Other calculations DO work, though; adding B3+B4 gives me the correct arithmetic result, and if I do a few cells of addition, then try to find the MAX of THOSE cells, it works. If I input the function "=B3" into D3, and "=B4" into D4, I CAN'T successfully do a MAX on D3 and D4. It gives me 0 again.
It's like it's only able to render the value as an actual value under very specific circumstances. And I have no idea why.
So A) how can I make it recognize a number as a number, and B) is there a better way to do what I'm describing a need to do?
I also don't know if this is accounted for already. Surely I can't be the first person to want a cell that waits until a certain condition is met, then stores a value and holds it even if the condition stops being met. So maybe I just don't know the function that does that.
