Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Look up of data in a different Sheet
I am trying to write a formula where I do a lookup of anything ("same cell ie
D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
#2
|
|||
|
|||
With all 50 sheet names on column B
type the following on cell C3 =INDIRECT("'"&B3&"'!d10") and copy it down column C You will need a VBA to get all sheet names in your courrent workbook on column B "Pester" wrote in message ... I am trying to write a formula where I do a lookup of anything ("same cell ie D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
#3
|
|||
|
|||
Try =INDIRECT(B2&"!D10")
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Pester" wrote in message ... I am trying to write a formula where I do a lookup of anything ("same cell ie D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
#4
|
|||
|
|||
Try INDIRECT
Put the cell ref in C1: D10 With B2:B51 containing the sheetnames, Put in C2: =INDIRECT("'"&$B2&"'!"&C$1) Copy C2 down to C51 If you want to extend the set up to cover other cell refs besides "D10" in C1, simply input the desired cell refs in D1, E1, F1 etc, select C2:C51 and fill across to F51, etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pester" wrote in message ... I am trying to write a formula where I do a lookup of anything ("same cell ie D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
#5
|
|||
|
|||
Max, Thanks for the help. Now, what if I want to look for information in a
Sheet or tab that is in a different Excel file, with a different path. Now, all the 50 sheet names will be in the other excell file. Do I need to put the path whole path from drive/folder etc in the cells? I try that and get a reference error "Max" wrote: Try INDIRECT Put the cell ref in C1: D10 With B2:B51 containing the sheetnames, Put in C2: =INDIRECT("'"&$B2&"'!"&C$1) Copy C2 down to C51 If you want to extend the set up to cover other cell refs besides "D10" in C1, simply input the desired cell refs in D1, E1, F1 etc, select C2:C51 and fill across to F51, etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pester" wrote in message ... I am trying to write a formula where I do a lookup of anything ("same cell ie D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
#6
|
|||
|
|||
I try that and get a reference error
Think INDIRECT requires that the source workbooks need to be open -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pester" wrote in message ... Max, Thanks for the help. Now, what if I want to look for information in a Sheet or tab that is in a different Excel file, with a different path. Now, all the 50 sheet names will be in the other excell file. Do I need to put the path whole path from drive/folder etc in the cells? I try that and get a reference error |
#7
|
|||
|
|||
Now,
all the 50 sheet names will be in the other excell file. Do I need to put the path whole path from drive/folder etc in the cells? I try that and get a reference error "N Harkawat" wrote: With all 50 sheet names on column B type the following on cell C3 =INDIRECT("'"&B3&"'!d10") and copy it down column C You will need a VBA to get all sheet names in your courrent workbook on column B "Pester" wrote in message ... I am trying to write a formula where I do a lookup of anything ("same cell ie D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
#8
|
|||
|
|||
Bernard,
All the 50 sheet names will be in the other excell file. Do I need to put the path whole path from drive/folder etc in the cells? I try that and get a reference error "Bernard Liengme" wrote: Try =INDIRECT(B2&"!D10") best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Pester" wrote in message ... I am trying to write a formula where I do a lookup of anything ("same cell ie D10) in every sheet. I want to have the NAME of the 50 sheets in column B, from B2 to B51. And in column C, create a formula for each respective cell where I look for the value of D10 from that sheet where I do the lookup. So create one formula in C2, and drag down to C51. So if in Cell B3 I have the name January, and in B4 February.....in cell C3, create formula that looks the name of the sheet written in cell B3, and then goes a looks for the value of ie cell D10 into the sheet called January. And in the Cell C4 the formula looks for the value of cell D10 in the sheet name from cel B4, so goes to February. etc etc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merger Two Data Sheet | Excel Discussion (Misc queries) | |||
Excel and propogating data | Excel Worksheet Functions | |||
refresh external data on a protected sheet | Excel Discussion (Misc queries) | |||
Conditional format sheet data | Excel Worksheet Functions | |||
Automatioc spill over data to a new Excel sheet from Xml source? | Excel Discussion (Misc queries) |