[SOLVED]Formula within a single cell rather than whole column

Hey, i am looking to transfer a pokemon spreadsheet I made in excel to coda as i feel it will be much more versatile for viewing. However many cells in my spreadsheet have formulas that reference a different cell in the same column. How do you do that in Coda?

As an example:
column name: # of candies
column values: 10, 12, 12, 25, 28, 28, 28
I want the second 12 and second and third 28’s to derive their value from the previous entries of the same value. in excel i do it currently with this formula: =VLOOKUP(“Caterpie”,$B$1:$E$153,4,FALSE) where the only data I change when necessary is the name between the “”

Thanks!

is there no answer to this?

Hi Marlon, Coda does not have cell level formulas but can you take a look at this Adding values from previous rows and see if that helps your scenario.

Thanks for the response but I specifically need a cell level formula to define the candies values…otherwise there is a lot of extra work for nothing. I think I can see a workaround with what is suggested in the thread you posted but it is even more work than manual entry.

Mind sharing a bit more of the Excel example? Might be some other ways to model this. In most cases, VLOOKUP can be mapped to a Coda formula - and generally results in the added benefit of being resilient to changing layouts, sorts, grouping, views, etc.

The formula i am using: =VLOOKUP(“Caterpie”,$B$1:$E$153,4,FALSE)
Explanation and table:

The VLOOKUP formula here is checking the 2nd column for the name "caterpie" for the entries of metapod and butterfree and then, if it finds that name, it copies the # of candies into the 5th column. I simply copy the formula for each new pokemon entry changing the name in quotations to the appropriate one. It has worked really well so far for building a good tracker database. And this way every time i catch a new pokemon from anywhere in the evolutionary line i only ever need to update the # of candies in the base pokemon of the species. (imagine having to update the eeveelutions every time i catch an eevee or something)
Pokemon number Pokemon Name # of Pokemon cost to evolve # of candies
10 Caterpie 2 12 28
11 Metapod 1 50 28
12 Butterfree 1 - 28

I basically need a formula that says “look for Pokemon Name.Caterpie, copy # of candies.Caterpie to # of candies.Metapod” but that formula would need to be cell specific because i enter the base pokemon it is supposed to identify the first time i add the entry.

Hey @Marlon_Needelman,

if I get your need right, you might have to add another column with “base candies”. That way, you can fill in the the candies of the base pokemon and with a lookup, you can print out this number in the “# of candies” for all releated evolution states.

Also you might need to add a “base pokemon” column. That way your formula can reference to it.

With these to extra columns, you don’t need a formula for every single cell. One formula fits for the whole column.

You can hide the extra columns, if they annoy you.

Here is an example:

Formula:

=Pokemon.lookup(Name,thisRow.[Base Name]).[Base Candies]

Does that fit your need?

Ok first, thank you daniel!
I tried it out and it worked at first, but then i copied in my updated excel table and filled it out, finally adding in the formula at the end but now my whole column of # of candies is empty:

It should have entered 34 into the cell where the mouse is and the 2 cells below it, but the entire column is blank.

here is a photo of the formula working in the same document but on another view:

the formula: Test.Lookup(Name,thisRow.[Base Name]).Source

tried copying the formula from one table to the other and adjusting accordingly. Now i get this:

the formula being used: [Pokemon Inventory].Lookup(Name,thisRow.[Base Name]).[Base Candies]

just tried rewriting the formula from scratch and it worked!

1 Like