How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1282
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have two sheets in one Workbook. One is a live inventory

Resolved Question:

I have two sheets in one Workbook. One is a live inventory of my cooler doors with over 540 products. The other is a History tab with over 1400 products. I would like to link pricing information between the two. For example, when I change a beer name in my live inventory it pulls over the costing information from the history tab? Is this possible?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

so you want to pull information from the history sheet to the live sheet?

The-PC-Guy :

are the column order ever going to change in the history sheet?

Customer:

As we get new products in, the history tab will be added to, but I can make the previous 1400 products stationary

The-PC-Guy :

all that matters to make this formula work is that the collumns stay the same

The-PC-Guy :

that is the price column will always be in the same place

The-PC-Guy :

the name column will always be in the same place

The-PC-Guy :

then it wont matter if you add or delete rows

Customer:

Yes they will

The-PC-Guy :

ok

The-PC-Guy :

this should be reletively simple

The-PC-Guy :

can you provide a copy of your workbook

The-PC-Guy :

or even a sample

The-PC-Guy :

so I can see how it is layed out


 

The-PC-Guy :

and how you want the results displayed

Customer:

Yes I can provide a sample, can I attach it through here?

The-PC-Guy :

yes

The-PC-Guy :

if you click the papercflip icon

The-PC-Guy :

at the top of your chat window

The-PC-Guy :

it says image

Customer:

got it. sending in a second.

The-PC-Guy :

but it will work on excel files

The-PC-Guy :

think that worked

The-PC-Guy :

give me a minute to download

The-PC-Guy :

ok what are the sheet names

The-PC-Guy :

we are pulling data from

The-PC-Guy :

and putting it on

Customer:

I want to pull from History and add to Live

Customer:

History Bottle Inventory , Live Bottle Inventory to be exact

The-PC-Guy :

ok


 

The-PC-Guy :

so the name in column A of the live sheet

The-PC-Guy :

matches the name in column A of the history sheet

The-PC-Guy :

or does column B effect it?

Customer:

Yes the names match, but content in the columns do not match. Column B does correlate with Column A.

The-PC-Guy :

so for example in live sheet

The-PC-Guy :

the first thing

The-PC-Guy :

is Southern

The-PC-Guy :

Tier Backburner

The-PC-Guy :

there should be only one souther tier backburner in histroy?

The-PC-Guy :

and what columns in live sheet do you want me to populate

The-PC-Guy :

and what are the coresponding columns in history sheet

The-PC-Guy :

this is important

The-PC-Guy :

because it will effect the values you get

Customer:

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:

Customer:

Live sheet: Column B ( Bottle Size) C (Case Quantity) I (Case Cost) J (Unit Cost)

Customer:

I want to auto populate from History columns:

Customer:

Column B (Bottle Size) F (Case Cost) G (Unit Cost)

Customer:

So scratch column C from Live. Just Same.. Same Case cost, Bottle Size, Unit Cost, Based on the beer name

The-PC-Guy :

ok

The-PC-Guy :

think i got it

The-PC-Guy :

one more question before I get started

Customer:

Sorry. I know it's a lot

The-PC-Guy :

not really

The-PC-Guy :

but

The-PC-Guy :

you should know

The-PC-Guy :

that the formula will pull the first match that it finds if their are multiples

The-PC-Guy :

for example live row 77

The-PC-Guy :

like you described before

The-PC-Guy :

will return the prices for which ever one is listed first in history sheet

The-PC-Guy :

unless you have some way you want to distinguish them

Customer:

yes i do.. by column B

Customer:

if that is possible

The-PC-Guy :

you just told me though that I would be populating column B in live sheet from B in history sheet

Customer:

column A is the beer name column B represents type of bottle,can,size it is

Customer:

can we bypass that then?

The-PC-Guy :

may I make a suggestion

Customer:

yes

The-PC-Guy :

if you want me to populate column B in live from Column B in history that can be done

The-PC-Guy :

what I would do is in column A

The-PC-Guy :

for the name

The-PC-Guy :

I would put something like

The-PC-Guy :

Drink Name (Bottle)

The-PC-Guy :

Drink Name (Can)

The-PC-Guy :

on both live and history sheets

The-PC-Guy :

in cases where there would be 2 of them in history sheet

The-PC-Guy :

this would make it really easy for the formula to distinguish

The-PC-Guy :

and give you the correct prices

The-PC-Guy :

and still allow column B to autopopulate

Customer:

so keeping Column names and positioning the same, but naming the beer X+bottle X+can in the individual cell

The-PC-Guy :

something like that

Customer:

I can do that

The-PC-Guy :

the only thing is it would need to match exactly in both sheets

The-PC-Guy :

so

Customer:

okay that works

The-PC-Guy :

okay

The-PC-Guy :

then Ill just create the formulas

The-PC-Guy :

and let you take care of the naming

Customer:

okay

The-PC-Guy :

now if it doesn't match

The-PC-Guy :

that is if you have something on the live sheet

The-PC-Guy :

that is not on the history sheet

The-PC-Guy :

should I just have it put a 0 in

The-PC-Guy :

by default

Customer:

have it put a ? if possible.. something other than a value

The-PC-Guy :

well by default

The-PC-Guy :

it would put

The-PC-Guy :

N/A

The-PC-Guy :

as the value

Customer:

that works

The-PC-Guy :

ok

The-PC-Guy :

will leave defaults then

The-PC-Guy :

give me a few minutes

Customer:

okay

The-PC-Guy :

ok

The-PC-Guy :

the formulas are now in

Customer:

how's it look?

The-PC-Guy :

i will provide you the download link

The-PC-Guy :

now the way this works

The-PC-Guy :

is you can copy and paste the formulas into additional rows in the live sheet

The-PC-Guy :

if you need more

The-PC-Guy :

also

The-PC-Guy :

you can add or delete any rows from history sheet

The-PC-Guy :

and the formulas will pickup the new values

The-PC-Guy :

also you can change any names in live sheet

The-PC-Guy :

and formulas will update

Customer:

okay. That all sounds exactly what I needed.

The-PC-Guy :

please check it out

The-PC-Guy :

you can click the link to download

The-PC-Guy :

I have enabled the rating button

The-PC-Guy :

when you are satisfied it works

The-PC-Guy :

you can rate me with one of the smiley faces at bottom of chat window

The-PC-Guy :


---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://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.

Customer:

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.

The-PC-Guy :

I can make any changes if necessary

Customer:

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-PC-Guy :

yes

The-PC-Guy :

the names have to match

The-PC-Guy :

even extra spaces can throw it off

The-PC-Guy :

for example

The-PC-Guy :

ABC DEF

The-PC-Guy :

is not the same as

The-PC-Guy :

ABCDEF

Customer:

I saw that.

The-PC-Guy :

as far as excel is concerned

The-PC-Guy :

sorry no way around that, excel takes everything very litterally

Customer:

Thank you. I'll make sure it is all correct.

Customer:

That is a good thing. diminishes the chance for mistake

Customer:

so once I click rate the payment goes through ?

The-PC-Guy :

yup

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1282
Experience: 20 years experience providing remote computer support
The-PC-Guy and 6 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions