**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to create a formula that searches for the sheet named in A2 (lookup tab) and then the component on the sheet in B2 (lookup tab)and then updates the cell in column I with whats the value in H2 (lookup tab) of the formula cell.

Solved by C. S. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
22/08/2018 - 03:08

Hi

User
22/08/2018 - 03:08

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 03:08

how ar eyou doing

Excelchat Expert
22/08/2018 - 03:09

downloading the file

User
22/08/2018 - 03:09

Thanks!

User
22/08/2018 - 03:09

how are you. im doing good. its still early

Excelchat Expert
22/08/2018 - 03:10

I'm doing good.. Early start of the day is always good :)

Excelchat Expert
22/08/2018 - 03:10

just bare with me trying to download the file

User
22/08/2018 - 03:10

not a problem

Excelchat Expert
22/08/2018 - 03:11

can you explain what your trying to do

User
22/08/2018 - 03:12

If you select the "lookup tab"

Excelchat Expert
22/08/2018 - 03:12

yes

User
22/08/2018 - 03:12

I want to create a formula in H2 that can be copied all the way down the column

Excelchat Expert
22/08/2018 - 03:13

ok

User
22/08/2018 - 03:13

that formula will look for the sheet number (A2), and component number on the sheet (B2)

Excelchat Expert
22/08/2018 - 03:13

ok got it

User
22/08/2018 - 03:13

and then update cell in column I of the found component with a new price thats from column H in the lookup tab

User
22/08/2018 - 03:14

thats IF that can be done... any suggestion or alternative is welcome

Excelchat Expert
22/08/2018 - 03:14

so for example H2 =2.60

Excelchat Expert
22/08/2018 - 03:14

oh it can be done :) give me few min to work on the formula.. just veiry if h2 = 2.60 is right

User
22/08/2018 - 03:15

I dont want to pull the info from the found cell.. i want to instead update that cell with whats in H2.

User
22/08/2018 - 03:15

so if i enter $100 in h2... it will update the found cell to $100

Excelchat Expert
22/08/2018 - 03:15

oh oh...

Excelchat Expert
22/08/2018 - 03:16

ok that give me a min to think

User
22/08/2018 - 03:16

hey no prob. take your time. im in your hands lol

Excelchat Expert
22/08/2018 - 03:19

do you mind i i use cell A1 to store the name it will be dynamically updated

User
22/08/2018 - 03:19

thats fine

Excelchat Expert
22/08/2018 - 03:24

still working

Excelchat Expert
22/08/2018 - 03:24

in 1 min you would get a prompt to extend the session please click yes

User
22/08/2018 - 03:24

got it

Excelchat Expert
22/08/2018 - 03:30

check tab 15382100...

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 03:31

changed LOOKUP TAB row 2 value in H just to confirm

Excelchat Expert
22/08/2018 - 03:31

if the value in H is not found it give a 0

Excelchat Expert
22/08/2018 - 03:31

you there

Excelchat Expert
22/08/2018 - 03:33

you there

User
22/08/2018 - 03:33

im here

Excelchat Expert
22/08/2018 - 03:33

were you able to donwload the file

User
22/08/2018 - 03:34

downloading now

Excelchat Expert
22/08/2018 - 03:34

i can walk you through how to update the rest of the sheets as you have lot many of them

User
22/08/2018 - 03:34

okay revieiwing

User
22/08/2018 - 03:39

where did all the rows go? are they just all hidden?

User
22/08/2018 - 03:39

is that what you meant by updated dynamically?

Excelchat Expert
22/08/2018 - 03:40

Yes i had applied filter.. please remove the filter to get all the rows..

Excelchat Expert
22/08/2018 - 03:40

My bad I should have removed before sharing.. :(

User
22/08/2018 - 03:42

oh no prob

Excelchat Expert
22/08/2018 - 03:43

Does the solution look right?

Excelchat Expert
22/08/2018 - 03:43

I have updated the formula ontu sheet : 15382100

User
22/08/2018 - 03:44

hmmm...

Excelchat Expert
22/08/2018 - 03:44

I can walk you through on the steps to be performed for copying this over to other cells..

User
22/08/2018 - 03:44

i guess i just dont see where the formula you input is

Excelchat Expert
22/08/2018 - 03:45

sheet 15382100 I12 onwards

Excelchat Expert
22/08/2018 - 03:45

since

Excelchat Expert
22/08/2018 - 03:45

since I needs to be updated with H the formula needs to be in I

User
22/08/2018 - 03:46

whoa. i can do that to all 600+ sheets?

Excelchat Expert
22/08/2018 - 03:46

yes

Excelchat Expert
22/08/2018 - 03:46

:D

Excelchat Expert
22/08/2018 - 03:46

can i walk you through

User
22/08/2018 - 03:46

yes please

Excelchat Expert
22/08/2018 - 03:46

check cell A1

User
22/08/2018 - 03:46

haha. i was so disoriented for a sec

Excelchat Expert
22/08/2018 - 03:47

in sheet 15382100

User
22/08/2018 - 03:47

oh i see it

Excelchat Expert
22/08/2018 - 03:47

now copy paste that formula to the next sheet

Excelchat Expert
22/08/2018 - 03:47

the entire one you do not need to change anything

Excelchat Expert
22/08/2018 - 03:47

i have donw that formula in 15404095

Excelchat Expert
22/08/2018 - 03:47

but you can delete and try again

User
22/08/2018 - 03:48

i see

Excelchat Expert
22/08/2018 - 03:48

:D

Excelchat Expert
22/08/2018 - 03:49

let me know once you have done that

User
22/08/2018 - 03:49

so the first two sheets "15382100 and 15404095" are already done

Excelchat Expert
22/08/2018 - 03:49

so i can tell you the next step

Excelchat Expert
22/08/2018 - 03:49

yes

User
22/08/2018 - 03:49

and you used a1 for each sheet

User
22/08/2018 - 03:49

okay so how do i duplicate that for each sheet?

Excelchat Expert
22/08/2018 - 03:49

15404095 sheet only a1 formula is done not the others

Excelchat Expert
22/08/2018 - 03:50

now click on A1 in 15382100

User
22/08/2018 - 03:50

got it

Excelchat Expert
22/08/2018 - 03:50

then double click and copy the entire formula

User
22/08/2018 - 03:50

clicked a1 of15382100

Excelchat Expert
22/08/2018 - 03:50

=VALUE(MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255))

Excelchat Expert
22/08/2018 - 03:50

this one

User
22/08/2018 - 03:50

copied

Excelchat Expert
22/08/2018 - 03:50

the double click on A1 in 15404095

Excelchat Expert
22/08/2018 - 03:50

for now delete the formula

Excelchat Expert
22/08/2018 - 03:51

then paste the copied formula(they are the same just to make you know the steps)

User
22/08/2018 - 03:51

deleted a1 of 15404095

User
22/08/2018 - 03:51

i see

Excelchat Expert
22/08/2018 - 03:51

now try to do the same in 15439827 it does not have the formula so no need to delete

Excelchat Expert
22/08/2018 - 03:52

do you see the sheet name?

User
22/08/2018 - 03:52

i do!

User
22/08/2018 - 03:52

very good

Excelchat Expert
22/08/2018 - 03:52

ok next step

Excelchat Expert
22/08/2018 - 03:53

go to cell I12 in 15382100

User
22/08/2018 - 03:53

ok got it

Excelchat Expert
22/08/2018 - 03:53

this is the formula

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 03:54

now again double click on the cell

Excelchat Expert
22/08/2018 - 03:54

copy the formula

Excelchat Expert
22/08/2018 - 03:54

=IFERROR(INDEX(Table1[UPDATED PRICE CVN 80],MATCH(1,($A$1=Table1[NNPN])*($B12=Table1[COMPONENT]),0)),0)

User
22/08/2018 - 03:54

ok

Excelchat Expert
22/08/2018 - 03:54

go to sheet 15404095

User
22/08/2018 - 03:54

copied

Excelchat Expert
22/08/2018 - 03:54

here the 1st cell you need the formula in is I11

User
22/08/2018 - 03:54

alright

Excelchat Expert
22/08/2018 - 03:55

now paste the formula do not click enter

Excelchat Expert
22/08/2018 - 03:56

now change $b12 in the formula to $b11

Excelchat Expert
22/08/2018 - 03:56

corresponding cell to I11.. this is the only change you would need to make while pasting the formula rest is the same

Excelchat Expert
22/08/2018 - 03:56

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 03:57

now click cntrl+shift+enter that is because it is a array formula not a regular formula

Excelchat Expert
22/08/2018 - 03:58

after the above step you would see { .. } around the formula .. indicating its a array formula

[Uploaded an Excel file]

User
22/08/2018 - 03:58

do i need to do this to each sheet?

Excelchat Expert
22/08/2018 - 03:58

yes

Excelchat Expert
22/08/2018 - 03:58

no other way

Excelchat Expert
22/08/2018 - 03:59

after that you just need to drag it..

Excelchat Expert
22/08/2018 - 03:59

were you able to follow the steps?

Excelchat Expert
22/08/2018 - 04:00

you there

Excelchat Expert
22/08/2018 - 04:04

the only other option you have is vba

Excelchat Expert
22/08/2018 - 04:04

but here we do not support that

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*