You are here:

Excel/2 workbooks updating a third.

Advertisement


Question
QUESTION: Hi to you Bob
First of all I would like to thank you for just taking the time to help lesser mortals like myself.
I have a big ask right now, maybe too big.

The title says 2 workbooks updating a third, its quite a bit more than that in as much as some macro/code required on the third workbook, or, you may suggest a better soloution.

1. What I would like is 2 multi-sheet workbooks (Share Sheet Silver Dawn & Share Sheet Stelissa) automatically updating a third single-sheet workbook (Crew Shares) with Trip Number (from B2), Date (from B4) and Net Mending (from F46) and Total Paid (from 10 rows down and 1 column right of the name)

2. To have a button on the Crew Shares sheet, when pressed to ask for Start & End dates, then show Only data within those dates.

3. Another button, when pressed, gives a “list” of the crew, generated by the ever changing contents of row starting G2 to ever expanding row 2, then show Only columns A, B & C and the column matching the crew name, and Only rows with data including the sub-total row.

4. A button to print “Only columns A, B & C and the column matching the crew name, and Only rows with data including the sub-total row”

5. Be able to leave the date filter in place and pick another crew name to show and print.

6. A button to clear ALL filters.

7. If you can help with all this, probably the hardest part of ALL, I would like to try and understand what and how it has been achieved, as my knowledge is very limited.

I have put the three workbooks mentioned above (with limited data) on Microsoft’s OneDrive, these can be accessed with the following link>
https://onedrive.live.com/redir?resid=4A23C96634544272!110&authkey=!AIfNfuemmQnGJZg&ithint=folder%2c.xlsm

Once again, thank you for your time.
Regards
Keith
(Penzanze UK)

ANSWER: There are a number of issues with your request aside from it really being too involved for "ask an expert" - this isn't a question, it's a small job!
Aside from that, there are inconsistencies with the data, making the task either not possible or quite involved. For example, in the Crew Shares workbook, you have Sliver Dawn instead of Silver Dawn; the values for James Black (Col Q) seem to come from EITHER B36 or B37 (inconsistent) with no explanation of which one is the right to choose. Or perhaps it should be B43 or other.; Your saying in step 1 "...Total Paid (from 10 rows down and 1 column right of the name)...) -- where is "the name"? What name?

Since this is involved, if you could answer the above questions and make the data consistent, this can be worked out, but not gratis - tell me what you're willing to pay and I will let you know if/when I can do it, with the macro fully documented.

Feel free to ask another if you find this unreasonable.

---------- FOLLOW-UP ----------

QUESTION: Hi Bob,

Thank you for your reply.

I fully understand what you are saying, about this be more like a "job".

Like you, I am doing this to help someone else, in this case the mother (in her 70's) who is trying to help her son with his records.

As the only thing I get out of this is the satisfaction that I have helped someone, (I guess you can understand that being an expert volunteer) and mother does it for the love of her son, I am unable to pay you for your services.

As steps 2 onwards in my last post, was just something I thought would be “nice” for the lady, it is not something she would use more than perhaps once a year, so I think I can forget that part.

So, would it still be too much of an ask to request your assistance for part 1 only?

1.   What I would like is 2 multi-sheet workbooks (Share Sheet Silver Dawn & Share Sheet Stelissa) automatically updating a third single-sheet workbook (Crew Shares) with Trip Number (from B2), Date (from B4) and Net Mending (from F47) and Total Paid (from 10 rows down and 1 column right of the name)

I apologise for the inconsistencies, totally my fault having manufactured this data purely for this exercise.  I have rectified the Silver Dawn error and input new figures as well as altering some of the layout to make both Share Sheet books consistent except for the first sheet in each book.

I will try and clarify my request.

When entering data into Share Sheet Silver Dawn & Share Sheet Stelissa, I would like the workbook Crew Shares updated with the
Vessel Name (A1)
Trip Number (from B2)
Date (from B4)
Net Mending (from F47)
Total Paid to the crew member
Crew Name in A5 Total Paid in B15
Name in E5, Total Paid F15
Name in A19, Total Paid in B29
Name in E19, Total Paid in F29
Name in A33, Total Paid in B43
Name in E33, Total Paid in F43.

Below is a new link to amended files.

http://1drv.ms/1gXDzYf

I hope this is a bit more doable.

Kind Regards
Keith

ANSWER: More questions:
1 - Most of Net Mending I find in F49, not F47 - should I check where the text "Net Mending" is?
2 - You say you want Crew Shares updated when entering data. When? With each cell entered? If I enter SOMETHING in one cell on Share Sheet Silver Dawn, do I update everything? How do I know if I need to start a new line or not? I think instead of updating Crew Shares with EACH cell entered, there should be a button which needs to be clicked to enter all the current data. But if the button is pressed 3 times, then each press will create a new line with the same data. Do you need error checking so this doesn't happen?
3 - where is "Total paid to the crew member"? Is that B15? Confused because you seemed to mention it twice.

---------- FOLLOW-UP ----------

QUESTION: Hi Bob,

I’m sorry for the confusion, I hope I have clarified it below, I must admit I do find difficult to explain things simply.

More questions:
1 - Most of Net Mending I find in F49, not F47 - should I check where the text "Net Mending" is?

All Net Mending should be in cell F47 for both Silver Dawn and Stelissa.

Please see amended files on new link.

http://1drv.ms/1hjgjEa


2 - You say you want Crew Shares updated when entering data. When?

I like the idea of a button “Update Crew Shares”

When data entry is complete press the button to write a new row in the Crew Share sheet.

If the “Date”, “Where working” and the “Trip No” already exist in the Crew Share sheet, prompt “This trip has already been recorded.  Do you want to update the figures?  
If NO do nothing, if YES update the figures


3 - where is "Total paid to the crew member"? Is that B15? Confused because you seemed to mention it twice.

As can be seen below, it is in fact mentioned 6 times, but should have the same cell references in both Stelissa and Silver Dawn.

Name in A5, Total Paid in B15

Name in E5, Total Paid F15

Name in A19, Total Paid in B29

Name in E19, Total Paid in F29

Name in A33, Total Paid in B43

Name in E33, Total Paid in F43

Also, if a new crew member arrived, would Brenda have to enter the name into the Crew Share sheet before pressing the button “Update Crew Shares” or could a column be added and the name inserted when the button is pressed.

I can only hope that I have explained things well enough.

I look forward to hearing from you.

Kind regards,
Keith

Answer
I lost your email address (if I ever had it) - I can send you the updated files. Please note that in the Crew Shares workbook you have "Alister" and in the Stelissa workbook you have "Allister" -- if they don't match exactly, they won't be able to be updated. You're going to have to ensure all is correct.
Yes, if a new crew member is used, that name would have to manually be entered in the Crew Share sheet.
Instead of putting a button on every sheet, there's a new command at the bottom of the right-click menu to use.

I will send you the files when I have your email address.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.