Strange title, but an equally strange problem.

I have a named range, Master_Fees, that is set out as follows:

Quantity code: 6 characters max. It may be less than 6, but not less than 5. Formatted as 'General';
Fee: Numeric, formatted to 2 decimal places;
Formatted fee: This if formatted into 6 digits, with leading '0' characters as appropriate. For example, if Fee was 6.95, this field would hold "000695". This is presently calculated as =(TEXT(D3*100,"000000"))

I have another column, outwith the named range above, that contains the following:

Treatment 1...Treatment 10:
Each of the cells under these headings will contain the following-
A five or six character code, that is one of the 'Quantity Codes' listed in the above range;
Two characters, as a combination of 'Y' & 'N', which are always present;
A maximum of 32, 2 digit numbers;

So what do I want to do? For each entry under 'Treatment', I want to lookup the value that represents the first 6 characters / digits of the 'Treatment', in the named range, and return the fee and / or the formatted fee. I then want to total up each of the Fee values that have been returned, format it as the formatted fee value, and store it in a cell.

Simple? I'm banging my head here, as I can't get it. I have, as an example:

Named range:
102101 39.30 003930
102102 39.30 003930
102103 39.30 003930
140109 77.85 007785
140110 86.50 008650
140111 95.15 009515

Treatment 1: 102101NN253685
Treatment 2: 140111YN1236524586842341424641
Treatment 3: 102103NN286512

In this case, the result would be:
Treatment 1: Fee=39.30 Formatted Fee=003930
Treatment 2: Fee=95.15 Formatted Fee=009515
Treatment 3: Fee=39.30 Formatted Fee=003930
Total charge: Fee=173.75 Formatted Fee=017375

How do I do it? Can anyone help?

Duncan

Hi Duncan,

Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb :

Named range:
102101 39.30 003930
102102 39.30 003930
102103 39.30 003930
140109 77.85 007785
140110 86.50 008650
140111 95.15 009515

Treatment 1: 102101NN253685
Treatment 2: 140111YN1236524586842341424641
Treatment 3: 102103NN286512

the string for Treatment 1 is in J3
Then for Fees:
=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,2,0)
and for formatted fees:
=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,3,0)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Wednesday, December 12, 2012 4:23:32 PM UTC, Claus Busch wrote:
Hi Duncan, Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb : Named range: 102101 39.30 003930 102102 39.30 003930 102103 39.30 003930 140109 77.85 007785 140110 86.50 008650 140111 95.15 009515 Treatment 1: 102101NN253685 Treatment 2: 140111YN1236524586842341424641 Treatment 3: 102103NN286512 the string for Treatment 1 is in J3 Then for Fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,2,0) and for formatted fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,3,0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

On Wednesday, December 12, 2012 4:23:32 PM UTC, Claus Busch wrote:
Hi Duncan, Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb : Named range: 102101 39.30 003930 102102 39.30 003930 102103 39.30 003930 140109 77.85 007785 140110 86.50 008650 140111 95.15 009515 Treatment 1: 102101NN253685 Treatment 2: 140111YN1236524586842341424641 Treatment 3: 102103NN286512 the string for Treatment 1 is in J3 Then for Fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,2,0) and for formatted fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,3,0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Claus, many thanks for the speedy reply. I don't mean to be a pain, but could you assist in explaining what you just did there!

=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),MASTE R_FEES,2,0)
The 'LOOKUP' itself, LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))). If I'm reading it right, it is looking up the value '9^9' in the lookup vector '1*LEFT($J3,COLUMN(1:1))'. What does the '9^9' signify? To me it looks like 9*9*9*9*9*9*9*9*9, but it can't be. This value doesn't exist in the spreadsheet! Where / what are you getting it from?

The Lookup Vector of '1*LEFT($J3,COLUMN(1:1))', to me reads as: get the leftmost 'COLUMN(1:1)' characters from the value in the cell $J3. When I evaluate 'COLUMN(1:1)', it always returns '1'. So, it now says, "return the 1*1 characters from the left of the contents of cell $J3."

From my reading, we now have:

VLOOKUP(LOOKUP(387420489,1*1),MASTER_FEES,2,0)

I don't understand what the formula is trying to do? I could just say thanks for that and move on, however I'd really like to know what it's doing.

Duncan

Claus, many thanks for the speedy reply. I don't mean to be a pain, but could you assist in explaining what you just did there!

=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),MASTE R_FEES,2,0)
The 'LOOKUP' itself, LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))). If I'm reading it right, it is looking up the value '9^9' in the lookup vector '1*LEFT($J3,COLUMN(1:1))'. What does the '9^9' signify? To me it looks like 9*9*9*9*9*9*9*9*9, but it can't be. This value doesn't exist in the spreadsheet! Where / what are you getting it from?

The Lookup Vector of '1*LEFT($J3,COLUMN(1:1))', to me reads as: get the leftmost 'COLUMN(1:1)' characters from the value in the cell $J3. When I evaluate 'COLUMN(1:1)', it always returns '1'. So, it now says, "return the 1*1 characters from the left of the contents of cell $J3."

From my reading, we now have:

VLOOKUP(LOOKUP(387420489,1*1),MASTER_FEES,2,0)

I don't understand what the formula is trying to do? I could just say thanks for that and move on, however I'd really like to know what it's doing.

Duncan

Hi Duncan,

Am Wed, 12 Dec 2012 09:20:52 -0800 (PST) schrieb
:

=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),MASTE R_FEES,2,0)
The 'LOOKUP' itself, LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))). If I'm reading it right, it is looking up the value '9^9' in the lookup vector '1*LEFT($J3,COLUMN(1:1))'. What does the '9^9' signify? To me it looks like 9*9*9*9*9*9*9*9*9, but it can't be. This value doesn't exist in the spreadsheet! Where / what are you getting it from?

The Lookup Vector of '1*LEFT($J3,COLUMN(1:1))', to me reads as: get the leftmost 'COLUMN(1:1)' characters from the value in the cell $J3. When I evaluate 'COLUMN(1:1)', it always returns '1'. So, it now says, "return the 1*1 characters from the left of the contents of cell $J3."

column(1:1) gives you 1, 2, 3, ...16384 (1 to columns.count step 1),
that gives you for LEFT($J3,COLUMN(1:1))) e.g 1, 10, 102, 1021, 10210,
102101, 102101N....
and then for 1*LEFT($J3,COLUMN(1:1))) 1, 10, 102, 1021,10210, 102101,
#Value, #Value,... and LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))) then shows
you the last result 102101.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,

Not sure I fully understand it, yet, but I'll keep looking at it!

Cheers once again

Duncan

Claus,

I've been having a look at it and expanding on it.

What I have is the following:

Columns
A B C D E F
Code1 Value Code2 Value Code3 Value
010101NN 140132NN 147006NN

In the value column, I'm looking to put your VLOOKUP solution however, I'm struggling with it. When I paste the solution into the various columns / rows, the "COLUMN(1:1)" function changes and goes awry. In Cell B2, it has the value "LOOKUP(1:1)" but in B3, it changes to "LOOKUP(2:2)", in B4, it becomes "LOCATION(3:3)" and so on. In Cell D2, it has the value "LOOKUP(1:1)" but again increments by one on each row.

The result of this is that it is giving me an error, namely the location function showing as "LOCATION(#REF!)" or the cell showing the value "#N/A".

I don't know why, as it works in some cells but not in others, which is making it more and more frustrating. Any help appreciated.

Duncan

Hi Duncan,

Am Thu, 13 Dec 2012 08:07:47 -0800 (PST) schrieb :

A B C D E F
Code1 Value Code2 Value Code3 Value
010101NN 140132NN 147006NN

to get the number from A2, format the cell "000000" and try:
=LOOKUP(9^9,1*LEFT(A2,COLUMN(1:1)))
or without formatting you have to use:
=TEXT(LOOKUP(9^9,1*LEFT(A2,COLUMN(1:1))),"000000")
You can put your VLOOKUP aroundto find the value.
It doesn't mind if COLUMN(1:1) changes to COLUMN(2:2)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2