how to enter a value in a specific cell based on column and row headers

[Shazzj001]

Hi,

Help please....

I need to enter a monetary value in a specific cell which is dictated to by the row (week) and column (Number ID).

I assume the best way to do this is using a VBA userform with 3 text boxes plus a few command buttons..

• ID number : 1 to 236
• Week date (always a Sunday) : 07/01/2024 through to 29/12/2024 (52 weeks)
• Monetary value eg: £20.00

How do I get the monetary value to assign cell corresponding to the ID Number (eg 24) and the week (eg 24/3/2024)?

My spreadsheet has ID number's from D3 down to D238 and Weeks from H2 to BG2.
My monetary values can be entered anywhere from H3 to BG238

I have bought a book on VBA programming, but I would appreciate guidance as to how I would go about this task as I don't know where to start.

Thanks for reading and I hope you can understand what I'm trying to do.

Kind Regards
Shazz

 

[shift-del]

Hallo Shazz

Why are you making it so complicated? You just need a 2-column-list.

Mappe6
	A	B	C
1	ID	Week	Value
2	24	24.03.2024	20
3	145	07.07.2024	44
4	38	28.01.2024	148
Tabelle2

 

[Shazzj001]

Hi Shift-del,

Thanks for responding.

I had thought of doing a 2- column list and entering all data onto that list, but how does this data transfer to the weekly donations spreadsheet?

This is a charity donations spreadsheet and it must show the full year of donations against the appropriate ID number and week.

Currently this is done by clicking into each cell, but there has to be another way of entering this data.

There is an additional problem I face...

The spreadsheet is updated fortnightly when donations are counted. I would need someway of knowing how much was entered on the day it's updated so that the money being lodged corresponds to the amount entered. There are totals at the end of each row and column, but it isn't just a simple case of =Sum () for that week... donations are entered according to the information on the donation envelope which is ID num and week. If a donator gives using 3 envelopes at a time these have to registered in that appropriate week cell which is detailed on envelope not a lump sum on date counted and entered.

Hope this all makes sense.

Thanks
Shazz

 

[shift-del]

This is a charity donations spreadsheet and it must show the full year of donations against the appropriate ID number and week.

That is what the list does.

If a donator gives using 3 envelopes at a time these have to registered in that appropriate week cell which is detailed on envelope not a lump sum on date counted and entered.

Type a row for each envelope. See the color-coded example.

Insert a pivot table to get the sum of each intersection and the sums for each row and each column.
You can group the date field into years, quarters and months if needed. And add subtotals.

Mappe6
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Week	Value			Summe von Value			ID
2	24	24.03.2024	20			Jahre (Week)	Quartale (Week)	Tage (Week)	24	38	145	Gesamtergebnis
3	145	07.07.2024	44			2024	Qrtl1	28. Jan		225		225
4	38	28.01.2024	148					24. Mrz	20			20
5	38	28.01.2024	74				Qrtl1 Ergebnis		20	225		245
6	38	28.01.2024	3				Qrtl3	07. Jul			44	44
7							Qrtl3 Ergebnis				44	44
8						Gesamtergebnis			20	225	44	289
Tabelle2

 

[Shazzj001]

That is what the list does.

Type a row for each envelope. See the color-coded example.

Insert a pivot table to get the sum of each intersection and the sums for each row and each column.
You can group the date field into years, quarters and months if needed. And add subtotals.

Mappe6
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Week	Value			Summe von Value			ID
2	24	24.03.2024	20			Jahre (Week)	Quartale (Week)	Tage (Week)	24	38	145	Gesamtergebnis
3	145	07.07.2024	44			2024	Qrtl1	28. Jan		225		225
4	38	28.01.2024	148					24. Mrz	20			20
5	38	28.01.2024	74				Qrtl1 Ergebnis		20	225		245
6	38	28.01.2024	3				Qrtl3	07. Jul			44	44
7							Qrtl3 Ergebnis				44	44
8						Gesamtergebnis			20	225	44	289
Tabelle2

Ok will have a look at this and see if it will work with my spreadsheet.

Thanks again for your response.
Sharon