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.
