Concentrate heading + time and offset

ziggyfo

New Member
Joined
Mar 24, 2021
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hi,

How would you write a concatenate formula to offset as its copied down.

Im looking to enter the formula in A3, to look at the heading in A1 and and the time in B2 and copy it down. However, an offset is required as I don't want to manually adjust the formula every time so it must then take the time in B7 when it reaches A8 and so on.

Currently there is no data in A2:A20 (this range is not static if that's relevant) i have manually done it as an example of the desired outcome. I managed to concatenate the cells "=CONCATENATE(A3," ",TEXT(B3,"hh:mm"))" but the offset is what im struggling with.

6.JPG
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ziggyfo

New Member
Joined
Mar 24, 2021
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Thanks keyboard, the head is supposed to read CONCATENATE and not concentrate lol.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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