Assist with Invoice Function to return cell that varies, always with the same total name 5 columns to the left

semoredhawk

New Member
Joined
Jul 6, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Attempting to create an invoice on another tab named Invoice. What function can I write to get a cell in the invoice tab to always return the Net number for the "Chesterfield - 5 Total:" line? This cell will NOT always be J16, it differs from month to month, but I want to simply copy/paste this report each month and get the invoice to return the correct information.

Reconciliation.png


Thanks,
Brad
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Will it always be in column J? If so, I would just do a vlookup. Something like this: =VLOOKUP( "Chesterfield - 5 Total:",A:J,10,FALSE)
 
Upvote 0
If that is a screenshot of Sheet1
=INDEX(Sheet1!$A:$AZ, MATCH(Sheet1!$A$A,"Chesterfield - 5 Total:",0),0)

Will return Columns A:AZ of the row that has "Chesterfield - 5 Total:" as its row header.
You can alter the final argument (0 above) to get any individual cell in that row.
 
Upvote 0
Will it always be in column J? If so, I would just do a vlookup. Something like this: =VLOOKUP( "Chesterfield - 5 Total:",A:J,10,FALSE)
It will always be in Column J, but this is not working for me. Could it have something to do with the fact that column A is merged with other columns on the "Chesterfield - 5 Total" line (and other total lines?
 
Upvote 0
If that is a screenshot of Sheet1
=INDEX(Sheet1!$A:$AZ, MATCH(Sheet1!$A$A,"Chesterfield - 5 Total:",0),0)

Will return Columns A:AZ of the row that has "Chesterfield - 5 Total:" as its row header.
You can alter the final argument (0 above) to get any individual cell in that row.
Thank you for the reply. This is not working for me when I ensure sheet name and final argument are correct. Could it have something to do with the fact that column A is merged with other columns on the "Chesterfield - 5 Total" line (and other total lines?
 
Upvote 0
Welcome to the MrExcel board!

When a suggestion "is not working for me" please try to provide additional detail otherwise helpers do not have much to go on.
Did it produce an error (what error)?
Did it return a value from the correct row but wrong column (what column)?
Did it return a value from the correct column but wrong row (what row)?
Did it return some other unexpected result (what was it)?
Did it crash Excel?
etc

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Did you include the sheet name with Nothnless formula? eg
=VLOOKUP( "Chesterfield - 5 Total:",Sheet1!A:J,10,FALSE)

If you did, try also
=VLOOKUP( "Chesterfield - 5 Total:*",Sheet1!A:J,10,FALSE)

If the data is in column A but the cell merged into B, C etc that should not be a problem.

Another option to try would be like
=INDEX(FILTER(Sheet1!$J$1:$J$200,Sheet1!$A$1:$A$200="Chesterfield - 5 Total:"),1)
 
Upvote 0
Thank you very much to each of you for the assistance and especially to Peter_SSs for the additional information/guidance on how to better illustrate my needs. I was able to get the VLookup function to work, and had simply placed the sheet name in the incorrect place (before "Chesterfield..." instead of before "A:J").
Thanks again!
 
Upvote 0
Cheers, glad you got it sorted. Thanks for letting us know. :)
 
Upvote 0
Thank you for the reply. This is not working for me when I ensure sheet name and final argument are correct. Could it have something to do with the fact that column A is merged with other columns on the "Chesterfield - 5 Total" line (and other total lines?
Yes, it could. Eliminate the merged cells. Center Across Selection works the same without the interferance.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top