# Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 22/08/2018 - 03:08
how ar eyou doing
Excelchat Expert 22/08/2018 - 03:09
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
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
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...
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
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.