YTL Excel #126: VLOOKUP & INDIRECT & COLUMN functions

Hey guys,

I have the following formula on sheet1 -

'=INDEX(INDIRECT(VLOOKUP(D9,INDIRECT(CONCATENATE("solver_",D10)),2,FALSE)),1,VLOOKUP(D11,solver_columncounter,2,FALSE))

D9, D10 and D11 are all user selected values (drop downs)

The line works perfectly - based on the selections, returns the value of a specific row/column in the named range.

Can someone help me translate this to VBA so I can have this run on the backend? I will be setting the returned value to a variable and using it in solver. I'm trying to avoid having to reference the cell on the page for the variable value.

Thanks for the help!!

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

There are really two questions here 1) I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.

2) I am working with this formula
=C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

and it comes from this thread

http://www.excelforum.com/excel-gene...orksheets.html

I have included a worksheet attachment that has explanations

Thank You

Hello,

I have a spreadsheet that I'm using offset/indirect functions with, and it works great until we get to the $ and % issue.

Basically, I need the function to return the numerical data WITH the symbol that's attached to it ($/%) ... Is this possible? I've tried every type of formatting, but nothing seems to work.

Should I be using a different function?

For instance,

My Reference cell says: $15
I'm using: =OFFSET(INDIRECT(Q5),0,11,1,1)
My Indirect referencing just presents: 15

I can't use formatting to fix this problem, since my reference cell is going to be a percentage 50% of the time.

Any help would be much appreciated!

Thank you!

I have created a spread sheet and would like to create data validation. As the following:
AC AB AC
Speaker-1 SP-1 1

I want the AC column to equal 1 in the E column. In colum E I have a drop down list. I want SP-1 to show in E-4 but I would like it to have a value of 1 so I can total the column. I tried using =indirect"AB"="1".

Hope this was clear.

Hi guys,

Does anyone know how to use the INDIRECT + OR functions together with defined arrays?

For some background, I have nine columns of data that are defined as the following arrays:

Date
Count
ProductName
RepairItem1
RepairItem2
RepairItem3
RepairItem4
RepairItem5
RepairItem6

For a given Date and ProductName, I am trying to sum up Count for all instances where RepairItem1 = X, where RepairItem2 = X, where RepairItem3 = X, where RepairItem4 = X, where RepairItem5 = X, and where RepairItem6 = X.


Formula that works
=SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(INDIRECT(RepairItem1)=$C$1,INDIRECT(Count),0),0),0)) + SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(INDIRECT(RepairItem2)=$C$1,INDIRECT(Count),0),0),0)) + SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(INDIRECT(RepairItem3)=$C$1,INDIRECT(Count),0),0),0)) + SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(INDIRECT(RepairItem4)=$C$1,INDIRECT(Count),0),0),0)) + SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(INDIRECT(RepairItem5)=$C$1,INDIRECT(Count),0),0),0)) + SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(INDIRECT(RepairItem6)=$C$1,INDIRECT(Count),0),0),0))


Formula that doesn't work
=SUM(IF(INDIRECT(Date)=$A$1,IF(INDIRECT(ProductName)=$B$1,IF(OR(INDIRECT(RepairItem1)=$C$1,INDIRECT(RepairItem2)=$C$1,INDIRECT(RepairItem3)=$C$1,INDIRECT(RepairItem4)=$C$1,INDIRECT(Re pairItem5)=$C$1,INDIRECT(RepairItem6)=$C$1),INDIRECT(Count),0),0),0))


This doesn't give me the right result, and seems to basically ignore the "RepairItem_" condition completely while summing up Count only for the specified Date and ProductName.

What am I doing wrong here?

Thank you

Can someone please explain what exactly the indirect and lookup function do in the formula below?

=MID(A1,LOOKUP(2,1/(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57),ROW(INDIRECT("1:"&LEN(A1)))),1)

A1: ADE1001
A2: ABFR20H1
A3: DRT101C

This fomula will extract first alphabet (Not a Number) from the right of the string. The results is E, H and C respectively.

I found a post where a user described a spreadsheet having a cell (AP3) specifying a start date, another cell (AQ3) specifying an end date, and a formula that rendered the number of Leap Days (2/29) within that date range.

If you're curious, see original post at:
http://www.mrexcel.com/forum/showthread.php?t=506796

That formula was:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(AP3&":"&AQ3)),"dd-mm")="29-02"))

This is a good trick, but I don't fully understand it. Aladin explained what it does, but I can't make sense of the syntax and relationship of the ROW and INDIRECT pieces.

For this part:
ROW(INDIRECT(AP3&":"&AQ3))
The contents of AP3 and AQ3 are serial date values, right? For the ROW function to work (in this case, as an array function, listing all the rows between two cell addresses), doesn't that INDIRECT have to yield a A1 style values (that is, have a lettered column component)?

Can someone explain?

Thanks!

I'll do my best to explain the problem I am encountering. Please bear with me.

The function is a combination of Indirect and LOOKUP functions.It reads,
= "A" & LOOKUP(Y12,INDIRECT(CF12),INDIRECT("B"&MID(CF12,2,LEN(CF12) - 2) & "B")) & "A"

The result should be A8A, which further refers to a named range.
The same function works in the previous rows with the corresponding cell numbers of course.

Any thoughts on what might be the problem?
I'd appreciate it.

VN

Hi all,

Can someone help with this formula,

Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))

Any help would be appreciated


VBA Noob