Forum Discussion
COMBINE NAMES WITH AMOUNTS
Good day!
I hope somebody is able to help me with the following issue.
I am working on an excel file which is going to contain a lot of rates. To make things a little easier i am trying to combine names with amounts.
for example
A=10
B=20
C=30
D=40
If I mention A in collom A1, collom K1 (with name KA) needs to be filled in with 10 automatically.
The same needs to happen when I mention B, C and so on.
A also contains other rates such as 20 30 40 and 50.
The information is mentioned in a second sheet and needs to be transferred into sheet 1.
If you need any further information please let me know.
Thanks in advance! 🙂
4 Replies
- Hi! Can you post a little more of an example what you want your sheet to look like? I'm not following name mentions in columns A1, K1. How are you referencing, what is your data input, where do you want your data output, etc.
If you can copy paste a sample table even if you manually type in what you want it to look like it'll help so we can get a better idea of what you're trying to do.Dustin-Doucette
Thanks for your reply!I have added 2 print-screens, hope this makes things a little more clear.
I have tried multiple options but without any success.
information from page 2 needs to be transferred to page 1. This should be done when i mention a certain name, or name in combination with a nr.
page 1.
ONE TWO THREE FOUR FIVE SIX SEVEN TICKET TICKET EXTRA FEE FEE EXTRA CONTROLE EXTRA VALIDITY A 1500 10 <--This information needs to be mentioned as soon as I use the letter A in column A 30 <--This information needs to be mentioned as soon as I use the letter A in column A nr.1 30-6-2020 <--This information needs to be mentioned as soon as I use the letter A + nr.1(column A+O) B 2000 20 <--This information needs to be mentioned as soon as I use the letter A in column A 40 <--This information needs to be mentioned as soon as I use the letter A in column A nr.2 31-5-2020 C 1600 30 " '' 50 nr.3 30-7-2020 " " D 1800 40 " '' 60 nr.4 30-6-2020 " " E 2000 50 " '' 70 F 1900 60 " '' 80 G 2500 70 " '' 90 Page 2.
DEBT. TICKET TICKET EXTRA FEE FEE EXTRA CONTROLE EXTRA VALIDITY A 10 30 85 205 305 nr.1 30-6-2020 B 20 40 95 215 405 nr.2 31-5-2020 C 30 50 105 225 505 nr.3 30-7-2020 D 40 60 115 235 605 nr.4 30-6-2020 E 50 70 125 245 705 nr.5 F 60 80 135 255 805 nr.6 G 70 90 145 265 905 nr.7 I could send you a copy of the excel file as well, just let me know if you need this.
Thanks again for your help!
Alright I think that gets my understanding a bit closer, but likely not all the way there. I'm interpreting your "Page2" is a reference table correct? So that has static information that is not changing, you want to reference it when entering other data into your Page1 table. So from my understanding when you enter the "name" in this case represented by a letter into column A, labelled ONE, you want the input table to populate your ticket amount, ticket extra, fee, fee extra, controle amounts. Then if you enter an Extra text "nr.1, nr.2" into column M labelled EXTRA, you want your Validity to populate.
I think I'm missing when you say you want to reference Name + Extra references together. Are extra reference codes "nr.1, nr.2.." not unique? There's a few ways to do stuff like this, finding the best option takes a good understanding of what your process is and what you're working toward - that's what's fun about excel, figuring out the best thing for you!
I've attached a SampleBook that I pasted your sample data into. I converted your Page1 into a table titled TicketsInput. I converted your Page2 into a table titled TicketDetails. I then used an Index-Match to pull your ticket, ticket extra, fee, fee extra and controle based on the name "A" and the validity column based on Extra "nr.1". I encourage you to look through my samplebook and pick apart the formula to understand the pieces. If you have any questions let me know.
