Excel changing cell references when columns inserted - VBA needed

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Hello, I don't think the answer is as straightforward as the question implies! I have a workbook with four worksheets. In Sheet1 I copy in data from an EPOS system and then I run code provided by members of this Forum that firstly splits a string that contains different products purchased, then code applies a lookup to obtain price and category and finally code inserts two columns and totals the price for each product.

On Sheet 2, I have attempted to sum the total price of each category in Sheet 1 - I tried to obtain a formula to sum every 5th column to the nth, but without success.

The problem is that when the third code is run the cell references in the sumifs formula on Sheet 2 changes even though I have accounted for the insertion of columns and some cell references are locked by using $$. I'm very confused.

Hope you can help - I think this probably needs code to sum the categories.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload Example file and desired results with XL2BB ADDIN ot at free hosting site e.g. GoogleDrive, OneDrive or www.dropBox.com and insert link here.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Please upload Example file and desired results with XL2BB ADDIN ot at free hosting site e.g. GoogleDrive, OneDrive or www.dropBox.com and insert link here.
Hi maabadi.

It's great that you've replied as you wrote the code for the string separation. Here is the OneDrive link example file with LUp and Totals 07.01.2020 TEST.xlsm You will see that the code works on data that is copied in on the Receipts Output tab up to row L. Your code separates the description string, then further code applies a look up and inserts two columns to include an if then and a sum formula.

What I want to do is sum the Total cost wherever it appears but need to apply a criteria. You will see on the tab Category Totals that I have tried to sum these (I allowed for up to thirty items). I've now deleted the data except for the formula in C5. My comments in red should explain more.

I think solving this by vba code is the answer - to sum every 5th column from a given point to the nth.

Thanks again.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Cell C5 shows desired output. But cell refrences change, as shown below, when code to insert columns is run.
You forgot to fix addresses with F4. For don't change Column Letter when formula run at multiple column you should add $ before Column letter e.g. $S4 Not S4
Also for Row Number, You should Add $ before Row number, e.g. S$4 Not S4 , and if you want fix both use $S$4 not S4 (all of this do with pressing F4 repeatedly)
Thus formula is This.
Excel Formula:
=IF(OR('Receipts Output'!$J:$J="Contactless",'Receipts Output'!$J:$J="Chip"),SUMIFS('Receipts Output'!$S:$S,'Receipts Output'!$Q:$Q,"Kitchen"))
to sum every 5th column from a given point to the nth.
Use this formula :
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL105)-COLUMN('Receipts Output'!$S2)+1,5)=0)*ISNUMBER('Receipts Output'!$S2:$AL105))
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You forgot to fix addresses with F4. For don't change Column Letter when formula run at multiple column you should add $ before Column letter e.g. $S4 Not S4
Also for Row Number, You should Add $ before Row number, e.g. S$4 Not S4 , and if you want fix both use $S$4 not S4 (all of this do with pressing F4 repeatedly)
Thus formula is This.
Excel Formula:
=IF(OR('Receipts Output'!$J:$J="Contactless",'Receipts Output'!$J:$J="Chip"),SUMIFS('Receipts Output'!$S:$S,'Receipts Output'!$Q:$Q,"Kitchen"))

Use this formula :
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL105)-COLUMN('Receipts Output'!$S2)+1,5)=0)*ISNUMBER('Receipts Output'!$S2:$AL105))
Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula a try thanks, but would appreciate it if you could advise how I can
You forgot to fix addresses with F4. For don't change Column Letter when formula run at multiple column you should add $ before Column letter e.g. $S4 Not S4
Also for Row Number, You should Add $ before Row number, e.g. S$4 Not S4 , and if you want fix both use $S$4 not S4 (all of this do with pressing F4 repeatedly)
Thus formula is This.
Excel Formula:
=IF(OR('Receipts Output'!$J:$J="Contactless",'Receipts Output'!$J:$J="Chip"),SUMIFS('Receipts Output'!$S:$S,'Receipts Output'!$Q:$Q,"Kitchen"))

Use this formula :
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL105)-COLUMN('Receipts Output'!$S2)+1,5)=0)*ISNUMBER('Receipts Output'!$S2:$AL105))

Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula for summing to nth column a try thanks, but I would appreciate it if you could advise how I can adapt this to sum by the category (kitchen) and payment type (contactless/chip) as shown in the first formula. Many thanks, Jeff
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula a try thanks, but would appreciate it if you could advise how I can


Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula for summing to nth column a try thanks, but I would appreciate it if you could advise how I can adapt this to sum by the category (kitchen) and payment type (contactless/chip) as shown in the first formula. Many thanks, Jeff
Don't know what I've done wrong but the formula returns a total of 416 whereas the actual sum of all the 5th columns is 296.10. Any idea why this might be? Thanks, Jeff
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

but I would appreciate it if you could advise how I can adapt this to sum by the category (kitchen) and payment type (contactless/chip) as shown in the first formula.

Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL100000)-COLUMN('Receipts Output'!$S2)+1,5)=0)*(ISNUMBER('Receipts Output'!$S2:$AL100000))*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))

Are you sure
Don't know what I've done wrong but the formula returns a total of 416 whereas the actual sum of all the 5th columns is 296.10. Any idea why this might be?
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL100000)-COLUMN('Receipts Output'!$S2)+1,5)=0)*(ISNUMBER('Receipts Output'!$S2:$AL100000))*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))
Again, many thanks. This time I used the formula it returned a total of 408 whereas the actual figure for all 5th columns from S2 is 192.95.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I find Problem. Use this Both with CTRL+SHIFT+ENTER
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER($S2:$AR105),$S2:$AR105,0))

AND
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER('Receipts Output'!$S2:$AR100000),'Receipts Output'!$S2:$AR100000,0)*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
I find Problem. Use this Both with CTRL+SHIFT+ENTER
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER($S2:$AR105),$S2:$AR105,0))

AND
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER('Receipts Output'!$S2:$AR100000),'Receipts Output'!$S2:$AR100000,0)*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))
That works, thanks. I changed the range from AR to CC and got #N/A. I may have more columns beyond AR (your earlier code split the string for up to 50 product items). Please advise how I should change this. Thanks again for all you are doing for me but we're nearly there!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top