LinkBack URL
About LinkBacks
How do you build the formula to lookup the respective sheet name indirectly in a double lookup to meet the double match? Hard to type the problem out because it requires multiple sheets. I have my sample attached. Thanks to someone who can help?
How do you build the formula to lookup the respective sheet name indirectly in a double lookup to meet the double match? Hard to type the problem out because it requires multiple sheets. I have my sample attached. Thanks to someone who can help?
Hi, welcome to the forum
Whats wrong with what you have there? Looks to me like you did a pretty good job
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I have the right answer but I don't have the formulas referencing correctly to pull the data from sheet 115 and 126. The lookup match has to be specific to the worksheet. You can see the wrong formulas highlighted in yellow.
Hi
F3Formula:
Please Login or Register to view this content.
It right answer formula? But wrong number? That why you did get correct answer.
All the best
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Its the right resulting answer but the wrong formula. I want the arrays looking for Worksheet names "115" and "126". Not "110". What I've done is fine for 3 worksheets but I have 500 worksheets. I need to have my formula indirectly find the worksheet by having another worksheet name within cells in my summary worksheet.
=IF($A3="","",INDEX(INDIRECT("'"&$A3&"'!$A$11:$AO$20"),MATCH($C3&$D3,'110'!$A$11:$A$20&'110'!$E$11:$E$20,0),MATCH($B3,INDIRECT("'"&$A3&"'!$A$10:$AO$10"),0)))
I think here it goes wrong
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
That is correct. I need to change the '110'! reference with an indirect formula. How do you do that as a concatenated array?
Try to make make this change twice:
'110'!$A$11:$A$20
INDIRECT("'"&$A3&"'!$A$11:$AO$20")
I tried but to no avail. #value error. I take it that the substitution didn't work in my sample file?
Ok
It not easy with indirect with 4 lookup?
Only way I can think of is sheet 110,115 and 126 is add insert column E Then put incopy down. A11 ZOO and F11 Labor Hours like this Column E (ZOOLabor Hours) together. You can hide Column E?Formula:Please Login or Register to view this content.
Consolidation sheet F3copy down.Formula:Please Login or Register to view this content.
This should work with 500 worksheet.
No it fails because you haven't built the A column reference to match the Job column. you have the A referencing only the horizontal periods.
oh you mean concatenate C and D or Job and Element and then concatenate all the spreadsheets on A and E? I see that working but interested if keeping it as is without helper cells.
YesOriginally Posted by Erickson28
See the file
The attached file is great but unfortunately I've been provided files on a shared network which can not be altered. Therefore, a helper column can't be created. Hard to believe this can't be done without the use of a helper.
Try this, copied down - no helpers needed...
=INDEX(INDIRECT("'"&$A4&"'!$G$12:$R$18"),MATCH(C4&D4,INDEX(INDIRECT("'"&$A4&"'!$A$12:$A$18")&INDIRECT("'"&$A4&"'!$F$12:$F$18"),0),0),MATCH($B4,INDIRECT("'"&$A4&"'!$G$10:$R$10"),0))
Hi
I crack it! But FDibbins beat me lol.
on your first file below post #1 F3copy down.Formula:Please Login or Register to view this content.
Hi
I crack it? But FDibbins beat it lol.
Copy downFormula:Please Login or Register to view this content.
A few of the column references are off but I fixed the alignment and it worked out! It had to be possible but that double array concatenation was tricky. Here is the formula for use on the download using (micope21 formula- I didn't check Dibbins yet:
{=INDEX(INDIRECT("'"&$A9&"'!$a$10:$R$18"),MATCH(C9&D9,INDEX(INDIRECT("'"&$A9&"'!$A$10:$A$18")&INDIRECT("'"&$A9&"'!$e$10:$e$18"),0),0),MATCH($B9,INDIRECT("'"&$A9&"'!$a$10:$R$10"),0))}
Last edited by Erickson28; 07-30-2015 at 12:41 PM. Reason: wanted to reference Micope
Also no need for the array control shift enter. This is great
=INDEX(INDIRECT("'"&$A9&"'!$a$10:$R$18"),MATCH(C9&D9,INDEX(INDIRECT("'"&$A9&"'!$A$10:$A$18")&INDIRECT("'"&$A9&"'!$e$10:$e$18"),0),0),MATCH($B9,INDIRECT("'"&$A9&"'!$a$10:$R$10"),0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks