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 Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4478
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I have a spreadsheet that has some formatting and I am having

Resolved Question:

I have a spreadsheet that has some formatting and I am having difficulty with IF then statements and producing the actual number if it is true AND then copying that result to another worksheet
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  aitizaz replied 1 year ago.

Aitizaz :

Hello and welcome! My name is XXXXX XXXXX I look forward to assist you today with your question and provide the best answer possible

Aitizaz :

kindly share the spreadsheet and also the requirement that you want to achieve

Customer:

Thanks - I am trying to do different quoting templates and gather the information from the main Census Data page. They have changed up some of the requirements and I am having difficulty filling in all the information on the UHCNEW, New Allsavers page. Also, on the census data worksheet - I need to calculate the age of the children and spouses but do not want it to show an age unless there is a date of birth - it is defaulting to 114. I cannot seem to get that figure to transfer to the other pages.

Customer:

I do have some issues with the tab Aetna but that is a different story that if you can help me with this - I will address.

Attachment: 2013-10-22_134755_hello_kitty_revision_1.xls

Full Size Image

Aitizaz :

i have a pre-schedule engagement due to which i will not be able to work on your assignment. however, i will redirect you to another expert who will help you. thank you for your patience

Customer:

ok,

Customer:

not sure if you received the first information or if someone is going to contact me again

Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

I got the Excel file that you posted. Can you please tell me which worksheet are you working with in this file?


Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 1 year ago.


Yes, what I am doing is cutting and pasting from a separate excel workbook the basic information on the original Census worksheet to the CENSUS Data tab. Last name, First, zip, DOB, etc.


 


My goal is to automatically fill in the remaining spreadsheets with that information. Just the UHCNEW, FLorida Blue, New Allsavers. For the New Aetna they have changed the format and that is something that I will need additional help with.


 


So I have been playing with different formulas and if then statements but there are a lot of formats in this and some things are just not copying over - like DOB calculations and if there is no DOB, I don't want it to calculate age


 

Expert:  Jess M. replied 1 year ago.
Thank you for writing back.

Are you now more concerned on the DOB columns in the UHCNEW sheet? I believe there are already working formulas there to show the DOBs.

Or are you concerned on New Aetna sheet? I can't grasp all these data in the file at once so I need specific description of the requirement in every sheet that you want to work on.

For instance, you mentioned "if there is no DOB, I don't want it to calculate age", which sheet are you referring to and which columns?

Jess
Customer: replied 1 year ago.


Okay, on the Census Data worksheet - under DOB Child1 - I put the formula in to calculate the age based on O13 (today's date) - minus the DOB of the child (same with Spouse) but if there is no child or spouse that exists - it calculates 114.

Expert:  Jess M. replied 1 year ago.
Hi,

I saw your original or previous post saying "Also, on the census data worksheet - I need to calculate the age of the children and spouses but do not want it to show an age unless there is a date of birth - it is defaulting to 114."

So what you actually want is to keep the DOB cell blank if there is no date of birth, instead of giving 114. Is this correct?

Here is the formula for that:
=IF(V13="","",(O13-V13)/365.25)

The value in V13 is the date of birth that is subtracted from Today then divided by 365.25 to get the current age.

You need to test V13 if it is blank using the IF statement. If it is blank, then the cell shall be blank, if not, then it shall give the result of the formula (O13-V13)/365.25.

It is when there are no values in column V that 114 is shown because it is like Today - 0 then divided by 365.25 giving 114.

Here is my sample or revised file:
http://wikisend.com/download/118754/hello_kitty_revision_jess1.xls

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! Cool

If you have any other questions, please ask me more or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Customer: replied 1 year ago.

Can you please look at the New Aetna worksheet and let me know if there is a way to extract the information on the
Census Data worksheet and put in the information. My concern is mixing columns and rows and getting the correct information.


Not sure if adding six rows of children to each Subscriber would work or if there is a better way.


 


 

Expert:  Jess M. replied 1 year ago.
What exactly are the data in Census Data worksheet that you want to move or integrate into the New Aetna worksheet?

There are several ways to lookup information from Census Data and then pulled out into the New Aetna worksheet. Please specify conditions or parameters to generate the correct information that you want displayed in the New Aetna worksheet.

For instance, you can pull out data from Census sheet TO the New Aetna sheet based on First name, Last name, or other columns in Census Data sheet.

Regards,
Jess
Customer: replied 1 year ago.


What I am trying to do is get the employee data, the spouse information and the possible children information into the template that is set up under New Aetna. The information is all in rows on Census Data but on the Aetna template the information for the spouse and possible children are listed in columns.

Expert:  Jess M. replied 1 year ago.
In the New Aetna sheet, only columns C to H are derived from the Census Data. Are you saying that you are free or allowed to insert columns in New Aetna to accommodate other information from Census Data sheet like Spouse and Children?

Jess
Customer: replied 1 year ago.


Not columns, but rows. I am trying to get the information like Subscriber, Spouse and Child information from columns into rows on the Aetna.


 


Have been just trying to copy formula to match cells, but feel there is an easier way.


 


I am going to a meeting and I will not be back until 1 PM - EST Thanks for your help


 

Expert:  Jess M. replied 1 year ago.
Please let us take a specific example. In your New Aetna Sheet, you have first Subscriber there is JOnes, that is in Row 2. Can you please tell me if there are missing information of JOnes in Row 2 that are present in Census Data sheet that you want to add in Row 2?

The columns of information are:
Last Name
First Name
Home Zip Code
Age / Date of Birth
Gender
Employment Status
Tobacco Status
Medical Tier

Are there other information in Census data that you want to add?

Also, in Rows 3 and 4, they represent Spouse and Children Info for Jones. What other information do you want to add here?

I am trying to understand your requirements, thus having this inquiry.

Or are you looking for a better way to fill out the information on the columns using the data in Census sheet?

Keep me posted.
Jess
Customer: replied 1 year ago.


Yes, I am looking for all the columns in the New Aetna to be filled in with the information on the Census Data for the Subscriber only - I only need the DOB and Gender for the child and spouse information. My confusion comes in with the information on B14's spouse is located in column Q, R and S, which I only need that information filled in F&G on the New Aetna sheet.


 


Make sense?

Expert:  Jess M. replied 1 year ago.
Yes, thank you for that confirmation. If you are just filling up the New Aetna sheet with the respective data from Census Data sheet, then there are ways to do this.

Please give me a moment to create a new revision of your file with the needed formulas.

Regards,
Jess
Expert:  Jess M. replied 1 year ago.
Hi,

Thank you for patiently waiting. Indeed your collection of data is irregular because the Census Data sheet are in columns and the New Aetna are in rows. I believe that you will agree that you cannot easily DRAG the formulas down to automatically copy because of the data inconsistency in terms of layout.

Now, for the Subscriber entries, I added a new column to reflect the "Full Name" to match the info in Census sheet. Also, the big purpose here for this new column is to facilitate the VLOOKUP formula so that you can easily pull out relevant information like Zip, Age, DOB, and Gender.

The VLOOKUP formula will look for the full name, when found, it will pull out those data.

After that, you can them DRAG the formula down to copy it to the other cells. However, the critical part is, you need to DELETE the contents for cell range G:I for Spouses and Children because their corresponding data will be pulled out in another way. This is the reason why I said "you cannot easily drag" formulas to copy them since the records are not continuous.

To summarize, the vlookup formula will only work for Subscribers and I suggest you do this first and then drag the formula down to copy. Then DELETE the cells in the range G:I for Spouses and children.

For Spouses and children, the easiest and fastest way is NOT though functions in a formula since you cannot manipulate those range of data because of inconsistent layout. The best approach is by direct cell reference. This is manual though but you just need to do it once in the New Aetna sheet.

This "cell referencing" method is better than simply copy&paste because when you change the data in Census Data, the referenced data in New Aetna are updated automatically.

For instance, in New Aetna sheet, cell G3 that requires spouse's age, type = to insert a formula, click on the Census Data sheet, then locate and click on Q13 and press Enter. That should give you the content of Q13 in Census Data sheet for the New Aetna sheet.

Since you need the other adjacent data for the spouse like DOB or Gender, you can just drag the fill handle of the cell containing the formula to the right to copy the other contents.

Here is my completed sample file:
http://wikisend.com/download/240608/hello_kitty_revision_jess2.xls

Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! Cool

If you have any other questions, please ask me more or reply to me – I’ll be happy to respond.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4478
Experience: Computer Software Specialist for more than 10 years
Jess M. and 5 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

Chat Now With A Microsoft Office Technician
Jess M.
Jess M.
330 Satisfied Customers
Computer Software Specialist for more than 10 years