First off, I’m a total noob.
I’m trying to create a document that looks up (and displays) a shipping cost (from a table) based on values that the user enters. I would have thought that this would be fairly simple, but I haven’t been able to find anything that helps me get to that point.
I have a table, with shipping zones in columns, and weight in rows. I want to input, for example, 8 for the weight, and Europe for the zone, and have the doc look up from the table that the charge for 8kg to Europe is $50.
I suspect I should be able to use filter or lookup, but can’t get my head around how to implement it.
The green “Region” blob in the formula is a reference to the Region column in the shipments table (it has a dotted line around it like formula references do in Excel). The pink “Charge” blob means a column pulled “through” the reference from the charges table.
Thanks for your help, Nick!
That’s not quite what I’m looking for.
The shipping charge is dependant on both the region and the weight of the package. I see what you’ve done to lookup from one column, but what if I have 5 or 30 and I want a value from a specific column?
I was able to get close using the following formula:
where PkgWeight is a number input elsewhere in the document, but I also have an select box for the destination, which I would like the doc to use to look up the shipping price.
I have these select boxes:
and I’m hoping that by changing the selection in each of these boxes it will show me the correct price.
Right now, I’ve simplified things such that it doesn’t make a lot of sense to do all this work to get the formula running when I can just look it up “with my eyes”, but eventually I want to have more complex data and multiple lookups from different tables. I simplified things just to see if I could get it to work.
Duh, I see you have the charges in columns (and you did say that and I skimmed right over) - this isn’t good database design tbh because it leads to problems like the one you’re experiencing!
Thanks, Nick. Because I don’t know what I’m doing, I’m not sure how to implement what you are suggesting. Are you aware of a help document, or at minimum can you tell me what you’ve done here is called? It looks promising and I’d like to give it a try!
Are you referring to the table structure itself, the formula I used to pull the data into the shipments table, or the grouping I used to make the “flat” shipments table look like your original table with regions along the top?
Thanks for your support with the question of @Paul_Denhoed.
If you are OK with my suggestion, why not to share you sample doc with Paul to make it for him more easy to understand as your support looks great. (I am at my mobile device, traveling)
Relational table design can be a bit of a black art (look up relational normal forms for the dry facts!) but the essence of the change here is that each “new piece of data” (i.e. a regional charge) should be on a separate row in the table, not a column. It means you can view and manipulate the data a lot more easily because the column you’re interested in is the same regardless of which region you’re working with.
The formula is a straight lookup and I think should be pretty self-describing. There’s probably a nicer way to do this via properly linked tables (in my revised example above the two tables aren’t linked at all - I make the link myself in the formula).
The grouping is dead easy, actually.
Just click the group button and add the two groups as follows:
errr, stupid questions: You have the Charges table, which I believe is, in essence, the same as (or serving the same function as) my Table 1. But I’m not grokking what the Shipments table does!?
Also, what does the Charges table look like BEFORE the grouping is applied?
Hmmm, ok, I think I see what’s going on with the Charges table…
I’m still not sure about the Shipments table, but…
I’m going to try to create a new table with Region as the first column, with 10 rows for each region (total 40), then 1-10 for each region in the Weight column, and the corresponding prices in the Charge column.
Then, group as you have done in your example, and go from there.
This’ll take me some time, and I need to break for dinner now, and not sure when I’ll have a chance to get back to this, but I will report back here in a few days (or earlier?) with my progress.
THANKS very much for your assistance! I really appreciate it.
Here’s another version where I’ve added a grouped view of the Charges table. The key here is that both “Charges” and “View of Charges” are essentially the same thing. “Charges” is the underlying table that shows the raw data. “View of charges” is just a window onto “Charges” that has been grouped to look like your earlier example. Hope this helps.
All of the above, possibly, but mostly the latter!
