how do you reference a single cell in an external sheet...dynamically? formulas? Cell Linking?
Thank you for your time.
I cant find the answer in the documentation or forum. So I am asking.
我如何referencea single cellin anexternal sheet?
b. Can I reference a cell by row number and column number?
c. Is there a way to make this dynamic?
using the ' =ExampleCell1 ' syntax is for the current sheet. How would I do this by for an external sheet?
Example:
- web-form creates a new row in Master sheet
- in newly created row, (column #2, row #5) then pulls text from specific cell in external sheet at (column #5, row #5)
- this would repeat for each new row that was created, where the row # was updated dynamically
-VLOOKUP looks for a range and a search criteria. I am not sure how to use it in this scenario as I don't have a search criteria and the range would always be changing.
-Workflow Automation requires I copy an entire row. which wont work for me.
-Cell linking doesn't seem to be able to automatically populate on newly added rows.
Thank you again.
Answers
-
Paul Newcome ✭✭✭✭✭✭Can you provide a mocked up screenshot that shows exactly what you are trying to accomplish with the data manually entered?
-
I dont have a mockup just yet but will try to do so soon. . . this is what support has given me:
Thanks for contacting Smartsheet Support. It sounds like you have a few questions on Smartsheet Functionality. I'm happy to assist.
我如何reference a single cell in an external sheet?
Cell Linking does exactly this,I recommend reviewing our Help Article: for details and instructions on this topic.
b. Can I reference a cell by row number and column number?
At this time, you can not reference a Cell by Row/Column Number. I will create an enhancement request on your behalf for our product team to consider this for development. We appreciate your input!
c. Is there a way to make this dynamic?
Dynamic Cell Links also do not currently exist within Smartsheet, I will create an additional enhancement request for this feature.
You mentioned VLOOKUP as being a possible solution, which it is as this would make a "dynamic cell link" but you will need to have some sort of "key column" to associate the two Sheets.
I recommend reaching out to your Customer Success Representative to assist you with coming up with a possible solution for this issue.
For general information on Smartsheet formulas and a listing of all available functions, please see
https://help.smartsheet.com/functions
For additional samples of formulas in action, we recommend using the Smartsheet Formula Examples template in the Template Gallery, which you can find by clicking on the "+" tab.
Please note that Smartsheet support can provide general assistance on formulas, but currently doesn't offer formula creation/custom programming services. When available, we will gladly pass on samples we have which may be similar to what you are trying to accomplish.
Let me know if you have any other technical questions on the above and I'll be happy to help. For new or unrelated support questions, please submit a new ticket through our Web Form at
https://help.smartsheet.com/contactto ensure that the next available technical support specialist can assist. This way you'll receive the quickest possible support from our team.
-
Paul Newcome ✭✭✭✭✭✭
Yeah. Support can't provide very detailed help when it comes to solutions like this. They are more of a "technical" type of support.
Even if it is just a couple of rows with fake data manually entered (and possibly arrows pointing if possible), screenshots would help immensely. There are a lot of different ways to pull data from one sheet to another, but the best way really does depend on the setup of the source data, the setup of the target sheet, and the desired outcome.
-
answer to A:
i started off by typing in
=VLOOKUP(
which brought up a link to "Reference Another Sheet". Click that, which opens up a form, where you can select the sheet and cell you want to refer to. once you've done that, just click "Insert Reference" to return to the original sheet. then you can edit the resulting formula to remove the VLOOKUP part. eg.
=VLOOKUP({Product Plan - Line Extension Allowance Up Range 1})
becomes
={Product Plan - Line Extension Allowance Up Range 1}
It's probably possible to type all that out, but the form is easier for me
-
Paul Newcome ✭✭✭✭✭✭
@Kai LübbeThat will work, but you can also use cell linking which is a little bit easier/less complex.
Categories
You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.<\/p>
=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)<\/p>"},{"commentID":387901,"body":"
Lets try a different approach.<\/p>
=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH(Start Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/66407/\") + MONTH([Start Date]@row))<= 202307<\/strong>, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/66407/\") + MONTH([End Date]@row))>= 202307<\/strong>), [$ per month]@row)<\/p> Basically we are creating a yyyymm stamp from the start and end dates and comparing them to the yyyymm stamp for that year\/month combo. The above is for July 2023 (202307).<\/p>"},{"commentID":387906,"body":"
<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/G1SDKXPQ7I1D\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/GCQ7U05Q4GNO\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/79GD9D2EWXCW\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
