Parts lists pop-up

[28130]

Hi there, i made a small parts list. In column A 1-99 each cell will have an item. Ex. A1 saw, A2 hammer, A3 screwdriver. When i click on an item such as A1, I would like a form with labels and text boxes to pop-up. i know i can make a form that matches my headers but i would like certain things to be only in the pop-up window only such as the description. Microsoft Access has a home inventory asset details form that pops up when an item is clicked on. Am i able to copy the Access form somehow or am I able to create my own form. Thank you

 

[Fluff]

Hi & welcome to MrExcel
You can create your own UserForm, have a look her to see how. https://www.contextures.com/xlUserForm01.html

 

[28130]

Is there a way without using VBA as i have limited coding experience. TY for your time

 

[Fluff]

Not if you want a pop-up form of some description an want to use Excel.

 

[Joe4]

As Fluff alluded to, if you want to use Excel with User Forms, you need to use VBA. You cannot assign values from the Form to the Worksheet without VBA.

You mentioned Access, why not just use Access for the whole thing? When it comes to Forms, I like Access much better, as you can bind the Form fields to Table fields, without having to use VBA to assign them.

 

[28130]

Hi, the reason I've used excel is because someone gave the formula to me so every item is adjusted for inflation every year on the first of the year. As in the chair purchased in 2010 would have 9 years of different yearly cpi"s inflation factored in. Where as the table purchased in 2015 would only factor in the last 4 years of inflation. Anal i know, but he got it to work. I don't believe i can incorporate this formula in to access. Thanks for the reply.

 

[Joe4]

I don't believe i can incorporate this formula in to access.

It depends on what the formula looks like (and if it uses some special functions).
But there is a decent chance that it might be able to be done in Access (but I really cannot see without seeing what that formula looks like).

 

[28130]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Article[/TD]
[TD="align: center"]Date Acquired[/TD]
[TD]Value[/TD]
[TD]Value Adjusted
for Inflation[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Inflation Rate[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Dining Chairs x 4[/TD]
[TD="align: center"]07-May-10[/TD]
[TD="align: right"]$ 800[/TD]
[TD="align: right"]$ 930.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2009[/TD]
[TD="align: right"]2.70%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Dining Table[/TD]
[TD="align: center"]07-May-18[/TD]
[TD="align: right"]$ 1,000[/TD]
[TD="align: right"]$ 1,019.00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2010[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Rolecks Watch[/TD]
[TD="align: center"]12-Mar-09[/TD]
[TD="align: right"]$ 25[/TD]
[TD="align: right"]$ 29.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2011[/TD]
[TD="align: right"]3%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Gold Ring Man's[/TD]
[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"]$ 1,500[/TD]
[TD="align: right"]$ 1,641.62[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"]1.70%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Gold Ring Woman's[/TD]
[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"]$ 3,850[/TD]
[TD="align: right"]$ 4,213.49[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Painting "Dogs Playing Poker"[/TD]
[TD="align: center"]01-Jan-88[/TD]
[TD="align: right"]$ 450[/TD]
[TD="align: right"]$ 537.76[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2014[/TD]
[TD="align: right"]0.80%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Painting "Scenic Scene"[/TD]
[TD="align: center"]01-Jan-10[/TD]
[TD="align: right"]$ 12,500[/TD]
[TD="align: right"]$ 14,545.07[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2015[/TD]
[TD="align: right"]0.70%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2016[/TD]
[TD="align: right"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2017[/TD]
[TD="align: right"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2018[/TD]
[TD="align: right"]1.90%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2019[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]=IF(YEAR(B2)>=YEAR(TODAY()),C2,FVSCHEDULE(C2,INDIRECT(ADDRESS(MATCH(MAX(YEAR(B2),$F$2)+1,$F$2:$F$99,0),COLUMN(G1))&":"&ADDRESS(MATCH(YEAR(TODAY()),$F$2:$F$99,0),COLUMN(G1)))))[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

</body>
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Article[/TD]
[TD="align: center"]Date Acquired[/TD]
[TD]Value[/TD]
[TD]Value Adjusted
for Inflation[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Inflation Rate[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Dining Chairs x 4[/TD]
[TD="align: center"]07-May-10[/TD]
[TD="align: right"]$ 800[/TD]
[TD="align: right"]$ 930.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2009[/TD]
[TD="align: right"]2.70%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Dining Table[/TD]
[TD="align: center"]07-May-18[/TD]
[TD="align: right"]$ 1,000[/TD]
[TD="align: right"]$ 1,019.00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2010[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Rolecks Watch[/TD]
[TD="align: center"]12-Mar-09[/TD]
[TD="align: right"]$ 25[/TD]
[TD="align: right"]$ 29.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2011[/TD]
[TD="align: right"]3%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Gold Ring Man's[/TD]
[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"]$ 1,500[/TD]
[TD="align: right"]$ 1,641.62[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"]1.70%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Gold Ring Woman's[/TD]
[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"]$ 3,850[/TD]
[TD="align: right"]$ 4,213.49[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Painting "Dogs Playing Poker"[/TD]
[TD="align: center"]01-Jan-88[/TD]
[TD="align: right"]$ 450[/TD]
[TD="align: right"]$ 537.76[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2014[/TD]
[TD="align: right"]0.80%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Painting "Scenic Scene"[/TD]
[TD="align: center"]01-Jan-10[/TD]
[TD="align: right"]$ 12,500[/TD]
[TD="align: right"]$ 14,545.07[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2015[/TD]
[TD="align: right"]0.70%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2016[/TD]
[TD="align: right"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2017[/TD]
[TD="align: right"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2018[/TD]
[TD="align: right"]1.90%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2019[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]=IF(YEAR(B2)>=YEAR(TODAY()),C2,FVSCHEDULE(C2,INDIRECT(ADDRESS(MATCH(MAX(YEAR(B2),$F$2)+1,$F$2:$F$99,0),COLUMN(G1))&":"&ADDRESS(MATCH(YEAR(TODAY()),$F$2:$F$99,0),COLUMN(G1)))))[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[28130]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Article[/TD]
[TD="align: center"]Date Acquired[/TD]
[TD]Value[/TD]
[TD]Value Adjusted
for Inflation[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Inflation Rate[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Dining Chairs x 4[/TD]
[TD="align: center"]07-May-10[/TD]
[TD="align: right"]$ 800[/TD]
[TD="align: right"]$ 930.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2009[/TD]
[TD="align: right"]2.70%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Dining Table[/TD]
[TD="align: center"]07-May-18[/TD]
[TD="align: right"]$ 1,000[/TD]
[TD="align: right"]$ 1,019.00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2010[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Rolecks Watch[/TD]
[TD="align: center"]12-Mar-09[/TD]
[TD="align: right"]$ 25[/TD]
[TD="align: right"]$ 29.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2011[/TD]
[TD="align: right"]3%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Gold Ring Man's[/TD]
[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"]$ 1,500[/TD]
[TD="align: right"]$ 1,641.62[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"]1.70%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Gold Ring Woman's[/TD]
[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"]$ 3,850[/TD]
[TD="align: right"]$ 4,213.49[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Painting "Dogs Playing Poker"[/TD]
[TD="align: center"]01-Jan-88[/TD]
[TD="align: right"]$ 450[/TD]
[TD="align: right"]$ 537.76[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2014[/TD]
[TD="align: right"]0.80%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Painting "Scenic Scene"[/TD]
[TD="align: center"]01-Jan-10[/TD]
[TD="align: right"]$ 12,500[/TD]
[TD="align: right"]$ 14,545.07[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2015[/TD]
[TD="align: right"]0.70%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2016[/TD]
[TD="align: right"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2017[/TD]
[TD="align: right"]2.10%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2018[/TD]
[TD="align: right"]1.90%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2019[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]D2[/TH]
[TD="align: left"]=IF(YEAR(B2)>=YEAR(TODAY()),C2,FVSCHEDULE(C2,INDIRECT(ADDRESS(MATCH(MAX(YEAR(B2),$F$2)+1,$F$2:$F$99,0),COLUMN(G1))&":"&ADDRESS(MATCH(YEAR(TODAY()),$F$2:$F$99,0),COLUMN(G1)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



This is the general layout with the formula as well.

 

[Joe4]

OK, I see the conundrum. It does use the financial function FVSCHEDULE, which I don't think Access has an equivalent for.

However, you may be able to find/use things that others have come up with, like the stuff found here: https://www.fmsinc.com/MicrosoftAccess/StatisticalAnalysis.html
Of course, you still then need to figure out how to translate your current formula to Access. You might need the assistance of someone who is familiar with those kind of financial statistic formulas.