LinkBack URL
About LinkBacks
Hello,
Can someone come up with code to fill in cells from 2 other sheets('AFRsDB' & 'AFRsParts') based on cell F2 on my 'AFRsInput' sheet.
Thank you in advance for your help,
FF
Hello,
Can someone come up with code to fill in cells from 2 other sheets('AFRsDB' & 'AFRsParts') based on cell F2 on my 'AFRsInput' sheet.
Thank you in advance for your help,
FF
Hi - I have taken a look.
Its unclear exactly what you are trying to achieve, however it does look like you can achieve what you need with if statements and vlookups.
Can you be more descriptive on what you need.
Cheers
Scar1
I think I will need a macro to do this since the sheet will be for entry also thank you.
Are you pulling information based on the dropdown F2 value?
Yes if you see the orange shape below with text information.
You can pull data into the form without a macro, if that's all it is used for. However, if you're using the same form to add data to the other two tabs, you'll need VBA to retrieve records.
Yes it will be for entering data also. Thank you
The first procedure goes in the worksheet code module for Sheet AFRsInput.
The second procedure goes in the standard code module1.
When an entry is made in cell F2 it will trigger the first macro which then calls the second procedure. If the entry is valid the second procedure will fill in the data on the input sheet. If the entry is not valid, a message will appear and the procedure will terminate.
Please Login or Register to view this content.Please Login or Register to view this content.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
I get error by trying this sorry.
The code is performing as it should. The problem is an anomoally in the worksheet that is causing vba to detect a merged cell condition. I tried running some routines to elimiunate any merged cells in the applicable ranges but that did not cure the problem and I cannot find where the cuprit is residing. Merged cells and vba don't mix well although it can usually be worked around. In this case, it is beyond my technical skills to locate and fix the cause of the code interruption. Sorry. Maybe someone else can come up with an idea that will work.
Regards, JLG
Thanks for your efforts.
Last edited by AliGW; 03-16-2019 at 07:03 AM.
To draw attention back to this thread, you should bump it by posting again to it. Please do NOT open new threads directing members to look at another thread of yours. The thread you opened to do this has been deleted. Thank you for your co-operation.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
My apologies how do I bump it please? I will cooperate as you wish.
You just have - as I said, you just need to post to the thread again. However, we don't expect you to do this too soon - if a day has elapsed since the last post to your thread, it's fine to bump.
I thought by having replies at first asking questions that I thought I mentioned in the original post lessons my chances of getting actual help. My fault for assuming. Have a wonderful day!
It does (but only because it is removed from the unanswered threads list), but then if helpers need clarification, they need clarification and need to answer. I think what you assumed was that you had given enough detail, when in fact you hadn't.
Forum Rule #5 covers this: https://www.excelforum.com/forum-rul...rum-rules.html
Did you look at the workbook? I had a shape on there describing the details.
No - I don't do VBA, sorry. My posts here were to do with moderation issues. I am sure someone who does will take a look, but please remember that it is a weekend.
Here's a solution using formulae (well, the first half - I'll post the rest later). It uses variants of this INDEX MATCH formula:
=INDEX(Table2[[SO '#]:[Notes to Customer]],MATCH(AFRsInput!D$4,Table2[AFR '#],0),MATCH(AFRsInput!J13,Table2[[#Headers],[SO '#]:[Notes to Customer]],0))
Last edited by AliGW; 03-16-2019 at 08:25 AM.
Thank you AliGW for helping me but I apologize again I will need code because this sheet will also be for entering data.
I really do appreciate your efforts.
Two more formulae required to produce the table on the right:
=IF([@Qty]="","",$F$2)
=IFERROR(INDEX(Table4[Qty],AGGREGATE(15,6,ROW($1:$500)/(Table4[AFR '#]=$F$2),ROW(1:1))),"")
Final non-VBA solution attached.
OK - well never mind, than. You'll have to wait for a coder.
If the idea is to pull data from sheets 2 and 3 to sheet 1, amend it then send the updates back then I can help with that.
I just need an explanation of what the table in columns N:Q is for and how it will be filled out. i.e. will it only ever have 3 entries per AFR# or could this vary?
BSB
Thank you for help in advance. I updated the workbook without any code.
The AFRsInput sheet will be for looking up records and also for inputting new records. If you look at the shape in orange it explains the ranges needed.
The AFR# is the control ID for the records.
Which bit of this does my solution in post #21 not do?
If there is more to it, as you suggested above (i.e. input), then you need to clarify.By selecting AFR #
1) D4:D18 & H4:H18 & L4,L7,L10,L13,L19 will fill in data from 'AFRsDB' sheet based on the AFR # selected in F2.
2) N3:Q3 and down will fill in parts from 'AFRsParts' sheet based on the AFR# selected in F2.
I understand that bit. What I don't understand is the the "Parts" table, hence the clarification questions above.Originally Posted by fredfarmer
BSB
AliGW your formulas work perfectly if this was just a reference sheet. I may consider doing that and have another sheet for input. I would prefer having one sheet to do both though.
You are very kind for helping.
So, where and what are the inputs going to be? This bit is not at all clear in your sample workbook. You need to give us the whole picture. We can only provide solutions based on what you are showing us that you want.
BSB,
The parts table is for parts that are used for each AFR kind of like a repair order at a shop.
The inputs will be all the ranges in the orange shape. Which will be the same ranges that your formulas cover.
Sorry, but that's not what you said.
In the orange box you tell us that you wish to select a number in F2 and from that automatically populate all those cells mentioned. So, explain in words how you really do want it to work.
The cell F2 will be for selecting previous AFRs and fill out all of the ranges like your formulas do very well.
I would like to also have the ability to create a new AFR. Have a macro to create a new AFR which will clear all of the ranges and manually fill them out. Then once they are filled out click another shape to save that AFR.
Thank you
Thanks - now it's clear what you want. It would have helped if you had said this clearly right at the start!
I'll leave it to BSB.
I truly apologize for my ignorance. You have been most helpful.
Apologies for the delay (hectic day!)
Give the attached a try for starters.
It will recall the data when you change the AFR# in F2 and you can amend as you see fit.
There's a button at the top of Column L that when clicked will update any changes you've made to the table on sheets AFRsDB.
I'm still none the wiser to how you use the Parts table so although it will populate when you change the AFR I've not added any code to make that stamp data to the part sheet (yet).
Anywhere near what you need?
This whole things would be easier (and look nicer) is it was done using a UserForm
BSB
Also the ability to add new rows to the bottom of the table still needs some work as you have it set up not to allow anything but the validation list in F2.
So how do you generate a new AFR?
BSB
I would like a code to 'Add' new AFR which will clear the ranges and then start to enter the number in D4 then fill out the rest of the fields. When satisfied have another code to 'Update' the information which will save to both 'AFRsDB' and 'AFRsParts' sheets.
OK, I have to pop to the shops but will be back in an hour and will work on that then.
Does it do what you need so far?
BSB
Oh I didn't see your latest update. That works great thank you. Is it possible to have the Add New also?
I also have to go out for a while normal Saturday errands. I appreciate everything and will return later.
The attached version now has an "add new" button that when clicked will add a new row to the DB sheet and increment the AFR# by 1 based on the highest in there so far.
I presume that will work for you but can revisit if these numbers should be derived in a different way.
Need more info on the parts table. Easy enough when it's a new record being entered as it will (it doesn't yet!) stamp anything in that table to the bottom of the data on the third sheet.
But what if you're amending a record, would you amend those existing figures? Would you add new rows to that table?
All important things to know before I can write the code..
BSB
I think part of the problem is that your data is all in tables and the code I wrote does not address tables. I don't have enough experience working with tables to fix the code so it will do the same thing with tables that it does with untabled data. Some of the code works fine, but then it hits an area where the sytax will not execute properly because of the way the tables are seen by vba.
I see that @BadlySpelledBuoy has already started with the proper code for use with tables.
Last edited by JLGWhiz; 03-16-2019 at 02:24 PM.
Conditions of Parts Table:
1) Able to add new parts to new record
2) Able to delete parts and have the AFRsParts sheet update
3) Able to update parts to AFRsParts and AFRsDB with Update button
Thank you
Hi BSB,
Almost everything is working at this point. The Parts aren't updating on the 'AFRsParts' sheet though when adding new records.
Thank you so much,
FF
Good evening FF,
Perhaps this version will do all you need?
At first I had it updating the parts list by looping through and overwriting, but then I remembered you need to delete too so changed it so that it just deletes any parts list for the given AFR and then adds them to the list again.
This means if you've deleted any from the parts list on Sheet 1 then it will also get deleted from Sheet 3.
Give me a shout if I've missed anything or if it's not doing quite what you need.
BSB
Hello BSB,
I selected the 'Add New' which cleared and made ready for new entries which works fine. Then I started to add a new AFR 10010 in D4 and filled out the rest of the fields but when selecting 'Update Data' it didn't paste the data to the 'AFRsDB' but it does paste to the 'AFRsParts'. Though it will paste the same info there ('AFRsParts') as many times as the button is selected.
One request also if possible would be to insert the AFR number in Parts Table when entering parts. I want to hide that column eventually.
I appreciate it very much.
Last edited by fredfarmer; 03-17-2019 at 06:45 PM.
Hello BSB,
I was wondering if it's more logical to have one cell (D4) on the 'AFRsInput' sheet for the AFR #. It seems that it is prone to errors having the same value in multiple places.
Could it still be a list and if the number isn't there prompt: "Would you like to add a new AFR?"
What do you think?
Also the 'Add New' is set to increment to the next number. Could this be open to allow the user to put a number that is not already in the list?
Thanks so much again
Last edited by fredfarmer; 03-19-2019 at 09:29 AM.
please bump to front thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks