YTL Excel # 126 part 2: VLOOKUP & INDIRECT & ROW

Look up each item's price in the product prices table ( lower left hand corner) and display the price in column C of the invoices table ( use a vlookup) The extended cost is the quanitity times the price. The shipping cost is computed by in column F by looking up the shipping rate and multiplying it by the extended cost ( column E). Column G is the sum of the extended cost and shipping cost.
*Shipping rate in lower right hand corner goes from ( 32 to 35, including headers)
* Column starts from a to G
* Rows go from 22 to 28 (including headers)
* Prices product table starts from 32 to 36 (including headers)

So far I have:

Customer Item Price Quantity Extended Shipping Total 90273 toothbrush 1 90310 music player 1 90291 radio 2 90312 toothbrush 3 90303 radio 1 90389 toaster 3 Item Price Extended$
Rate music player $91.45 0 1% radio $65.00 10 5% toaster $27.00 100 10% toothbrush $12.50

50 named dynamic ranges; ECWP_COLUMN_1, ECWP_COLUMN_2,.......ECWP_COLUMN_50 - are used when referencing my target in the example SUMPRODUCT calculation below:

{=SUMPRODUCT(--(ECWP!$J$6:$J$608=$F215),--(ECWP!$N$6:$N$608=$F220),( ECWP_COLUMN_1 ))}
-entered as an array

Each of the columns has a numeric header; 1,2,3....50, which relates directly to it's named range. eg - ECWP_COLUMN_1 carries header "1", ECWP_COLUMN_33 carries header "33", etc.

I'd like to incorporate INDIRECT into the above formula so that I can drag my formula across columns, negating the need to physically amend each array (50 columns) but I'm not even sure it's possible. It would make the task of building the tables I need much less time consuming.

I've got a workbook with tables on multiple worksheets. The worksheets are called Summary, Q1, Q2, Q3, and Q4. Each of the worksheets have tables:


Worksheet

Table

Summary

SummaryTable

Q1

Q1Table

Q2

Q2Table

Q3

Q3Table

Q4

Q4Table



I have a column (LookupColumn) in Summary:SummaryTable that I'd like to refer to the corresponding table on one of the worksheets based on lookup data in that row in SummaryTable (e.g., an event that occurs in Q1, Q2, Q3, or Q4).

I've tried INDIRECT and concatenation within the Vlookup formula but can't get it to work. And there isn't much info on formulas and tables out there so I'm at a loss. I'm also wondering given the issues Vlookup has with tables that cross worksheet INDIRECTs may not work.

Any thoughts / comments / suggestions would be appreciated.

Kind regards.
IntlTaxMan

Dear all,

Your help would be much appreciated.

How can I refer in a formula to a sheet by using the first three letters of a cell. I have two files and I need to find the sheet in one file by the reference of the product code (THA005). I understand this is possible with INDIRECT and INDIRECT.EXT which requires the morefun.xll add-in.

The value of cell A13 is THA005. The sheet name in the other file is called THA. The formulas I have to integrate this in are underneath. Any ideas how to integrate this into the formulas?

(original formula)
=MATCH(A13, [TFWebsite.xls]Tha!$A$5:$A$5000,0)
(original formula)
=IF(ISNUMBER(C13),INDEX([TFWebsite.xls]Tha!$C$5:$C$5000,C13),0)

(my own attempt, doesn't work)
=MATCH(A14, [TFWebsite.xls] indirect(A14&!$A$5:$A$5000,0))

My own attempt doesn't work, probably because of the fact that the cell value is THA005 while to sheet is called Tha. In addition most likely a problem because the sheet is in another file. ?????

Your e-mail on this is welcome veenkris@yahoo.co.uk

Many thanks,

Kris

So I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.

This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.

I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:

Code:

'H:\%location%\[file.xls]Main Template'!$A:$CG

And just to set it up I have this vlookup code:
Code:

=VLOOKUP(B2,(INDIRECT("$O$2")),INDIRECT("O4",TRUE),FALSE)
O2 - Value of "DIR'!$A$1"
O4 - Value of "18"

I did a windows search to find one of the items and changed the value of O2 and I got #N/A as the result.

Please direct me properly so I can get this taken care of.

Thank you in advance for your help.

-Nic

Hi, I'm trying to create a VLOOKUP using INDIRECT to point the vlookup to the target file, but I'm having trouble with the syntax in the cell I'm referencing with the INDIRECT. - At least I think that's the problem!

The VLOOKUP I'm using is: Code:

=VLOOKUP(Contacts!B3,INDIRECT(D8),2,0)

, and the data in cell D8 is Code:

'T:\NTGI Middle Office\Client Reporting\ADIC\Monthly Reports\ADIC1\September\[ADIC1_Perf.xls]USD Historical'!$A:$E

Currently I'm getting #REF! as a result of the above.

Is anybody able to help?
Many thanks,

I am not familier at all with R1C1 notation. This is the segment out of a larger formula that Harlan Grove wrote the actual formula is below and it works great!

=SUM(MMULT(--((A1:F1)=TRANSPOSE(A2:C2)),(COLUMN(INDIRECT("rc1:rc"&COLUMNS(A1:F1),0))=ROW(INDIRECT("1:"&COLUMNS(A1:F1))))*10^(COLUMNS(A1:F1)-ROW(INDIRECT("1:"&COLUMNS(A1:F1))))))


What I am trying to figure out the is the "rc1:rc" notation. Is ther another way to write this part of the formula without using R1C1 notation?

=COLUMN(INDIRECT("rc1:rc"&COLUMNS(A1:F1),0))

Thanks in advance Stephen

Any anyone suggest me some good sources where i can find some tutorial about this indirect-vlookup function. Below is what i want to crack

=INDIRECT(VLOOKUP(MATCH($Q$1,O175:AM175,1),Refrence!$C$1:$D$65,2,FALSE)&10)

Hi,

I am sure this has been discussed but I am having trouble
understanding what is needed.
I think I need to use a indirect call within vlookup.

Scenario
multiple workbooks with same name except a with a differing date.
ie file 'foo 01.12.03.xls'
worksheet in each file foo called 'auth zoo'
I have a summary workbook with a column with each date: E2 = 01.12.03
also in summary, A1 = 'foo ' and B1 = auth zoo'!$C$11:$I$81 and C1 =
..xls
my lookup value is in A3
what I would like to do, but I am not having too much success is:
vlookup(A3,indirect(A1&E2&C1&B1),7,0)

I would greatly appreciate it if someone could explain the error of my
ways. Thanks.

Regards,
Joe

I'm trying to get my formula in Y3 to search another sheet (Week1) and within the range u2:v14500 find the value in x3 and return the value 1 column over. The formula I'm trying to use is below and failing muiserably. Can anyone help me figure out what is wrong here....

U V W X Y 1 Week1 2 This Week Last Week 3 297788 1 297788 4 274840 2 274840
Spreadsheet Formulas Cell Formula V3 =IF(C3="","",IF (ISNA (VLOOKUP ($U3,$C$3:$H$7021,6,FALSE) ) ,"",VLOOKUP ($U3,$C$3:$H$7021,6,FALSE) ) ) X3 =IF(INDIRECT ($X$1&"!U"&ROW () ) >0,INDIRECT ($X$1&"!U"&ROW () ) ,"") Y3 =IF(COUNTIF (INDIRECT (X1&"!"&"$u$2:$v$14500") ,X1) , VLOOKUP (X3,INDIRECT (X1&"!"&"$u$2:$v$14500") ,2,0) ,"") V4 =IF(C4="","",IF (ISNA (VLOOKUP ($U4,$C$3:$H$7021,6,FALSE) ) ,"",VLOOKUP ($U4,$C$3:$H$7021,6,FALSE) ) ) X4 =IF(INDIRECT ($X$1&"!U"&ROW () ) >0,INDIRECT ($X$1&"!U"&ROW () ) ,"") Y4 =IF(AI5="","",VLOOKUP (X4,$AI$4:$AJ$5938,2,FALSE) )