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 John D Your Own Question

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9655
Experience:  Worked on Macs and PC's for 20+ years.
53785
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

I am using Excel 2008 on a Mac and I am trying to have information

Customer Question

I am using Excel 2008 on a Mac and I am trying to have information entered on the 1st worksheet populate cells on another worksheet.
The 1st wksht is named Entry Tab. The names of the other wkshts correspond to names on the drop-down list that populate col:A of the 1st wksht, named Entry Tab. The drop-down list is named Product.
Example: If CRVI is selected from the drop-down list in col:A on Entry Tab, I would like the info entered in that row in col:D–col:J to populate cells in the wksht named CRVI in col:B–col:H, including any blank cells.
I would like to, at some time, be able to delete entries made on Entry Tab while retaining that info posted to the other wkshts.
Submitted: 3 years ago.
Category: Mac
Expert:  John D replied 3 years ago.

Hi,

 

Since Excel 2008 cannot run VBA macros, so we are confined to using formulas. If you could send me the file I will take a look at it and let you know.

 

Customer: replied 3 years ago.
How can I attach the excel doc in this message?
Expert:  John D replied 3 years ago.

Ok, go to www.wikisend.com and upload the file there. You will then get a page that has the File ID and Download Link. Copy either the File ID or the Download Link and come back here and paste it in your reply.

 

If the file has sensitive information you can either replace the sensitive data with dummy values, or let me know before you upload it.

 

 

Customer: replied 3 years ago.
The file ID is 447428
Expert:  John D replied 3 years ago.

Got it, thanks.

 

There seem to be some inconsistencies between the sheet names as they appear ion the sheets tabs and the names in the drop down list. They should match 100% in order for it work Do you want to fix the names and send it back or would you like me to do that myself.

 

 

 

 

 



Edited by John D on 8/31/2010 at 8:58 PM EST
Expert:  John D replied 3 years ago.

Ok, I had to correct the drop down list source data so it matches 100% the worksheet names

 

Here you go

 

FILE

 

The formulas are in the yellow cells on the Entry Tab worksheet

 

Hope this is ok. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the work.

 

 

 

Customer: replied 3 years ago.
Thanks for finding the inconsistencies. I found a few more in the list names that I used in the indirect validation function that I had setup in col:E. Is there a way to reestablish that function for that column and the validation functions that were in columns H and J?
I took it for a spin but could not get the cells on the Entry Tab wksht to populate their corresponding cells on the wksht defined by the Product in col:A.
I have uploaded the file at wikisend with file ID 968922.
Expert:  John D replied 3 years ago.

Hi,

 

Not sure I understand what you are trying to do, you seem to have typed data in the cells that have formulas thus erasing the formulas. Of course the formulas won't work any more because there are no more formulas in these cells. I am referring to the data that you have entered in rows 6 and 7.

 

If you want me to reinstate the formulas in these rows so they will pull the information from the relevant sheet let me know.

 

Please don't forget to click ACCEPT as this is the only way I get credit for helping. I will of course continue to assist if you need further assistance or if you have any question.

.

 

 

Customer: replied 3 years ago.
The Entry Tab wksht is the wksht that will be the point at which all data will be entered. As a Product is selected in col:A, I would like information to be posted to the wksht with the name that corresponds to the product selected in col:A. (After looking at other functions, I think that what I am attempting is like a comboBox function for the Entry Tab wksht.)
Expert:  John D replied 3 years ago.

I'm sorry I don't understand.

 

If you want me to continue on this part please remember to click the accept button.

 

 

John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9655
Experience: Worked on Macs and PC's for 20+ years.
John D and 5 other Mac Specialists are ready to help you
Expert:  John D replied 3 years ago.

Thanks.

 

In the file that I sent you there are formulas in columns D thru J on the 'Entry Tab' sheet. These formulas read the selected Product in column A, and read the Page # XXXXX column C, then it will go to that Product sheet and look for that Page # XXXXX that sheet. If it finds a match it will pull the information from that row on the Product sheet and places that information in the corresponding column on the Entry Tab sheet.

 

Please review the above procedure and let me know which part, if any, you want me to modify.

 

 

 

 

Customer: replied 3 years ago.
I have edited my original message to try to clarify my intent.
I am trying to have information entered on the 1st worksheet (Entry Tab) and eventually have that data populate cells on another worksheet.
The 1st wksht is named Entry Tab. Users will not be entering info on any other wksht. The names of the other wkshts correspond to names on the drop-down list that populate col:A of the 1st wksht. The drop-down list is named Product.
Example: If CRVI is selected from the drop-down list in col:A on the Entry Tab wksht, I would like all info entered in that row, in col:D–col:J, (Lsn #, Element, Description, Date, Contributor, Notes, and Status), to eventually populate cells in the wksht named CRVI in col:B–col:H, (Lsn #, Element, Description, Date, Contributor, Notes, and Status), including any blank cells.
I would like to, at some time, be able to delete the user's entries made on the Entry Tab wksht while retaining that info on the CRVI wkshts.
Expert:  John D replied 3 years ago.

I see. Ok so it is the other way around. The Products sheets should populate with data from the Entry Tab.

 

Ok in which row on each sheet would that data go. For example if you enter the following data on the Entry tab:

 

column A --> CRVI

column C --> 0056

 

in which row would the data go on sheet CRVI? will it be added to the bottom of the list (i.e. row 124), or will it replace any existing Pg # XXXXX ? or where?

 

 

 

 



Edited by John D on 9/1/2010 at 10:53 PM EST
Customer: replied 3 years ago.
The page number is XXXXX only data that would not populate the related wksht.
I would like all info entered on the Entry Tab in col:D–col:J - (Lsn #, Element, Description, Date, Contributor, Notes, and Status) - to eventually populate cells in the next available row on wksht named CRVI in col:B–col:H - (Lsn #, Element, Description, Date, Contributor, Notes, and Status) - including any blank cells.
Expert:  John D replied 3 years ago.

Thanks for the additional information

 

Ok the only way that can be done is with a macro, and that because the result needs to be placed not in a fixed cell or row but rather into a variable location (e.g. first available row), and formulas can only return data in their own cells (i.e. cannot place data in other cells). I have however written the macro for that hoping you will be able to run in on Excel 2004 or another version of Excel (as I mentioned before Excel 2008 does not support vba macros).

 

Here is the file with the macro embedded in the Entry Tab sheet

 

XLSM version:

http://www.sendspace.com/file/uyxcri

(the download link is at the bottom of the page)

 

XLS version:

http://www.sendspace.com/file/lbctfn

(the download link is at the bottom of the page)

 

 

The macro is automatically triggered when the user changes the Product in any row on Entry Tab. So you need to first fill out the data in columns D thru J, then select the Product from the drop down list in column A. The macro will then copy the data from that row into the first available row of the selected sheet.

 

To access the macro right click on the sheet's tab at the bottom and choose View Code

 

Hope this helps. Let me know if you have any questions

 

 

 

Customer: replied 3 years ago.
Thanks!
Expert:  John D replied 3 years ago.

You're welcome. Feel free to get back to me if you need further assistance with this

 

 

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:

 
 
 
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
< Last | Next >
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • 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
 
 
 

Meet The Experts:

 
 
 
  • Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
< Last | Next >
  • http://ww2.justanswer.com/uploads/macthelife/2009-10-20_1899_mikesebaharsquare64.jpg Mike's Avatar

    Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
  • http://ww2.justanswer.com/uploads/AS/ashiknasameen/2012-5-15_141836_final2.64x64.jpg Ashik's Avatar

    Ashik

    Mac Helper

    Satisfied Customers:

    5282
    7+ Years of Experience in troubleshooting Macs, iPhone, iPad, iPod etc
  • http://ww2.justanswer.com/uploads/DP/dpean/2012-6-6_172828_avatorme1.64x64.JPG Daniel's Avatar

    Daniel

    Mac Genius

    Satisfied Customers:

    4670
    Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.
  • http://ww2.justanswer.com/uploads/VI/vinodvmenon2005/1.64x64.jpg Vinod Menon's Avatar

    Vinod Menon

    Support Specialist

    Satisfied Customers:

    2068
    worked as a Tech support Associate for Apple products
  • http://ww2.justanswer.com/uploads/BE/beboo/2011-1-14_201648_n5063313142021801763.64x64.jpg Brandon M.'s Avatar

    Brandon M.

    Mac Support Specialist

    Satisfied Customers:

    1501
    10+ Years Mac Support as contractor and currently an IT Manager for law firm
  • http://ww2.justanswer.com/uploads/MA/MacDruid/IMG_0232.64x64.JPG John T. F.'s Avatar

    John T. F.

    Mac Druid

    Satisfied Customers:

    1408
    20+ years in the computer/Mac industry
  • http://ww2.justanswer.com/uploads/MA/MacHelpdesk/1d2d506.64x64.jpg David's Avatar

    David

    Mac Support Specialist

    Satisfied Customers:

    1236
    BSc, H.Dip, Apple Certified