Log In
Register
Home
June 1, 2020
Offline
Lets say I have a workbook with 4 sheets
1st sheet:
Lets say I have a table.
Column A contains rows, with each row being a drop down list having Yes or No.
Columns B-D contain information on products.
Columns E and F are blank.
2nd-4th sheet:
These sheets are invoice tables for different customers, all the same format.
Rows tagged Yes in the first sheet can show up in these invoice sheets through data validation.
There are two cells in this sheet that contain numbers, calculated & dependent on those designated rows.
These two cells are what fills up columns E and F on the first sheet, but I have been doing this manually.
Is there a way I can automate this?
If I do an if statement with the logic being: If this row(s) (in sheet 1) is tagged, calculate the numbers in those two cells on the other sheets, then make sheet 1's columns E and F refer to those cells. BUT, once those are untagged as Yes, then columns E and F go away. Which is not what I want. I want them to stay permanently as long as the row has been tagged Yes. How can I do this?
Thank you
Trusted Members
December 20, 2019
Offline
Pretty much anything can be automated in Excel.
To make things easier can you mock up a workbook of what you are after can post it on here and i will see what i can do
Purfleet
June 1, 2020
Offline
I have uploaded a sample workbook. So on sheet 1, I have tagged those rows "Yes".
If you go to sheet 2, you can see that you can see those rows in the table. Once these rows are tagged Yes in sheet 1, I want cell K7 (Sheet2) to go in cell L2 (Sheet1) &&& cell K8 (Sheet2) to go in cell K2 (Sheet1). I am looking a way for these to be done automatically. Sheet 3 would be an exact copy of sheet 2.
However,when I need to make a new invoice, once untagged Yes in Sheet 1, I still want K2 and L2 (Sheet1) data to stay.
Thank you
October 5, 2010
Offline
Hi Carlos,
No file attached.
There's a video here that shows you how to attach files
https://www.myonlinetraininghu.....this-first
Regards
Phil
June 1, 2020
Offline
Here is the file
October 5, 2010
Offline
Hi Carlos,
I don't see how that can work as described. If you have the invoice being populated with data from the Shipping Dept sheet, then the invoice number and date are fed into the Shipping Dept sheet, what happens the next time you want to use Item # 10 and 11 in another invoice? Won't the invoice number and date for this new invoice over-write the previous values in the Shipping Dept sheet?
I don't have all the data but the way I'd approach this is to have a lookup table on a sheet for customer and item information and then use data validation lists on the invoice to populate the line items from this.
In another table you can record the invoice numbers and dates but you'll need a separate row for each invoice.
I don't know if the invoices will exist in the workbook forever, in which case you might just be able to use lookups to record the invoice number and date. Or if the invoices get removed then you'd need VBA to do this recording.
As I say I'm guessing a bit here as I don't understand the full process you go through. If you can provide details of the whole process and more data with examples of the records you want created that will help.
Also, it's not a good idea to merge cells, they can cause issues with VBA and formulas. There are a number of them on the invoice sheet. You should unmerge them and just use 'Center across selection' from the Alignment tab of the Format Cells dialog.
Regards
Phil
June 1, 2020
Offline
Thank you for bringing that up. There is a lot of unnecessary information that I excluded because it would have made it more confusing, but I guess that is something that should have been mentioned. Let's say we make an invoice for items #3, #6, and #10. For the next order we receive, the items included in that next order will be added to our list. Even if it is the same item as item #3, it is for a different job. So instead, this item could be item #11. We have a column of "Purchase Order #" that I did not include which helps us differentiate items such as #3 and #11. I will provide a recording of what I am trying to achieve to make it easier to understand. Sorry for the confusion.
1 Guest(s)
