Forum Discussion
Help: Formula multiple sheets and columns
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.
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.1
1 3 6.3 8.52 -0.52
1
1
1
1
Thanks 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.
Riny_van_Eekelen thank you so much! I tried something else with your idea and it worked! Thanks a lot!
Joa14