Forum Discussion
Help: Formula multiple sheets and columns
Hi, I need help finding a formula. Have tried different ones it sends me an error (SPILL).
Sheet One I have four columns, all integer values. Column A has Plots, Column B has Temp1, Column C has Temp2 and Column D has Temp3. There are 1500 for each plot, total 45k, and I need to put their corresponding values of each Temp which are coming from another sheet (Test-Temp Sheet, each temp on different column). Any idea? Thanks!
Sheet One looks like this....
Plot Temp1 Temp2 Temp3
1
1
1
1
Test-Temp Sheet looks like this...
Plot Temp1 Temp2 Temp3
1 1.56 2.08 0.52
2 4.68 2.6 0.1
3 6.3 8.52 -0.52
Thanks!
Joa14 OK, that's because you are referencing the entire column A. Excel then tries to return the if_true and if_false values for all 1 million+ rows in the sheet. Enter such a formula anywhere else than on the first row and you will indeed get #SPILL! without the dotted lines.
(By the way, why do you have "P3"? That will return the text P# when A does not contain 1. Remove the "" if you intend to return the value of cell P3.)
So move it to row 1 in the column where you have the formula now and change it to:
=IF($A:$A,$O1,P1)
You'll get a #VALUE! error for the first element and it will probably not give you the result you expect.
You need to limit the range from column $A:$A to, for example A$$2:$A$100. Can't tell if that will give the correct answers either. Up to you. You need to share the file (upload to Onedrive or similar and post the link here) if you can't upload it to the forum directly. And show what the expected results are.
4 Replies
Joa14 You don't show the formula that produces the #SPILL! error, but it's trying to output the result in multiple cells in one go. Click in the cell with #SPILL! and note the range surrounded by the dotted line where it wants to show the result. For instance:
Now, empty all cells that block the output. In this case the three "x" below #SPILL! and the formula will reveal its result. Up to you to determine if it's the correct one.
Thank you, Riny.
I did not see the dotted line when clicking the cell with the Spill error. It did not work. I copied and pasted the values from sheet two to sheet One to make things easier so the formula I am using is: =IF($A:$A,$O3,"P3"). Now the sheet looks like this:
A B C D O P Q R
Plot Temp1 Temp2 Temp3 Plot Temp1 Temp2 Temp3
1 1 1.56 2.08 0.52
1 2 4.68 2.6 0.11 3 6.3 8.52 -0.52
1
1
1
1Thanks for your insights!
Joa14
Joa14 OK, that's because you are referencing the entire column A. Excel then tries to return the if_true and if_false values for all 1 million+ rows in the sheet. Enter such a formula anywhere else than on the first row and you will indeed get #SPILL! without the dotted lines.
(By the way, why do you have "P3"? That will return the text P# when A does not contain 1. Remove the "" if you intend to return the value of cell P3.)
So move it to row 1 in the column where you have the formula now and change it to:
=IF($A:$A,$O1,P1)
You'll get a #VALUE! error for the first element and it will probably not give you the result you expect.
You need to limit the range from column $A:$A to, for example A$$2:$A$100. Can't tell if that will give the correct answers either. Up to you. You need to share the file (upload to Onedrive or similar and post the link here) if you can't upload it to the forum directly. And show what the expected results are.
