Refer to a cell in a table imported fromExcel (1 Viewer)

Bobp3114

Member
Local time
Today, 22:35
Joined
Nov 11, 2020
Messages
42
Using a single rate per row...
Hi Mark
I have used your code successfully...What would need to change if the field eg KG was a text field instead of a numerical field?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:35
Joined
Feb 19, 2013
Messages
16,553
if it is a text field the the sorting order changes which matters when using <= or >=. As numbers the order would be

1
2
11
12

as text the orders would be

1
11
12
2

to fix you would need to restate your values as

01
02
11
12

and if your largest number was 3 digits

001
002
011
012
 

MarkK

bit cruncher
Local time
Today, 04:35
Joined
Mar 17, 2004
Messages
8,178
Hey Bob,
To implement the solution I proposed you need to do a numeric sort on the Weight field, so making it a string doesn't make sense. You can see from what CJ is saying that a numeric sort on a string adds cost without payoff.
 

Bobp3114

Member
Local time
Today, 22:35
Joined
Nov 11, 2020
Messages
42
Hey Bob,
To implement the solution I proposed you need to do a numeric sort on the Weight field, so making it a string doesn't make sense. You can see from what CJ is saying that a numeric sort on a string adds cost without payoff.
Hi Mark
Maybe I did not explain clearly...your solution works fine for that table....I have another table...that requires a similar solution
tblESSRates tblESSRates
I was trying to change the KG field of the previous example to suit the OriginCountry field here...any thoughts would be appreciated
Bob

EntryIDOriginCountryDestHongKongChinaDestAustraliaNewZealandDestRestOfAsiaDestEuropeDestAmericasDestRestOfWorld
1​
China
0.90​
1.62​
0.90​
0.90​
0.90​
0.90​
2​
Hong Kong
0.90​
1.65​
0.90​
0.90​
0.90​
0.90​
3​
Rest Of Asia
0.00​
0.00​
0.00​
0.00​
0.00​
0.00​
4​
Europe
0.20​
0.20​
0.20​
0.20​
0.20​
0.20​
5​
Americas
0.20​
0.20​
0.20​
0.20​
0.20​
0.20​
6​
Rest Of World
0.20​
0.20​
0.20​
0.20​
0.20​
0.20​
 

MarkK

bit cruncher
Local time
Today, 04:35
Joined
Mar 17, 2004
Messages
8,178
Bob, a couple of problems,
1) This data is not normalized. Remember how @MajP fixed your previous spreadsheet? That job needs to be done for this spreadsheet.
2) This is not a series where price varies with weight. Here you have discrete pairs, each with a fixed price.
a) To get a result here you will have to supply different parameters (origin, not weight), and
b) the logic that constitutes a match will differ (weight will not fall in a range, origin will require an exact match)
Hope this helps,
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:35
Joined
May 21, 2018
Messages
8,463
OriginDestinationRate
ChinaHong Kong / China0.90
ChinaAustralia /New Zealand1.62
ChinaRest of Asia0.90
ChinaEurope0.90
ChinaAmericas0.90
ChinaRest of World0.90
Hong KongHong Kong / China0.90
Hong KongAustralia /New Zealand1.65
Hong KongRest of Asia0.90
Hong KongEurope0.90
Hong KongAmericas0.90
Hong KongRest of World0.90
Rest Of AsiaHong Kong / China0.00
Rest Of AsiaAustralia /New Zealand0.00
Rest Of AsiaRest of Asia0.00
Rest Of AsiaEurope0.00
Rest Of AsiaAmericas0.00
Rest Of AsiaRest of World0.00
EuropeHong Kong / China0.20
EuropeAustralia /New Zealand0.20
EuropeRest of Asia0.20
EuropeEurope0.20
EuropeAmericas0.20
EuropeRest of World0.20
AmericasHong Kong / China0.20
AmericasAustralia /New Zealand0.20
AmericasRest of Asia0.20
AmericasEurope0.20
AmericasAmericas0.20
AmericasRest of World0.20
Rest Of WorldHong Kong / China0.20
Rest Of WorldAustralia /New Zealand0.20
Rest Of WorldRest of Asia0.20
Rest Of WorldEurope0.20
Rest Of WorldAmericas0.20
Rest Of WorldRest of World0

Code:
Public Function GetShippingRate(Origin as string, Destination as string) as currency
  GetShippingRate = dlookup("Rate","NormalizedTable", "Origin = '" & Origin & "' AND Destination = '" & destination & "'")
end function
 
Last edited:

Users who are viewing this thread

Top Bottom