Dragging a formula horizontally whilst searching vertically

Resolved · Urgent Priority · Version 365

Wed 6 Sep 2023, 11:48 Delegate Sarah said...

Sarah has attended:
Excel Advanced course

Dragging a formula horizontally whilst searching vertically

Hello,

I have a large document with lots of separate sheets for different retailers with one main summary sheet.

On the main 'summary' sheet, I want to pull back information from each of the retailer sheets. The summary sheet is working horizontally, while the retailer sheets are working vertically.
This means I want to pull a formula across, whilst returning search result from a separate sheet going vertically

I have used Index formulas however because the cells in the sheet I am looking to are merged, it isn't pulling back the correct merged cell reference and instead is looking at each individual cell and isnt working

I think I could use a sumif formula to do this, but because the cells are pulling back text, I cant seem to do this

If someone could help I would be very grateful!

Fri 8 Sep 2023, 12:58 Trainer Martin said...

RE: dragging a formula horizontally whilst searching vertically

Hi Sarah,

Thank you for your question to the forum.

If you have Office 365 Excel then I would suggest you use the TRANSPOSE function which changes the orientation of all linked cells. Let's say your source data is in a sheet called Retail South in cells A2 to A11.

1. Ensure all source cells are unmerged

2. In the summary sheet, select the first cell in the row you want to populate and type =TRANSPOSE('Region South'!A2:A11) or just type =TRANSPOSE and then select the cells from the other sheet

The real benefit of doing this is that the summary data is a live link to the source data. So any changes made will be reflected in the summary sheet.

I hope this helps. Please let me know if it has worked or not

Kind regards
Martin Sutherland
IT Trainer

Fri 12 Jan 2024, 00:41 Trainer Rich said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Display pictures on Chart Data Point

Replacing a single chart data point bar with a picture.
Step 1: Left click on a bar. Then, wait, and do a second single click on the bar. This will select just one data point.

Step 2: Right click on the bar and select Format Data Point.

Step 3: On the fill effects tab, choose a picture. Browse for a picture for that bar. Indicate if you want it to be stretched or stacked. Repeat for each bar.

View all Excel hints and tips