Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question
so you want to pull information from the history sheet to the live sheet?
are the column order ever going to change in the history sheet?
As we get new products in, the history tab will be added to, but I can make the previous 1400 products stationary
all that matters to make this formula work is that the collumns stay the same
that is the price column will always be in the same place
the name column will always be in the same place
then it wont matter if you add or delete rows
Yes they will
this should be reletively simple
can you provide a copy of your workbook
or even a sample
so I can see how it is layed out
and how you want the results displayed
Yes I can provide a sample, can I attach it through here?
if you click the papercflip icon
at the top of your chat window
it says image
got it. sending in a second.
but it will work on excel files
think that worked
give me a minute to download
ok what are the sheet names
we are pulling data from
and putting it on
I want to pull from History and add to Live
History Bottle Inventory , Live Bottle Inventory to be exact
so the name in column A of the live sheet
matches the name in column A of the history sheet
or does column B effect it?
Yes the names match, but content in the columns do not match. Column B does correlate with Column A.
so for example in live sheet
the first thing
there should be only one souther tier backburner in histroy?
and what columns in live sheet do you want me to populate
and what are the coresponding columns in history sheet
this is important
because it will effect the values you get
Correct. There are very few exceptions. For example: In live number 77 says Cigar City Jai Alai. In history there are two different Jai Alai's one that comes in bottle and one in can. that is shown by column B. They both have different prices. The columns I need to populate are:
Live sheet: Column B ( Bottle Size) C (Case Quantity) I (Case Cost) J (Unit Cost)
I want to auto populate from History columns:
Column B (Bottle Size) F (Case Cost) G (Unit Cost)
So scratch column C from Live. Just Same.. Same Case cost, Bottle Size, Unit Cost, Based on the beer name
think i got it
one more question before I get started
Sorry. I know it's a lot
you should know
that the formula will pull the first match that it finds if their are multiples
for example live row 77
like you described before
will return the prices for which ever one is listed first in history sheet
unless you have some way you want to distinguish them
yes i do.. by column B
if that is possible
you just told me though that I would be populating column B in live sheet from B in history sheet
column A is the beer name column B represents type of bottle,can,size it is
can we bypass that then?
may I make a suggestion
if you want me to populate column B in live from Column B in history that can be done
what I would do is in column A
for the name
I would put something like
Drink Name (Bottle)
Drink Name (Can)
on both live and history sheets
in cases where there would be 2 of them in history sheet
this would make it really easy for the formula to distinguish
and give you the correct prices
and still allow column B to autopopulate
so keeping Column names and positioning the same, but naming the beer X+bottle X+can in the individual cell
something like that
I can do that
the only thing is it would need to match exactly in both sheets
okay that works
then Ill just create the formulas
and let you take care of the naming
now if it doesn't match
that is if you have something on the live sheet
that is not on the history sheet
should I just have it put a 0 in
have it put a ? if possible.. something other than a value
well by default
it would put
as the value
will leave defaults then
give me a few minutes
the formulas are now in
how's it look?
i will provide you the download link
now the way this works
is you can copy and paste the formulas into additional rows in the live sheet
if you need more
you can add or delete any rows from history sheet
and the formulas will pickup the new values
also you can change any names in live sheet
and formulas will update
okay. That all sounds exactly what I needed.
please check it out
you can click the link to download
I have enabled the rating button
when you are satisfied it works
you can rate me with one of the smiley faces at bottom of chat window
---------------------------------------------------------------------------------------------------------------if you have any questions in the future you can visit my profilehttp://www.justanswer.com/computer/expert-1expert-2rulethemall/and ask a question right in my box,also you can put "PC Guy only" in your question title if you want to get to me.
Okay awesome. I just downloaded the link and now I am looking through it. I will rate as soon a I take a look. I appreciate your help. I should know in 1 minute or 2.
I can make any changes if necessary
Everything look's good. I really appreciate it. The errors I see are the ones that I have to fix with specific naming issues.
the names have to match
even extra spaces can throw it off
is not the same as
I saw that.
as far as excel is concerned
sorry no way around that, excel takes everything very litterally
Thank you. I'll make sure it is all correct.
That is a good thing. diminishes the chance for mistake
so once I click rate the payment goes through ?